Inserting formula into cell using vba

[arehman1289]

Hello

Im trying to insert a formula in a cell, with a mix of strings and integer values but am unsuccessfull.

nr = 41
For k = 1 To 21
If k = 3 Then
Cells((nr + k), "D").Value = "VLOOKUP('User Input'!$C(nr+k),'Work process list'!$A$2:$B$11,2,FALSE)"
End If
Next k

All it does is paste "VLOOKUP('User Input'!$C(nr+k),'Work process list'!$A$2:$B$11,2,FALSE)" as a string.

In this part of the formula C(nr+k)

I would like it to iterate through the rows of the C Column of the worksheet User Input.

Please help

 

[mumps]

Try:

Cells((nr + k), "D").Formula = "=VLOOKUP('User Input'!$C" & nr + k & ",'Work process list'!$A$2:$B$11,2,FALSE)"

 

[Fluff]

It should be

Cells(nr + k, "D").Formula = "=VLOOKUP('User Input'!$C" & nr + k & ",'Work process list'!$A$2:$B$11,2,FALSE)"

But the loop is irrelevant as it will only put that formula in D44

 

[arehman1289]

thank you so much. it works and forgive me for my amatuer questions

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[arehman1289]

I am writing a basic code as:

Range("A20:E40").Copy Cells(nr, "A")
Cells(nr, "A").Interior.ColorIndex = 35
Cells(nr, "D").Formula = "bla"

In this case line 3 isnt executed, but when I change nr in this line to an integer it works as:

Range("A20:E40").Copy Cells(nr, "A")
Cells(nr, "A").Interior.ColorIndex = 35
Cells(43, "D").Formula = "bla"

It is quite strange since the variable nr has been declared globally and if this were the problem with the variable the first two lines wouldnt execute. What could it be ?

Thanks

 

[Fluff]

As this is a totally different question, you will need to start a new thread. Thanks

 

[arehman1289]

okkk