How can I change the row number in the range().formula

[amitcohen]

Hi Guys
I'm using the following macro to create cells with formulas.

Sub CreateLoginDetails()
Dim i As Long
Dim lps As Long
lps = Range("C1").Value
For i = lps To 1 Step -1
    With ActiveSheet
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = "TAG POS=1 TYPE=A ATTR=TXT:Log<sp>Out"
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = ""
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = "TAG POS=1 TYPE=INPUT:SUBMIT FORM=ID:loginform ATTR=ID:wp-submit"
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = "=CONCATENATE(""TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:loginform ATTR=ID:user_login CONTENT="",'DB List'!$C1)"
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = "SET !ENCRYPTION NO"
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = "=CONCATENATE(""TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:loginform ATTR=ID:user_login CONTENT="",'DB List'!$B1)"
        .Range("A" & i).Insert xlShiftDown
        .Range("A" & i).Formula = "=CONCATENATE(""URL GOTO="",'DB List'!$A1)"
    End With
Next i
End Sub

For every loop, I need to change the row number with 1 step.
So first round will populate code for A1, B1, C1.
The next round will populate code for A2, B2, C2.
And so on..
But have to use the "=CONCATENATE()" in the code, I can't find a way to force that change inside the cell.

Hope you have some ideas for me

Thanks,
Amit.</sp>

 

[Ruddles]

Is this it?

.Range("A" & i).Formula = "=CONCATENATE(""URL GOTO="",'DB List'!$A[COLOR=red][B]" & i & "[/B][/COLOR])"

 

[amitcohen]

Absolutely awesome.

Thank you!!