I am trying to compare cells from a temporary worksheet to columns in different worksheets. Fields in temp are: Item, In/Out, Location, and ID
Situation 1: Item is listed in "Temp" (temporary worksheet) and is already in sheet1. The row that matches the item in sheet1 will need to be updated from the fields in "Temp".
Situation 2: Item from "Temp" is not listed in sheet1. Sheet2 is searched through to find the item title. Then the item, the fields from "Temp" and the item title (from sheet2) is input into sheet1.
Situation 3: Item from "Temp" is not found in either sheet. The item will be input into sheet2 as well as sheet1 without an item title.
To accomplish this I tried to use if statements with vlookup and a loop. Here is my attempt. I have omitted irrelevant code. I am not sure if my IF statements are working well. I think this may be because they are not activating the cell that matches my CBC, or ITEM, variable. How should I go about selecting this cell as well as using the vlookup properly?
Sub proTest()
Dim CBC As Variant
Dim PNN As Variant
Dim RNG As Range
Dim rB As Range
Dim InOut As Variant
Dim Loc As Variant
Dim iii As Variant
Set CBC = Worksheets("Temp").Cells(1, 1)
Do Until CBC = ""
PartNN = Mid(CBC, 6, 6)
Set RNG = Worksheets("Sheet2").Range("a2:a65536")
Set rB = Worksheets("Sheet1").Range("a3:a65536")
If Application.VLookup(CBC, rB, 1, False) = 1 Then
'COPY AND PASTING CODE'
Worksheets("Temp").Cells(1, 1).EntireRow.Delete
CBC = Worksheets("Temp").Cells(1, 1)
ElseIf Application.Vlookup(PNN, RNG, 1, False) = 1 Then
Sheets("Sheet1").Select
If Range("a1") <> "" Then
Range("a1").End(xlUp).Offset(1, 0).Select
Else
Range("a1").End(xlUp).Select
End If
CBC.Paste
'COPY AND PASTE CODE'
Worksheets("Temp").Cells(1, 1).EntireRow.Delete
CBC = Worksheets("Temp").Cells(1, 1)
Else
Sheets("Sheet2").Select
If Range("a1") <> "" Then
Range("a1").End(xlUp).Offset(1, 0).Select
Else
Range("a1").End(xlUp).Select
End If
PNN.Paste
Sheets("Sheet1").Select
If Range("a1") <> "" Then
Range("a1").End(xlUp).Offset(1, 0).Select
Else
Range("a1").End(xlUp).Select
End If
CBC.Paste
'COPY AND PASTE CODE'
Worksheets("Temp").Cells(1, 1).EntireRow.Delete
CBC = Worksheets("Temp").Cells(1, 1)
End If
Loop
End Sub
Bookmarks