Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

End up in the "A" Column

Posted on 2014-02-27
11
Medium Priority
?
208 Views
Last Modified: 2014-03-04
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!
0
Comment
Question by:BostonBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 39893638
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

Open in new window

0
 

Author Comment

by:BostonBob
ID: 39893680
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
a1 = Target.Address

'''' 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!
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39893697
The correct reference should be

Cells(r1, 1).select

Right before the End Sub.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BostonBob
ID: 39893905
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,
0
 
LVL 10

Expert Comment

by:etech0
ID: 39893946
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.
0
 

Author Comment

by:BostonBob
ID: 39893962
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
0
 

Author Comment

by:BostonBob
ID: 39893971
Even tried:

sheet2.cells(r1, 1).Select

No dice....
0
 
LVL 10

Expert Comment

by:etech0
ID: 39893981
can you try changing r1 to the number 10, and see if it works then? Let's try to narrow down the problem...
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 39894434
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
    a1 = Target.Address

    '.....
    '.....
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    Cells(r1, 1).Select
End Sub

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39894601
If you want to play with the activecell then try

cells(activecell.row,1).select
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39904909
noted that changed cell is not necessarily the activecell. (but probably is)
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question