CODEWITHSUNDEEP
vbaexcelloopsforeach
Manu221
Manu221

Reputation: 41

VBA looping though cells in a range

I am working on a piece of code that will run through a sheet of data and check that said data is in the master sheet and if it is not found, it will add the data.

I was working on doing it via a For Each Cell in range. However, I am running into a issue that I cannot seem to find a work around for. This may just be me being slow and not finding it after working on some things for a few hours.

Here is what I have so far. I will spare the declarations.

The data looks something like this. On the Gold sheet I have 1 On the other I have: 2

The code is meant to take sheet1 and see if any of those stock codes are missing from sheet2, and if they are missing to just notify the user. 

Set xlsheet1 = Sheets("Gold")
Set xlsheet4 = Sheets("Working Sheet")

xlsheet1.Activate

xllr1 = xlsheet1.Range("B1").End(xlDown).Row
Set xlrange1 = xlsheet1.Range("B1:B" & xllr1)

With Range("A:Z")

Given my data is dynamic, i use the following .find to find the column with the stock codes.

    Set gs = .Find(what:="Symbol", After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    gcol = gs.Column
    fr1 = gs.Row + 1
End With

xlsheet4.Activate

xllr4 = xlsheet4.Range("A1").End(xlDown).Row
Set xlrange4 = xlsheet4.Range("A1:A" & xllr3)

this is where i encounter an issue. It works fine when the stock code matches. I was thinking of putting in a line saying if i = xllr1 and xlcell4.value <> cells(i, gcol) then etc... but it just doesn't seem to work. 

xlsheet1.Activate
For i = fr1 To xllr1
    For Each xlcell4 In xlrange4
        If xlcell4.Value = Cells(i, gcol) Then
        Else
            MsgBox "did not find code " & Cells(i, gcol)
        End If
    Next xlcell4
Next i

I hope that makes sense and appreciate any help!

Upvotes: 1

Views: 164

Answers (2)

Pat Jones
Pat Jones

Reputation: 898

You could just dump the values that you're searching through into an array:

Dim xlwb As Workbook
Dim xlsheet1 As Worksheet
Dim xlsheet4 As Worksheet
Dim list As Range
Dim listLength As Integer
Dim arr1()

Set xlwb = ThisWorkbook
Set xlsheet4 = xlwb.Sheets("Working Sheet")

listLength = xlsheet4.Range("A1").End(xlDown).Row
Set list = xlsheet4.Range("A1:A" & listLength)

ReDim arr1(listLength)
j = 0

For Each xlCell in list
    arr1(j) = xlCell
    j = j + 1
Next xlCell

Then search the array for each element:

Set xlsheet1 = xlwb.Sheets("Gold")
Set list = xlsheet1.Range("B1:B" & xlsheet1.Range("B1").End(xlDown).Row)

For Each xlCell in list

    found = False

    For j = 0 To UBound(arr1)
        If arr1(j) = xlCell Then
            found = True
        EndIf
    Next j

    If Not found MsgBox "Element not found: " & xlCell

Next xlCell

It's just another possibility for how to do this!

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

You need to trap the scenario where Find was unable to find a "match", by using If Not gs Is nothning Then.

Also, you have too many uses of unecessary Activate between our Sheets, this takes a long time to execute, instead use fully qualifed reference, by using With statements.

When looking to match values in 2 columns in 2 different worksheets, the fastest and "cleanest" way is to use one For loop and Application.Match function (see how it's implemented in the code below).

Modified Code

Option Explicit

Sub CompareStocks()

Dim xlsheet1 As Worksheet
Dim xlsheet4 As Worksheet
Dim gs As Range, xlrange1 As Range, xlrange4 As Range, C As Range
Dim xllr1 As Long, xllr4 As Long, gcol As Long, fr1 As Long
Dim i As Long

Set xlsheet1 = Sheets("Gold")
Set xlsheet4 = Sheets("Working Sheet")

With xlsheet1
    xllr1 = .Range("B1").End(xlDown).Row
    Set xlrange1 = .Range("B1:B" & xllr1) ' set the Range of Stocks in "Gold" sheet
End With

With xlsheet4
    Set gs = .Cells.Find(what:="Symbol", LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not gs Is Nothing Then  ' find was successful
        gcol = gs.Column
        fr1 = gs.Row + 1
    Else ' <-- Find failed to find a match >> raise an error message
        MsgBox "Unable to find Symbol", vbCritical
        Exit Sub
    End If

    ' set the range of "Symbol"s in "Working Sheet"
    xllr4 = .Cells(.Rows.Count, gcol).End(xlUp).Row ' get last row with data in the column where "Symbol" was found
    Set xlrange4 = .Range(.Cells(1, gcol), .Cells(xllr4, gcol)) ' set the range where all "Symbol"s are located
End With

' ===== Comparing values in 2 columns in 2 worksheets could be achieved with one For loop, and one Application.Match =====
' loop through cells in Stock range in "Gold" sheet
For Each C In xlrange1
    If IsError(Application.Match(C.Value, xlrange4, 0)) Then ' if Match was unable to find a matching record in "Symbol" range in "Working Sheet"
        C.Offset(, 1).Value2 = "Stock not found in " & xlsheet4.Name & " sheet!" ' write an error message to the column on the right
'        MsgBox "did not find code " & .Cells(i, gcol)
    End If
Next C

End Sub

Upvotes: 0

Related Questions