CODEWITHSUNDEEP
vbaexcelruntime-error
Pwoods
Pwoods

Reputation: 105

VBA runtime error 9

I am currently getting a error saying subscript out of range. on the line of code 

If Sheets(Master).Cells(i, A).Value = AssetNum.Value Then

I'm trying to use a for loop to increment i so the row range starts at 12 and adds 1 to it each time. Then inside the for loop I want to use an If statement to check and see if the cell (i,A) is equal to the value in AssetNum. If the loop reaches the value of the EmptyRow it ends the loop. Im not exactly sure how to use a for loop IF-THen statement correctly.

Public i As Integer

Private Sub AssetNum_Change()

End Sub

Private Sub Enter_Click()



Dim EmptyRow As Long

'Audit will only search Master Sheet
Worksheets("Master").Activate


'Find empty row value so we can use that for limit of search
With Sheets("Master")
     EmptyRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
End With

'i needs to be set to minimum limit
'Begin loop of search
For i = 12 To EmptyRow + 1

If Cells(i, 1).Value = AssetNum.Value Then

    'Go to compare userform to display

    Compare.AssetDisplay.Value = AssetNum.Value
    Compare.LocationDisply.Value = Cells(i, 2).Value
    Compare.Show

   End If
 Next i

    'If i gets to emptyrow num then go to non found asset userform
    Unload Me
    NonFoundAsset.Show

Upvotes: 0

Views: 134

Answers (2)

R.Katnaan
R.Katnaan

Reputation: 2526

Use as follow:

If Sheets(Master).Cells(i, "A").Value = AssetNum.Value Then

And also this line:

Compare.LocationDisply.Value = Cells(i, "B").Value

Upvotes: 0

Paul Ogilvie
Paul Ogilvie

Reputation: 25276

I assume your refer with your error to the line:

If Cells(i, A).Value = AssetNum.Value Then

Well, I see nowhere that A is declared. Then VBA declares it automatically (advice: always turn Tools, Options, Require variable declarations to ON). Neither do I see A being initialized, so its value will be 0 and that is not a valid reference for Cells. Hence, Subscript out of bounds.

If you want to reference the "A" column, then write:

If Cells(i, 1).Value = AssetNum.Value Then

as "A" is the first column.

Upvotes: 1

Related Questions