VBA string comparison with left not working (1 Viewer)

[machumpion]

sub match()
Dim whitebook As Workbook
Set whitebook = Workbooks("whitespace (aggregated) jun 28.xlsx")
For Each store In Workbooks("copy of NCG co-op.xlsx").Worksheets(1).Range("A1:A204")
For x = 1 To 26917
If Left(store, 4) = Workbooks("whitespace (aggregated) jun 28.xlsx").Worksheets("existing stores detail").Left(Range("C" & x), 4) Then
store.Interior.ColorIndex = 5
End If
Next
Next
End Sub
--------------
I am trying to compare the first 4 characters in a range in one book to the first 4 characters in a range in another book. I bolded the part that I am having trouble with. The code does not work when I use the left function with Range inside. Can someone tell me how to do this properly?

 

[kevlray]

Any time I have used a range with concatenating another string ("C" & x), I have always had to use the trim function ("C" & trim(x)). Why it has extra spaces I do not know. Also I think you might have to convert the x to a string, but it has been awhile since I have had to code in Excel.

 

[Rx_]

Won't have time to test. Suggest using an object instead of counter.

 Sub highlightrows()
Dim ColC As Range, ColD As Range, incrementMe As Integer

Set ColA = Range("C1:C2225")
Set ColB = Range("D1:D2225")
incrementMe= 1

For Each Rng In ColC
' Take an extra step for My4char1 = Lert( Rng.Value, 4)
 ' or what ever your trying to do with the string
 ' This is the code to just compare the whole strings
    If Rng.Value = Rng.Offset(, 1).Value Then
        Rng.Interior.Color = 16777215
        Rng.Offset(, 1).Interior.Color = 16777215
    Else
        Rng.Interior.Color = 500
        Rng.Offset(, 1).Interior.Color = 500
    End If
 Next 
 End Sub

If your data set has 40,000 rows, this object reference will be so much faster than using the for next loop and referencing an object (cell).
See the comments above, just add another variable to trim the sting as needed.

 

[NBVC]

I think the LEFT() should be around the whole references

e.g.

If Left(store, 4) = [B]Left[/B](Workbooks("whitespace (aggregated) jun 28.xlsx").Worksheets("existing stores detail").Range("C" & x), 4) Then