PDA

View Full Version : Having trouble with a VBA double loop iteration



evanpek87
03-27-2015, 11:08 AM
Hi I'm trying to verify that all the records in one column are in the second column. Simple program. However, it never makes it past the point where the second column has a value that isn't in the first column. The point of this code is that the program skips to the next value when it can't find the current one anywhere in the first column. Here is the code, and thanks for any help.




Private Sub RunMatch_Click()


Dim maxRange, minRange As Integer
Dim aVal, bVal As String
minRange = CInt(minValue.Text)
maxRange = CInt(noRecords.Text)


For i = minRange To maxRange
aVal = Cells(i, "A")
bVal = Cells(i, "B")

If aVal = bVal Then
Cells(i, "C").Value = "1"
Else
For j = i + 1 To maxRange
aVal = Cells(j, "A")
If aVal = bVal Then
Cells(i, "C").Value = "1"
Exit For
Else
End If
Next j

End If
Next i


End Sub

mikerickson
03-27-2015, 05:02 PM
You could put
=--(ISNUMBER(MATCH(B1,A:A,0))) in C1 and drag down

Or perhaps
=--(ISNUMBER(MATCH(B1,A1:A$65536,0)))

Paul_Hossler
03-28-2015, 07:17 PM
However, it never makes it past the point where the second column has a value that isn't in the first column. The point of this code is that the program skips to the next value when it can't find the current one anywhere in the first column. Here is the code, and thanks for any help.


Q -- first or second column?

Since your outer loop is the first column, I assume you meant when it cannot find a first column value in the second?

This is not tested and I'm not 100% I understand, but it might give you a starting point




'----- recommended
Option Explicit

Private Sub RunMatch_Click()

'---- you need to repeat 'As Integer' otherwise they're Variant
Dim maxRange As Integer, minRange As Integer
Dim aVal As String, bVal As String
Dim i As Long, j As Long

minRange = CInt(minValue.Text)
maxRange = CInt(noRecords.Text)

With ActiveSheet
For i = minRange To maxRange

.Cells(i, 3).Value = 0

For j = minRange To maxRange
If .Cells(i, 1).Value = .Cells(i, 2).Value Then
.Cells(i, 3).Value = 1
Exit For
End If
Next j
Next i
End With
End Sub




There are more efficient ways, but this follows down your path