Script out of Range Error

Hello,

I have written a block of code to check if a sting appears in an array and if so, to make modifications on the line where a match is found. The Reference range and the array are on two separate worksheets. When I run this on a dummy spreadsheet to test the code, it works fine, however when I add it to my master spreadsheet (which has about 20 tabs, lots of database references, pivot tables and code already developed) I am getting a "Runtime-error '9': Subscript out of range" error. The specific line if code is in BOLD below. I have already checked and double checked that the worksheet names are correct and I am using the "Option Explicit" function to ensure no variables are undefined. Any thoughts? Thanks in advance!

(ps - the actual excel file has too much sensitive information to post screenshots so I cannot provide)

Option Explicit
Sub testing()

Dim b As Variant
Dim i As Long
Dim lr As Long
Dim lc As Long
Dim a As Range

Application.ScreenUpdating = False

lr = Sheets("Master Query").Cells(Rows.Count, "B").End(xlUp).Row

lc = Sheets("Master Query").Cells(1, Columns.Count).End(xlToLeft).Column

b = Sheets("Master Query").Range("B8:B" & lr)

For i = 2 To lr
  Set a = Sheets("Inputs").Range("C32:C34").Find(b(i, 1), LookAt:=xlWhole)
  If Not a Is Nothing Then
    Sheets("Master Query").Cells(i, 3).Value = 20
    Sheets("Master Query").Cells(i, 4).Value = 30
    Sheets("Master Query").Cells(i, 5).Value = 40
    Set a = Nothing
  End If
Next i

Application.ScreenUpdating = True

End Sub

Hi,

maybe try

'Set b = Sheets("Master Query").Range("B8:B" & lr)

For i = 2 To lr
  Set a = Sheets("Inputs").Range("C32:C34").Find(Sheets("Master Query").Range("B" & 6 + i), LookAt:=xlWhole)

Ciao,
Holger

Hi, elevate_yourself,

you can work with Range and two or three variables or use cells and have the same (only switching the place for rows and columns)

Sub one()
Dim lngCtr As Long

Const cstrCOL As String = "B"
Const clngSTART As Long = 6
For lngCtr = 1 To 6
  Debug.Print Range(cstrCOL & clngSTART + lngCtr).Address
Next lngCtr
End Sub

You may pass the column string or number as a parameter to the sub as well if you want to (if you use a number for the column make sure to use Cells instead of Range).

BTW: we don´t see what´s in your worklbook - why do you use find and not WorksheetFunction.CountIf?

Ciao,
Holger

I suspect, you do not have data in column B. The array b is empty.

b = Sheets("Master Query").Range("B8:B" & lr)

Hi, AB33,

lr stands the last used row in Column B, Area starts at Row 8 while Loop starts at row 2 - there might a good point no data being available due to different sizes.

Ciao,
Holger

Hi, elevate_yourself,

change

b = Sheets("Master Query").Range("B8:B" & lr)

to

b = Sheets("Master Query").Range("B1:B" & lr)

and you may use your array (which I think isn´t necessary at all).

Ciao,
Holger