for next loop question

  • Thread starter Thread starter ram
  • Start date Start date
R

ram

I am using the code below to enter a new column in a worksheet and fill the
new column with a value based on the contents in to different cells. This
works fine.

I would like to enter a vlookup formula in column P each time the code
loops. I don’t know how to add the vlookup formula in the loop code

Thanks for any help


Sub subtest1()
Dim rang As Range, lngrow As Long, lngcount As Long, ws As Worksheet
Worksheets("Production").Columns(1).Insert
Set ws = Worksheets("Production")
lngrow = ws.Cells(Rows.Count, "B").End(xlUp).Row
rng = lngrow
For Each rng In Range("A1:A" & lngrow)
rng.Value = rng.Offset(0, 1) & rng.Offset(0, 2)


Next rng
End Sub
 
Hi,

It would have helped if you told us what you wanted to Vlookup and where but
here's something that may get you started

For Each Rng In Range("A1:A" & lngrow)
Rng.Value = Rng.Offset(0, 1) & Rng.Offset(0, 2)
Rng.Offset(, 15).Formula = "=vlookup(" & Rng.Address & ",J1:K100,2,false)"
Next Rng
End Sub

Mike
 
Edit the VLOOKUP formula to suit.

For Each rng In Range("A1:A" & lngrow)
rng.Value = rng.Offset(0, 1) & rng.Offset(0, 2)
rng.Offset(0, 15).Formula = "=VLOOKUP(G" & rng.Row & ",$A$1:$D$56,3,FALSE)"


Gord Dibben MS Excel MVP
 
Hi Mike,


I will be looking for the mothly production numbers located on a second
sheet. I think i came accross the vlookup code before.

Thanks for your help
 
Back
Top