Comparison of String/Text/Number.

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Comparison of String/Text/Number.

Post by Steve_in_Kent »

I extract a table from SAP, into an Excel table.. then paste that table into worksheet 2 of my Workbook.

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..... :flee:

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
For clarification Brammer sheet, is sheet 2, copying from. and Inks... is sheet 1.

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.

:scratch:
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Comparison of String/Text/Number.

Post by HansV »

Could you attach a small sample workbook demonstrating the problem?
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Comparison of String/Text/Number.

Post by ChrisGreaves »

Steve_in_Kent wrote:
14 Nov 2020, 17:31
(yes, i have tried reformatting, to number, General etc. etc)
Hi Steve.
Just a quick conversation point: My understanding of Formatting is that it does not change the contents of a cell, it changes only the appearance of a cell.
Thus I don a colourful Pirate costume when I am appearing in The Pirates of Penzance by Gilbert and Sullivan, but I re-clad myself in my bland street clothes to catch the bus home.
I look like a Pirate on stage for two hours, but that doesn't make me a pirate.

I have attached a trivial workbook showing a simple use of the TEXT, LEN, and T functions of Excel.
Cheers
Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Re: Comparison of String/Text/Number.

Post by Steve_in_Kent »

Thanks Hans yes will do tomorrow ..

Chris .. thanks will check in the morning !!
Steve
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Re: Comparison of String/Text/Number.

Post by Steve_in_Kent »

ok.. well.. i created an example sheet... and rejigged it to work.. ran it.. and it worked perfectly. #confused.

so i think, somehow, it was something to do with the code.. but for the life of me, i don't know what !

cheers all.
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!