Add Text infront of Numbers

[bongyc]

Dear all,

In Sheet 1, A2 to A100 contains a list of numbers. I would like to do the following by creating a VBA:

1) Copy A2 to A100 from Sheet 1 to Sheet 2 at A2 to A100;

2) Add a text "DL" infront of the numbers in Sheet 2 after the above copying.

Appreciate for your expertise. Thank you.

Cheers,
YC

 

[Norie]

Do you really need VBA?

Sub CopyAddDL()

    Worksheets("Sheet2").Range("A2").Formula = "=" & Chr(34) & "DL" & Chr(34) & "& Sheet1!A2"
    
    Worksheets("Sheet2").Range("A2").Copy Worksheets("Sheet2").Range("A2:A100")
    
    Worksheets("Sheet2").Range("A2:A100").Copy
    
    Worksheets("Sheet2").Range("A2").PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    
End Sub

 

[Cbrine]

Sub CopyData()
Dim cell as range, ss as worksheet, ps as worksheet

set ss = activesheet
set ps = sheets("Sheet2")

for each cell in ss.range("A2:A100")
  ps.range(cell.address) = "DL"& cell
Next 

End sub

HTH
Cal

 

[Joe Was]

On Sheet2, Cell: A2 add this formula:

="DL" & Sheet1!A2

Then copy this formula down to the bottom of your range on Sheet2!

 

[bongyc]

Dear Joe, Cbrine and Norie,

Thank you very much with the brilliant solutions which really made my day.

Norie, may I know what is the reason for putting Chr(34) before and after DL in the VBA ?

Cheers,
YC

 

[Norie]

Chr(34) represents a double quote ", which is needed for the worksheet formula.

 

[bongyc]

Dear Norie,

Thanks for the explanation. It helps me to understand better.