Reputation: 41
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
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
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