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 dont 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

"ram" wrote:

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 dont 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

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

On Fri, 16 Oct 2009 12:02:01 -0700, ram
wrote:

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 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

"Mike H" wrote:

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

"ram" wrote:

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 dont 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