Concanate problem

[PANKAJUTEKAR]

All Experts,
Hi..

j = col B

Function concatenate()
Dim i As Integer, j As Integer
Do Until j = 18
i = 2.................rowindex
j = 3.................colindex
Cells(i, j) = Cells(3, 2)........range("b3")
ActiveCell.FormulaR1C1 = "=concatenate(""P-"",cell(3,2))"
j = j + 1
Loop
End Function

Private Sub Workbook_Open()
Call concatenate
End Sub

Wht i tryed, in my col A is blank.
col b contains some tags like -
01H001A-M01
01H001A-M02
01H001A-M03 and more.

i got an error 1004...for -
ActiveCell.FormulaR1C1 = "=concatenate(""P-"",cell(3,2))"

So, how do i concatenate P- with respective cells automatically?

 

[pgc01]

Hi

Try:

ActiveCell.FormulaR1C1 = "=concatenate(""P-"",R" & i & "C" & j & ")"

 

[CharlesChuckieCharles]

You've got CELL instead of CELLS

 

[Sektor]

First, you're using FormulaR1C1 when you use A1 notation.
Second, not "cell", but "Cells".
Third, "cells" must be concatenated with ampersand (&).

ActiveCell = "P-" & Cells(3, 2)

 

[CharlesChuckieCharles]

So if you want to keep the cell reference then

Range("A3").Formula = "=Concatenate(""P-""," & Cells(3, 2).Address & ")"

else use SEKTOR's answer

 

[PANKAJUTEKAR]

Dear All Experts,

Thanks For ur quick help.

i got 3 options (answers) from you. All are working but only for the one cell that b3.
my row index not going increasing wherein,
i put j=j+1

You got it all?


Kind Suggestion..

 

[CharlesChuckieCharles]

So if you want to keep the cell reference then

Range("A3").Formula = "=Concatenate(""P-""," & Cells(3, 2).Address & ")"

else use SEKTOR's answer

Then use

Range("A3").Formula = "=Concatenate(""P-""," & Cells(i, j).Address & ")"

 

[Sektor]

In your code ActiveCell doesn't advance. Is in red what you want?

[COLOR="Blue"]Sub[/COLOR] concatenate()
 
    [COLOR="Blue"]Dim[/COLOR] j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    
    j = 1
    [COLOR="Blue"]Do[/COLOR] Until j = 18
        ActiveCell = "P-" & cell(2, j)
        j = j + 1
[B][COLOR="Red"]        ActiveCell = ActiveCell.Offset(1, 0)[/COLOR][/B]
    [COLOR="Blue"]Loop[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[PANKAJUTEKAR]

Sektor Sir,

What this "Offset" mean?
last time also you used...how this works?

 

[Sektor]

You update the same cell 18 times. Is it OK?