How to 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.

 

[Hui]

Amitcohen


what about using Cells instead of Range

eg:

[pre]

Sub CreateLoginDetails()
Dim i As Long
Dim j As Integer
Dim lps As Long

lps = Range("C1").Value
j = 1

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

[/pre]

 

[xld]

Amit,


Try this


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 = "=""TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:loginform ATTR=ID:user_login CONTENT=""&'DB List'!$C" & i & ")"

.Range("A" & i).Insert xlShiftDown

.Range("A" & i).Formula = "SET !ENCRYPTION NO"

.Range("A" & i).Insert xlShiftDown

.Range("A" & i).Formula = "=""TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:loginform ATTR=ID:user_login CONTENT=""&'DB List'!$B" & i & ")"

.Range("A" & i).Insert xlShiftDown

.Range("A" & i).Formula = "=""URL GOTO=""&'DB List'!$A" & i & ")"

End With

Next i

End Sub

 

[amitcohen]

Hi Guys

Thanks for your answers.

xld; your solution is perfect for the challenge.




Hui; Using Cells() never cross my mind..

I learned another thing 2day.

BTW, is it a better coding to use Cells() instead of Range()?

What is the idea behind it?


Cheers,



Amit.

 

[xld]

Amit,


It is not better per se, but if you have a variable column, Cells can take a numeric column, which may be better when assigning via a variable, whereas Range must have a column letter(s).