Select Cell Dynamically in Excel VBA

Question

Hi! I have the following bit of code that, when I hit the ENTER key in column G, just selects the first three cells in the row, turns them to values, and then moves the cursor to column D on the next row. While perhaps not the best or fastest way to do it, has been working well for a couple of years.

    If Target.Column = 7 And Target.Row = lngBottomRow Then

        Range(ActiveCell.Offset(lngBottomRow - CursorRow, -(CursorColumn - 1)), _
            ActiveCell.Offset(lngBottomRow - CursorRow, -(CursorColumn - 3))).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveCell.Offset(1, 3).Select

        Save_File

    End If

Sometimes I add comments in column J, and today I decided to add similar functionality so that when I hit the ENTER key and the cursor is in column J, it will save the file and move the cursor back to the next cell for data entry. I figured that I could just take the range select command that I already have and modify it to select a single cell. Toward that end, I tried this:


    If Target.Column = 7 And Target.Row = lngBottomRow Then
        Range(ActiveCell.Offset(lngBottomRow - CursorRow, -(CursorColumn - 1)), _
            ActiveCell.Offset(lngBottomRow - CursorRow, -(CursorColumn - 3))).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveCell.Offset(1, 3).Select

        Save_File

    ' If the cursor is in column J, move the cursor to the next empty cell in
    ' column D and save the file.
    ElseIf Target.Column = 10 Then

        Range(ActiveCell.Offset(lngBottomRow - (CursorRow - 1), -(CursorColumn - 6))).Select
        ActiveCell.Offset(-1, -6).Select

        Save_File

    End If

When it hits the Range Select command in the ElseIf block, I get this error:

 Run-time error '1004':

 Method 'Range' of object'_Worksheet' failed.

Is the Range Select command not appropriate for selecting a single cell, or do I have a syntax error that I am missing?

Any help that you can offer will be greatly appreciated!

--Tom

Answer

I was able to resolve the issue.

First off, there was an error in my original post. In the ElseIf block, the line ActiveCell.Offset(-1, -6).Select should not be there. That was from an earlier version of the statement where I was just trying to see if it would select the correct cell. I simply forgot to remove it.

I have re-written the ElseIf block as follows, and it works without errors.

    ElseIf Target.Column = 10 Then

        Range(ActiveCell.Offset(lngBottomRow - (CursorRow - 1), -(CursorColumn - 4)), _
            ActiveCell.Offset(lngBottomRow - (CursorRow - 1), -(CursorColumn - 4))).Select

        Save_File

    End If

Select Cell Dynamically in Excel VBA

1 answer