Results 1 to 7 of 7

Thread: [RESOLVED] Getting current Cell Address in Excel

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Resolved [RESOLVED] Getting current Cell Address in Excel

    I'm new to VBA and Office programming. I have an Excel spreadsheet that contains a command button. I want this button to insert a literal value into specific cells only. My cursor will be setting on the targeted cell and then I will click on the command button to insert the literal value. My problem is, how do I know which cell I'm setting on? Is there a function that can tell me what cell my cursor is occupying?

    Thanks,
    Blake

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Getting current Cell Address in Excel

    activecell
    or activecell.address to return the location
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: Getting current Cell Address in Excel

    The ActiveCell refers to current cell. So you can use ActiveCell.Address to get the address of current cell.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Getting current Cell Address in Excel

    What I am trying to do is this

    Code:
        For x = 6 to 25
             If ActiveCell.Address Then
                  Cells(x, 3) = "PAID"
             End If
        Next x
    I have a range of cells starting at row 6 column 3 (it will always be column 3). If the cursor is setting on one of these row/columns, then I wish to be able to click a button and have it insert "PAID" in the cell.
    Blake

  5. #5
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: Getting current Cell Address in Excel

    Code:
        For x = 6 to 25
             If ActiveCell.Row = x And ActiveCell.Column = 3 Then
                  ActiveCell.FormulaR1C1 = "PAID"
             End If
        Next x
    Last edited by Pradeep1210; Feb 9th, 2009 at 04:32 PM.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  6. #6
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: Getting current Cell Address in Excel

    Better still:
    Code:
    If ActiveCell.Column = 3 Then
         If ActiveCell.Row >= 6 And ActiveCell.Row <= 25 Then
              ActiveCell.FormulaR1C1 = "PAID"
         End If
    End If
    You don't need to loop thru the cell in this way.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Getting current Cell Address in Excel

    that worked Pradeep1210...thanks!!!!
    Blake

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width