Excel VBA run time error 1004 on Range object

Question

Trying to count whether or not something is present within a grid in excel thats defined by whether or not it is filled with a color or not (thats how my boss wants it formatted). Not really an expert at VBA, and am getting an error in the second line of this loop. Variables are defined before this, and theres a bunch of other stuff inside the loop, but I'm not including that to save space. I'm pretty sure the problem would be evident here.

With ThisWorkbook.Worksheets("Core List") Do While Not .Range(Cells(3, x)).Value = "" 'while current name box isn't empty name1 = .Range(Cells(3, x)).Value 'sets the name in the stat sheet ThisWorkbook.Worksheets(stat).Range(Cells(4, x - 2)) = name1 Loop End With

Is stat a variable ? or a name of a sheet ? Also you might need to simply add .Value to the end of that line — Holmes IV
– Holmes IV, Commented Jul 7, 2015 at 16:43
Need to know what you've declared your variables are, how you're setting them and what line the error is occurring on. — Gareth
– Gareth, Commented Jul 7, 2015 at 16:45
Is your clipboard fill when you are attempting the count? If so, It may help to count before you are attempting a copy/paste — Chris
– Chris, Commented Jul 7, 2015 at 17:07

Daniel Möller · Accepted Answer · 2015-07-07 16:46:31Z

If the sheet where the code is in is not the same as "Core List", you just need to use .Cells, see:

With ThisWorkbook.Worksheets("Core List") Do While Not .Cells(3, x).Value = "" 'while current name box isn't empty name1 = .Cells(3, x).Value 'sets the name in the stat sheet ThisWorkbook.Worksheets(stat).Cells(4, x - 2).Value = name1 Loop End With

1 - You don't need to put Cells inside Range if you want only one cell. And if you use Cells loose like that, it will take the cell from the sheet where you inserted this code. But if you use .Cells, you take it from the sheet you declared in the With block.

Notice: if x is zero or lower, it will fail, there is no column 0 in a sheet. Also, if it's too big (I believe 256), you will too blow the limits of the sheet.

Collectives™ on Stack Overflow

Excel VBA run time error 1004 on Range object

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related