How can I select a cell, given its row and column number?

Question

I am trying to select a cell by giving which row and column to use.

It is giving me an error:

"Unable to get the select property of the range class."

when I get to this line:

Sheets("Ticker").Cells(currRow, etfCol).Select.Paste

Here is a short snippet of my really long code:

Dim etfCol As Integer, etfCount As Integer, currRow As Integer, currRowValue As String
etfCol = 1   'Just select the first column
etfCount = Sheets("Automated Table").Cells(Rows.Count, etfCol).End(xlUp).Row
'Repeat for every row.
For currRow = 5 To etfCount
    ''''''''''''''''''''''''''''''''''Copy and paste the ticker
    Cells(currRow, etfCol).Copy
    Sheets("Ticker").Cells(currRow, etfCol).Select.Paste
Next

Am I getting this error because "etfCount" is the value I got from "Automated Table" sheet, and I am trying to use that for "Ticker" sheet?
This is the only reason I could think of, but that doesn't wholly explain this error.

I tried debugging the code.

You need to [select the sheet](https://stackoverflow.com/a/3902841/11683) before you can select a range on that sheet. When you fix that, you will have error 424 "Object required" because `.Select` does not return a `Range`, so `Select.Paste` makes no sense. Once you fix that by moving `Select` and `Paste` onto different lines, please see https://stackoverflow.com/q/10714251/11683 for why you never needed `Select` in the first place. — GSerg, Aug 05 '19 at 23:21

Gary's Student · Answer 1 · 2019-08-05T23:35:51.537

1

Replace:

Sheets("Ticker").Cells(currRow, etfCol).Select.Paste

with:

Sheets("Ticker").Cells(currRow, etfCol).Paste

This assumes that Sheets("Automated Table") is active.

edited Aug 05 '19 at 23:35

answered Aug 05 '19 at 23:29

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · answered Aug 05 '19 at 23:35

If you are using .Select (which is not a good practice in VBA), you may profit a lot by the Macro Recorder. Just record the copy and the paste and examine the code:

Sub Makro4()
    Range("B4").Select
    Selection.Copy
    Range("G8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

As you see, the .Select and the .Paste are on various lines and so they should stay. In your code it should be:

Sheets("Ticker").Cells(currRow, etfCol).Paste Sheets("Ticker").Cells(currRow, etfCol).Paste

Or you may do it a bit better:

With Sheets("Ticker").Cells(currRow, etfCol)
     .Select
     .Paste
End With

Anyway, as pointed out in the link, the usage of Select is not to be encouraged. (Although it works).

How can I select a cell, given its row and column number?

2 Answers2