On sheet 1 i have a list of all the things, with their SAP number.
I want to lookup the price of each item, one by one, and copy it from sheet two, to sheet 1. simples!
I tried Vlookup..but found that some of them wouldn't work... then i tried Index, Match.. and that didn't work.... ok i thought.. use tried an trusted VBA.....
So here's an example of sheet two
A / B / C / D
A= =VALUE(TRIM(CLEAN(B1)))
B is the number pasted from the SAP table.
C is the description.
D is the price i want, copied into sheet 1
Row A is my attempt to clean things up.
so here is an example of sheet 2.
12345678 / 12345678 / Fresh Grapes / 3.45
This is my code, running from a button.
Code: Select all
Function GetPrices()
' With Application
' .ScreenUpdating = False
' .EnableEvents = False
' End With
' Loop through I177 to I1300 and pull in the correct contract prices
Sheets("Inks Greases Reorder").Range("J177:J1400").ClearContents
Dim MySap As String
Dim c As Range
Dim firstAddress As String
Dim MyCost As String
Dim Rng As Range
For Myloop = 177 To 1300
MySap = ThisWorkbook.Worksheets("Inks Greases Reorder").Range("A" & Myloop).Value
'Search for it in Brammer sheet
If Trim(MySap) <> "" Then
With Worksheets("Brammer").Range("A1:A1300")
Set Rng = .Find(What:=MySap, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Rng Is Nothing Then
' Found the part
MyCost = Sheets("Brammer").Range(Rng.Address).Offset(0, 3).Value
ThisWorkbook.Worksheets("Inks Greases Reorder").Range("J" & Myloop).Value = MyCost
Else
ThisWorkbook.Worksheets("Inks Greases Reorder").Range("J" & Myloop).Value = "No Price"
End If
End With
End If
Next Myloop
End Function
What makes it more confusing.. is, if do some basic error checking like this...
=IF(x=y,1,0)... comparing the various cells against each other...
On the 2nd sheet.
A column, <> B column
A column <> Sheet 1 Sap number ( I column number)
however, B column number DOES match I column number on the first sheet. but still displays, no Price. (obviously, i'm checking A column though!)
So its like, something is going on, with column B numbers.. (imported into excel from SAP). yet clicking on them, shows all as TEXT
(yes, i have tried reformatting, to number, General etc. etc)
I've tried a few things . XlWhole.. cleaning up the numbers on the first sheet.. all sorts of things.