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, Jul 07 '15 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, Jul 07 '15 at 16:45
See [Run-time error '1004' while copying](http://stackoverflow.com/questions/31251910/run-time-error-1004-while-copying/31251977) from yesterday. — , Jul 07 '15 at 16:52
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, Jul 07 '15 at 17:07

score 1 · Accepted Answer · answered Jul 07 '15 at 16:46

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.

Thanks! The .Cells thing fixed it. – fredark Jul 07 '15 at 16:56 — fredark, Jul 07 '15 at 16:56

Excel VBA run time error 1004 on Range object

1 Answers1