and another iteration that will leave blanks where there is no item number,
using an if and next statement

Option Explicit
Dim rng As Range
Dim lastrow As Long
Dim i As Long
Sub test()
Application.ScreenUpdating = True
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
i = 1
For i = 1 To lastrow
If Cells(i, "A").Value = "" Then
Cells(i, "A").Offset(0, 1).Formula = ""

Else

Cells(i, "A").Offset(0, 1).Formula = "=IF(ISNA(VLOOKUP(A" & i &
",Sheet2!parts,2,FALSE)),""""," & _
"VLOOKUP(A" & i & ",Sheet2!parts,2,FALSE))"

End If
Next i

Application.ScreenUpdating = True

End Sub


--


Gary


"PeteB" wrote in message
...
Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially I
want to run a macro that will insert a column in a specific location (not
a problem), then it will go down each row in the previous column to look
at each cell, and if it's empty, it leaves the new cell in the next column
blank. If it finds text, such as "Item 1", it will put a part number in
the next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete