VBA: Reference cell address with a loop

Question

I have a workbook of about 40 sheets. I am working inside a table on sheet1 compiling data from throughout the workbook.

Each cell value in column A has a corresponding sheet of the same name somewhere inside the workbook.

I am attempting to loop through the column and for each cell 1) find the corresponding worksheet 2) copy a single cell value 3) paste that value back to the righthand side of the original table.

My current attempt is below. I keep getting (Error 91) Object variable not set. Any debugging tips or general advice would be greatly appreciated!

Sub LoopColumn()
 
Dim cell As Range
Dim ws As Worksheet
Dim ws_num As Integer
Dim CellName As String
Dim CellLocation As Range


ws_num = ThisWorkbook.Worksheets.Count
Worksheets(1).Select

For Each cell In Range("A:A")
    CellName = ActiveCell.Value
    CellLocation = ActiveCell.Address
   
        
        For i = 1 To ws_num

            If ws.Name = CellName Then
                ThisWorkbook.Worksheets(i).Activate

                ActiveSheet.Range("L1").Select
                Selection.Copy
                ActiveSheet.Paste Destination:=Worksheets(1).Range(CellLocation.Offset(0, 4))
            
            End If
        Next i
    
    Worksheets(1).Select
Next cell
 
 
End Sub

You really should [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) instead of looping over *every single cell* in column A. — BigBen, Jul 29 '20 at 17:30
Do you really want to iterate over `1048576` cells in column A? — John Coleman, Jul 29 '20 at 17:31
You are Getting (Error 91) Object variable not set. because you have declared ws object `Dim ws as worksheet` but have not set it to any worksheet. — AFzal Saiyed, Jul 29 '20 at 18:10

score 2 · Accepted Answer · answered Jul 29 '20 at 17:59

Some suggestions:

Sub LoopColumn()
 
    Dim c As Range, ws As Worksheet, wsList As Worksheet
    
    Set wsList = ThisWorkbook.Worksheets(1)
    
    'only loop cells in the used range of ColA
    For Each c In wsList.Range("A1:A" & wsList.Cells(Rows.Count, 1).End(xlUp).Row).Cells
        If c.Value <> "" Then
            Set ws = Nothing
            On Error Resume Next 'ignore error if no sheet with this name
            Set ws = ThisWorkbook.Worksheets(c.Value) 'try to get the sheet
            On Error GoTo 0      'stop ignoring errors
            If Not ws Is Nothing Then
                ws.Range("L1").Copy c.Offset(0, 4) 'no need to activate anything
            End If
        End If
    Next c
    
End Sub

Patates Pilées · Answer 2 · 2020-07-29T20:03:28.110

If you want general debugging advice

Use F8 to run code line by line and identify the line that gives you the error (if code is short)
Use breakpoint (red dot on side of code) to stop the code early and look at your variable value before the error
Use Option Explicit on top of your code to force you to declare every variable corectly

With some experience using Breakpoint and running code on debug you'll most likely be able to find where and why the error are occuring. It'll make it a lot easier for you to look online or ask better on Stack!

VBA: Reference cell address with a loop

2 Answers2