How about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'do nothing
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(iRow + 2, "A").Value
.Cells(iRow + 1, "B").FormulaR1C1 _
= "=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"
End If
Next iRow
.Rows(1).Delete
End With

End Sub

Be careful. This line is actually in R1C1 reference style:
"=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"

It's looking for sheet2 columns 1 and 2 (A:B).


DWeb wrote:

Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

--

Dave Peterson