# End up in the "A" Column

on
Hi All,

Fairly easy question today and low hanging fruit for most of you.  ...still hard for me.

Anyways,

If I have this kind of notation:

Worksheets("Brains").Range(a1).offset(0, 26).value = "0"
Worksheets("Brains").Range(a1).offset(0, 26).value = """"
Worksheets("Brains").Range(a1).offset(0, 24).value = Worksheets("CC").Cells(r1, "V").value

'What would I need right here to have the cursor be in the relevant row and Column "A"?
'In other words, in the far left of the spreadsheet in the relevant row using the type of notation as above.

So  my questions are, as above, in the comments:

"What would I need right here (at the end of the code) to have the cursor be in the relevant row and Column "A"

The notation above where there are two equations (left and right) is there an actual name for this kind of notation?

thanks!
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
How are we determining which row you need?
Also, the Cells() property takes 2 numbers, not a number and a letter. The first number is the row number, and the second number is the column number. So if you wanted to select cell B1, you could do this:
``````cells(1,2).select
``````

Commented:
Sorry let me give you some more of the code:

Private Sub Worksheet_Change(ByVal Target As Range)

'''' GET THE ROW OF THE ACTIVE CELL

Dim r1 As String
r1 = Target.row

Dim a1 As String

'''' GET THE ADDRESS OF THE ACTIVE CELL

If Not Intersect(Target, Range("Z10:Z250")) Is Nothing Then
'
'    ''' DISABLE EVENTS SO WHEN THIS MACRO PASTES A FORMULAS IN A CELL, EVENT MACROS WILL NOT FIRE DURING THE
'    ''' EXECUTION OF THIS MACRO

Application.EnableEvents = False
Application.ScreenUpdating = False

''' PASTE FORMULAS IN THE DESGNITED CELLS
'Worksheets("Test7").Range(a).offset(0, 15).value = Target.offset(0, 15).value

Worksheets("Brains").Range(a1).offset(0, 26).value = "0"
Worksheets("Brains").Range(a1).offset(0, 26).value = """"
Worksheets("Brains").Range(a1).offset(0, 24).value = Worksheets("CC").Cells(r1, "V").value
'    Worksheets("Brains").Range(a).offset(0, 13).value = Worksheets("constants").Cells(r, "AQ").value
Worksheets("Brains").Range(a).offset(0, 16).Formula = "=if(" & S & r & "*" & U & r & "*" & V & r & "*" & V & r & "*" & V & r & ",1,0)"

'Target.offset(0, 10).Values = ""
'Target.offset(0, 12).value = ""
'Target.offset(0, 13).value = ""
'Target.offset(0,0).Values = ""
'Target.offset(0,-1).value = ""
'Target.offset(0, 3).value = ""

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

'Cursor should go to the farthest left point (or "A" column) in the relative cell.

End Sub

Hope this makes sense as this is how it was explained to me on this experts-exchange.

thanks!
Top Expert 2013

Commented:
The correct reference should be

Cells(r1, 1).select

Right before the End Sub.

Commented:
I am getting the message:

Run-time error '1004':  Select method of Range class failed.

I tried:

Worksheets("B O").cells(r1, 1).Select

AND

Worksheets("B O").cells(r1, "A"). Select

B O is the name of the sheet (yes, it has the space)

I am stumped.

thanks,

Commented:
When you get the error and the code stops, open the immediate window, and type ?r1 (yes, there's a question mark there). See what you get.

Commented:
Which ever row I do the test in it is correct.

So if I am testing in row 10 the immediate window gives a "10"

Same for 12
same for 17
Ect

Commented:
Even tried:

sheet2.cells(r1, 1).Select

No dice....

Commented:
can you try changing r1 to the number 10, and see if it works then? Let's try to narrow down the problem...
Commented:
here is my test. seems to have the desired effect on my test. the cursor ends up in Column A of whatever row the change was mad in.

not that i changed r1 to be a LONG as this is what the row property returns.

``````Private Sub Worksheet_Change(ByVal Target As Range)
'''' get the row of the CHANGED cell

Dim r1 As Long
r1 = Target.Row

Dim a1 As String

'.....
'.....
Application.EnableEvents = True
Application.ScreenUpdating = True

Cells(r1, 1).Select
End Sub
``````

Commented:
If you want to play with the activecell then try

cells(activecell.row,1).select

Commented:
noted that changed cell is not necessarily the activecell. (but probably is)

Do more with