Why does only .Range("A2") work when I'm setting a value between two sheets? How can I make variable references work?

Question

Very new to VBA for excel.

I'm trying to put values from one sheet into another sheet. I've scoured the internet for solutions, and nothing has worked. Every time I get a 1004 Error: Application-defined error.

This is going to be part of a bigger program, but I can't even move past this step.

My goal is to make a Macro that will go through each sheet in the workbook as listed in the first column of the Masterlist. Within each sheet, the macro should look two different columns for the items listed in the second column of the Masterlist and replace it with the items corresponding replacement.

My code is below. The lines of code are commented out and labelled which do and don't work.

I was trying to get the value from Masterlist A2 into Output A2. Everything except the line labelled as correct will get an error, but I need this to function with integer variables eventually.

Sub Test()

    Dim Masterlist As Worksheet
    Dim Output As Worksheet
    
    Set Masterlist = ThisWorkbook.Worksheets("Masterlist")
    Set Output = ThisWorkbook.Worksheets("Output")
    
    'This works: Sheets("Output").Range("A2").Value = Sheets("Masterlist").Range("A2").Value
    
    
    'These don't work:
        
    ' Output.Cells(1, 0).Value = Masterlist.Cells(1, 0).Value
    ' Sheets("Output").Offset(1).Range("A1").Value = Sheets("Masterlist").Offset(1).Range("A1").Value
End Sub

I get the same error when trying to use the expression below

IsEmpty(Sheets("Masterlist").Cells(DateRow, 1))

Range("A2") is the equivalent of Cells(2,1) (numbering of rows and columns starts at 1, not 0) — cybernetic.nomad
– cybernetic.nomad, Commented Jul 9 at 16:02
Sheets("Output").Offset(1) worksheets do not have an Offset method, only Ranges have that. — Tim Williams
– Tim Williams, Commented Jul 9 at 16:03
Thank you for the answers, everyone!! I'm very new to this, so I really appreciate it. Thank you! — SongBird
– SongBird, Commented Jul 10 at 14:29

FunThomas · Accepted Answer · 2025-07-10 06:43:22Z

Just to summarize what was written in the comments:

When you use Cells, the numbering starts at 1 (not at 0), and the first index is the row. So Range("A2") is the same as Cells(2, 1). You can, btw, also write Cells(2, "A"), but that's not very handy in most cases.

Output.Cells(2, 1).Value = Masterlist.Cells(2, 1).Value

Using Cells has the advantage that you can easily work with variables for rows and columns, so for example

Dim row As Long, Col as Long
col = 1
For row = 2 to 10
    Output.Cells(row, col).Value = Masterlist.Cells(row, col).Value
Next row

You can use Offset always relative to a Range (it is a method of Range, not of Worksheet). For example

Sheets("Masterlist").Range("A1").Offset(1, 0) = ...  ' This will write into A2

Collectives™ on Stack Overflow

Why does only .Range("A2") work when I'm setting a value between two sheets? How can I make variable references work?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related