Search code examples
vbaexcelexcel-2013

VBA - Can anyone explain why I get object errors with Cells()?

All, I am having extreme trouble understanding why I am getting object errors. Every time I read something, I think I have it figured out, and when I try to implement I get an error. The particular code I am working with right now is:

'Set station cell value
iRow = rEmptyStation.Row
iColumn = rEmptyStation.Column
Cells(iRow - 1, iColumn).Copy
Range(Cells(iRow, iColumn), Cells(iRow, iColumn)).Select
Selection.Paste
rEmptyStation.Value = sStation

iRow and iColumn are of type long, rEmptyStation is a range, and sStation is a string. All I'm trying to do is copy the cell above the cell in question, paste it to the cell in question (for it's formatting), and then set the cell in question equal to a string.

It gives me an object error on the line where I have [junk].Select. In place of [junk], I have tried putting Cells(iRow, iColumn), I have tried using a With Worksheets(1) and .Cells(iRow, iColumn) statement, and I've also tried using .Cells() and .Range() in the With statement.

Can ANYONE explain to me how to get this to work, and how to choose the right code in every situation????

Solution

  • What ended up working for me was switching my code to the following:

    'Set station cell value
iRow = rEmptyStation.Row
iColumn = rEmptyStation.Column
Cells(iRow - 1, iColumn).Copy
Cells(iRow, iColumn).Select
Selection.PasteSpecial xlPasteFormats
rEmptyStation.Value = sStation

    Now, I don't know why, but when I have the code as the following, I get the object error (at the Selection.Paste line):

    'Set station cell value
iRow = rEmptyStation.Row
iColumn = rEmptyStation.Column
Cells(iRow - 1, iColumn).Copy
Cells(iRow, iColumn).Select
Selection.Paste
rEmptyStation.Value = sStation

    Here is a link to my project in its entirety. If anyone can figure that out, that'd be great (the code after this snippet in question is NOT TESTED!).