CODEWITHSUNDEEP
excelvba
Hung Pham Vu
Hung Pham Vu

Reputation: 29

Excel VBA For Loop is ignored without warnings

I have a piece of code as follow:

Dim index, find As Integer
    find = 0
    index = MoldWidthComboBox.Value * 10 + MoldLengthComboBox.Value / 10
    'MsgBox (index)
    Dim Range1 As Range
    Set Range1 = Range("B2:B71")
    Dim RowNum As Integer
    For RowNum = 2 To Range1.Count
        If Cells(RowNum, 2) = index Then
            designtable1.Configuration = Cells(RowNum, 1).Value
            find = 1
            'Exit For
        End If
    Next RowNum
    
    If find = 0 Then
        MsgBox ("No any moldbase configurations is found")
        MsgBox (index)
        Exit Sub
    End If

Problem: The code runs without any error warning but it seemed like my For loop is ignored in the running process, which cause errors to my big program. I can't make out why. Please help me.

Note: The problem came up when I added some lines of code in the original one, which run well and look as follow:

Dim index As Integer
index = MoldWidthComboBox.Value * 10 + MoldLengthComboBox.Value / 10
Dim Range1 As Range
Set Range1 = Range("B2:B71")
Dim RowNum As Integer
For RowNum = 2 To Range1.Count
        If Cells(RowNum, 2) = index Then
            designtable1.Configuration = Cells(RowNum, 1).Value
        End If
Next RowNum

Upvotes: 1

Views: 111

Answers (1)

VBasic2008
VBasic2008

Reputation: 55073

A VBA Lookup: For...Next vs Application.Match

A Quick Fix (For...Next)

Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

Dim MoldIndex As Double
MoldIndex = MoldWidthComboBox.Value * 10 + MoldLengthComboBox.Value / 10
'MsgBox (MoldIndex)

Dim rgIndex As Range: Set rgIndex = ws.Range("B2:B71")

Dim FirstRow As Long, LastRow As Long

With rgIndex
    FirstRow = .Row
    LastRow = .Row + .Rows.Count - 1
End With

Dim r As Long, IsFound As Boolean

For r = FirstRow To LastRow
    If ws.Cells(r, "B").Value = MoldIndex Then
        designtable1.Configuration = ws.Cells(r, "A").Value
        IsFound = True
        Exit For ' if you only need the first occurrence
    End If
Next r

If IsFound Then
    IsFound = False ' reset if the whole thing is in a loop
Else
    MsgBox "No moldbase configurations found for '" & MoldIndex & "'.", _
        vbExclamation
    Exit Sub
End If

An Improvement (Application.Match)

  • Note that this works only if you're after the first occurring match.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

Dim MoldIndex As Double
MoldIndex = MoldWidthComboBox.Value * 10 + MoldLengthComboBox.Value / 10
'MsgBox (MoldIndex)

Dim rgIndex As Range: Set rgIndex = ws.Range("B2:B71")

Dim rIndex As Variant: rIndex = Application.Match(MoldIndex, rgIndex, 0)

If IsNumeric(rIndex) Then
    designtable1.Configuration _
        = rgIndex.Cells(rIndex).EntireRow.Columns("A").Value
Else
    MsgBox "No moldbase configurations found for '" & MoldIndex & "'.", _
        vbExclamation
    Exit Sub
End If

Upvotes: 1

Related Questions