View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ty Ty is offline
external usenet poster
 
Posts: 72
Default VLOOKUP Insert & Copy

I have several postings. All of the answers solved my problem. Here
is another problem that I can't resolve with just VLOOKUP. VLOOKUP
only grabs the first line of data from the other sheet.

I'm trying to use the same VB script from my first post(down below).
I need to look at Sheet #2 in comparison to Sheet #1. Whenever col
1:sheet2 has matching data, then sheet #1 need to INSERT ROW and copy
sheet2:column 2:cell data to sheet1:column2 plus sheet2:column1:cell
data to sheet1:column1. All changes will be made on Sheet #1 after
viewing Sheet #2.

More detail:
col 1 in both sheets will have the same type of data. Example: last 4
SSN.

sheet1
col A
2255
3322
1134
8844

col B
blank

Sheet2
col A
2255
2255
2255

col B
Ty
Lincoln
Tony



Sub Duplicates()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0,
0)
ActiveCell.Offset(Offsetcount - 1, 0).Interior.Color = RGB
(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

I understand this might not be clear the first time around to the
reader. If not, please ask questions. Thanks in advance.