Sheet Comparisons expounded on

[rudogg]

I have another question regarding this code, provided by Fluff. I am trying to use it in the same fashion for matching GTIN numbers on sheets 1 and 3.

The GTIN's on Sheet1 are formatted as General, so as soon as I click in a field and hit enter it turns to the scientific display of the number.
The GTIN's on Sheet3 are formatted as General sometimes, and as custom 000000000000 in other cases.

I can't really modify the formats on Sheet1 for fear of data not being reimported.

How do I compare these types of numbers? Any help would be greatly appreciated!

Sub Match_GTIN_Change_OurPrice()

    Sheets(1).Activate
    
    Dim Cl As Variant, mydiffs As Integer
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets(3)
        For Each Cl In .Range("D3", .Range("D" & Rows.count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, -2).Value
        Next Cl
    End With
    With Sheets(1)
        For Each Cl In .Range("N2", .Range("N" & Rows.count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.Interior.Color = vbYellow
               If Cl.Offset(, 4).Value <> Dic(Cl.Value) Then
                  Cl.Offset(, 4).Value = Dic(Cl.Value)
                  Cl.Offset(, 4).Interior.Color = vbYellow
                  mydiffs = mydiffs + 1
               End If

            End If
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been changed based on a match with the GTIN.", vbInformation
End Sub

 

[sijpie]

Will this work? I format the GTIN to a string, and then compare these. I haven't tested this at all. If i doesn't work post a few lines of your sheets for me to test some ideas. See also my comment in the code

Option Explicit

Sub Match_GTIN_Change_OurPrice()

    Sheets(1).Activate
    
    Dim Cl As Variant, mydiffs As Integer
    Dim Dic As Object
    Dim sGTIN As String
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets(3)
        For Each Cl In .Range("D3", .Range("D" & Rows.Count).End(xlUp))
            sGTIN = Format(Cl.Offset(, -2).Value, "00000000000#")
            Dic(Cl.Value) = sGTIN
        Next Cl
    End With
    With Sheets(1)
        For Each Cl In .Range("N2", .Range("N" & Rows.Count).End(xlUp))
            sGTIN = Format(Cl.Value, "00000000000#")
            If Dic.Exists(sGTIN) Then       '' this part I don't understand. Two lines up the GTIN is compared, _
                                    and below I think the prices are compared, but it checks the same cell value???
               Cl.Interior.Color = vbYellow
               If Cl.Offset(, 4).Value <> CDbl(Dic(sGTIN)) Then
                  Cl.Offset(, 4).Value = CDbl(Dic(sGTIN))
                  Cl.Offset(, 4).Interior.Color = vbYellow
                  mydiffs = mydiffs + 1
               End If

            End If
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been changed based on a match with the GTIN.", vbInformation
End Sub

 

[rudogg]

Will this work? I format the GTIN to a string, and then compare these. I haven't tested this at all. If i doesn't work post a few lines of your sheets for me to test some ideas. See also my comment in the code

Option Explicit

Sub Match_GTIN_Change_OurPrice()

    Sheets(1).Activate
   
    Dim Cl As Variant, mydiffs As Integer
    Dim Dic As Object
    Dim sGTIN As String
   
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets(3)
        For Each Cl In .Range("D3", .Range("D" & Rows.Count).End(xlUp))
            sGTIN = Format(Cl.Offset(, -2).Value, "00000000000#")
            Dic(Cl.Value) = sGTIN
        Next Cl
    End With
    With Sheets(1)
        For Each Cl In .Range("N2", .Range("N" & Rows.Count).End(xlUp))
            sGTIN = Format(Cl.Value, "00000000000#")
            If Dic.Exists(sGTIN) Then       '' this part I don't understand. Two lines up the GTIN is compared, _
                                    and below I think the prices are compared, but it checks the same cell value???
               Cl.Interior.Color = vbYellow
               If Cl.Offset(, 4).Value <> CDbl(Dic(sGTIN)) Then
                  Cl.Offset(, 4).Value = CDbl(Dic(sGTIN))
                  Cl.Offset(, 4).Interior.Color = vbYellow
                  mydiffs = mydiffs + 1
               End If

            End If
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been changed based on a match with the GTIN.", vbInformation
End Sub

Hey Thank you kindly for the reply! I'm gonna take a stab at this today and report back with results..

The code I posted above may have been a work in progress and not completely correct due to modifications, but the idea was to create a dictionary from sheet 3 with the offset values of the GTINs.
Then comparing to the GTIN's on Sheet 1, if it exists, highlight the GTIN on sheet 1. Then based on it existing, check the dictionary value based on the offset to the GTIN on sheet 1. If it's not the same then change it.

I hope that makes sense?!? I've been making macros for years, but just now getting into the nuts and bolts of coding VBA

Thank you again!

 

[sijpie]

What is held in these offsets in Sheet3? Are these the GTIN's or something else (prices)?
Post the headers of the tables, that will make it a lot clearer for me.