Vlookup - on two tabs

  • Thread starter Thread starter GRS
  • Start date Start date
G

GRS

I have been having so many problems with the Lookup function. I though
I had them sussed but I just can’t get this one to do what I want.

I export a stock list every week which is a .csv file.

I’ve created one from last week and this is going to be my Master. O
the first tab of this ss it’s called ‘stocklevels’.

When I export the stocklist next time, I’ll export it to another fil
and then copy all the data into this master spreadsheet into tab 2 an
call it stocktoday.

I then want the lookup to look in column A on the stocklevel tab, fin
that value in column B in the stocktoday tab, and insert the stoc
amount in the stock today column (column V) in column G of th
stocklevel master.

Also, and this is where it gets even more tricky. There may be ne
products on the stocktoday tab than there are on the stock master. I
it possible to then get a macro to compare the product column on eac
and then add (preferable in a different colour) the new products at th
bottom of the list?

Thanks!

Gil
 
One way:

Public Sub AddNewProducts()
Dim rCell As Range
Dim rOld As Range
Dim rNew As Range
Dim rDest As Range
With Sheets("stocklevel").Range("A" & Rows.Count).End(xlUp)
Set rOld = Range(.Parent.Cells(1, 1), .Cells)
Set rDest = .Offset(1, 0)
End With
With Sheets("stocktoday")
Set rNew = .Range("B1:B" & _
.Range("B" & Rows.Count).End(xlUp).Row)
End With
For Each rCell In rNew
If Application.CountIf(rOld, rCell.Value) = 0 Then
With rDest
.Value = rCell.Value
.Font.ColorIndex = 5
End With
Set rDest = rDest.Offset(1, 0)
End If
Next rCell
End Sub
 
Back
Top