Runtime error 91 VBA Macro

[Peteor]

Sub Macro1()
Dim Cel As Range
Dim Rng As Range
Dim Local_Variable As Object


Set Rng = Worksheets("Index").Range("B5:B142")


For Each Cel In Rng
If Cel.Offset(0, 4) <> "X" And Cel.Offset(0, 8).Value = "X" Then
Cel.Offset(0, -1).Hyperlinks(1).Follow
If Application.WorksheetFunction.CountIf(Worksheets("Sheet1").Range("A1:A68"), Range("B4").Value) = 1 Then
Set Local_Variable = Worksheets("Table 1").Range("AL67:AL2255").Find(Range("B4").Value)
Range("G25") = Local_Variable.Offset(6, -37).Value
Range("G26") = Local_Variable.Offset(7, -37).Value
Range("G27") = Local_Variable.Offset(8, -37).Value
Range("G28") = Local_Variable.Offset(9, -37).Value
Range("G29") = Local_Variable.Offset(10, -37).Value
Range("G30") = Local_Variable.Offset(11, -37).Value


I have had this issue many times, and worked around it rather than figure it out. Local_Variable causes a runtime error 91. Can anyone help me here?

 

[DanteAmor]

Your macro has some details.


- You must declare the variable as Range.

[COLOR=#333333]Dim Local_Variable As [/COLOR][COLOR=#ff0000][B]Range[/B][/COLOR][COLOR=#333333][/COLOR]

- In this part always look for the value B4 in the range.

[COLOR=#333333]Set Local_Variable = Worksheets("Table 1").Range("AL67:AL2255").Find([/COLOR][COLOR=#ff0000]Range("B4").Value[/COLOR][COLOR=#333333])[/COLOR]

Every time you cycle, always look for the same value.


- You are not indicating the reference of the sheet.

[COLOR=#333333]Set Local_Variable = Worksheets("Table 1").Range("AL67:AL2255").Find([/COLOR][B][COLOR=#ff0000]sheets("xxxx").[/COLOR][/B][COLOR=#333333][/COLOR]Range("B4").Value[COLOR=#333333])[/COLOR]

- You should check if there is a sheet with the name "Table 1"

[COLOR=#333333]Set Local_Variable = Worksheets("[/COLOR][B][COLOR=#ff0000]Table 1[/COLOR][/B][COLOR=#333333]").Range("AL67:AL2255").Find([/COLOR][COLOR=#ff0000]sheets("xxxx")[/COLOR].[COLOR=#333333][/COLOR]Range("B4").Value[COLOR=#333333])[/COLOR]

- You must check if he found the data.

[COLOR=#333333]   Set Local_Variable = Worksheets([/COLOR]"[COLOR=#ff0000]Table 1[/COLOR]").Range("AL67:AL2255").Find([COLOR=#ff0000]sheets("xxxx").[/COLOR]Range("B4").Value[COLOR=#333333])
[/COLOR][COLOR=#ff0000]   If Not Local_Variable Is Nothing Then[/COLOR]
      Range("G25") = Local_Variable.Offset(6, -37).Value
      Range("G26") = Local_Variable.Offset(7, -37).Value
      Range("G27") = Local_Variable.Offset(8, -37).Value
      Range("G28") = Local_Variable.Offset(9, -37).Value
      Range("G29") = Local_Variable.Offset(10, -37).Value
      Range("G30") = Local_Variable.Offset(11, -37).Value
   End If

If you can describe what you need to do with an example, I will gladly help you correct the code.

 

[Peteor]

Dante,

I am trying to parse a large amount of data between two different spreadsheets.

I have added your comments to my code but am having issues with the find function I believe, as it is always returning "Nothing", even when I know it should not. For instance, on the active sheet Cell B4 will have the value of "W2". On the "Table 1" Sheet, Cell AL67 has the value "W2", yet it still returns nothing as written. Here is the code with your comments:

Sub Macro1()
Dim Cel As Range
Dim Rng As Range
Dim Local_Variable As Range


Set Rng = Worksheets("Index").Range("B5:B142")


For Each Cel In Rng
If Cel.Offset(0, 4) <> "X" And Cel.Offset(0, 8).Value = "X" Then
Cel.Offset(0, -1).Hyperlinks(1).Follow
If Application.WorksheetFunction.CountIf(Worksheets("Sheet1").Range("A1:A68"), Range("B4").Value) = 1 Then
With Worksheets("Table 1").Range("AL67:AL2255")
Set Local_Variable = Worksheets("Table 1").Range("AL67:AL2255").Find(ActiveSheet.Range("B4").Value)
If Not Local_Variable Is Nothing Then
Range("G25") = Local_Variable.Offset(6, -37).Value
Range("G26") = Local_Variable.Offset(7, -37).Value
Range("G27") = Local_Variable.Offset(8, -37).Value
Range("G28") = Local_Variable.Offset(9, -37).Value
Range("G29") = Local_Variable.Offset(10, -37).Value
Range("G30") = Local_Variable.Offset(11, -37).Value

 

[DanteAmor]

Dante,

I am trying to parse a large amount of data between two different spreadsheets.

I have added your comments to my code but am having issues with the find function I believe, as it is always returning "Nothing", even when I know it should not. For instance, on the active sheet Cell B4 will have the value of "W2". On the "Table 1" Sheet, Cell AL67 has the value "W2", yet it still returns nothing as written. Here is the code with your comments:

you must run the macro on the active sheet that contains the value in B4.

Try

[COLOR=#333333]Set Local_Variable = Worksheets("Table 1").Range("AL67:AL2255").Find(ActiveSheet.Range("B4").Value, , xlvalues, xlwhole)[/COLOR]