CODEWITHSUNDEEP
excelfunctionvbarangecell
user2572734
user2572734

Reputation: 51

Using the "Cells" function in excel vba

I am trying to select a cell in excel using vba through the use of the range function.

I have done this many times before even with using variables to specify the range instead of just numbers.

However, I cannot seem to select a range using the cells function.

I know that this is possible as I have done much research and have seen other people's code that will successfully run using this function.

Below is the syntax I'm using to try and select the range (which is only 1 cell).

Additionally, when i run the code i get the following message:

"Run-time error '1004': Method of 'Range' of object'_Global' failed".

Thank you for any and all help.

Dim item As String
Dim itempaste As Boolean
Dim itemcnt As Integer

itemcnt = 1
itempaste = False
Range("X3").Select
Do
    item = ActiveCell.Value
    If item <> "" Then
        Range("A18").Select
        Do
            If ActiveCell.Value = "" Then
                ActiveCell.Value = item
                itempaste = True
            Else
                ActiveCell.Offset(1, 0).Select
            End If
        Loop Until itempaste = True
    End If
    itemcnt = itemcnt + 2
    Range(Cells(1, (21 + itemcnt))).Select
Loop Until itemcnt = 11

Upvotes: 5

Views: 71947

Answers (1)

Geoff
Geoff

Reputation: 8850

You're using the Range type like a function; the Cells call is actually returning you a Range object already. Instead of

Range(Cells(1, (21 + itemcnt))).Select

..try:

Cells(1, (21 + itemcnt)).Select

If you want to be more explicit:

Dim target As Range
Set target = Cells(1, (21 + itemcnt))
target.Select

Upvotes: 5

Related Questions