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 instead of looping over every single cell in column A. — BigBen
– BigBen, Commented Jul 29, 2020 at 17:30
Do you really want to iterate over 1048576 cells in column A? — John Coleman
– John Coleman, Commented Jul 29, 2020 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
– AFzal Saiyed, Commented Jul 29, 2020 at 18:10

Tim Williams · Accepted Answer · 2020-07-29 17:59:00Z

2

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

answered Jul 29, 2020 at 17:59

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patates Pilées · Accepted Answer · 2020-07-29 20:03:28Z

1

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!

edited Jul 29, 2020 at 20:03

answered Jul 29, 2020 at 18:01

Patates Pilées

2451 silver badge9 bronze badges

Collectives™ on Stack Overflow

VBA: Reference cell address with a loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related