Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA formulas (Excel 200 or 2003)

    Hi All, me again ...

    I have dynamically placed a cell link in cell AH3 via VBA code. Now, I need to determine the row number of the formula in this cell and add a constant to it.
    Let's say,

    Constant = 10
    AG3 contains this formula /link.............. =E8
    AH3 should contain this formula/link.... =E18 (8 + Constant)

    I do not know how to obtain the '8' from the formula in cell AG3. Is this possible? If not, I will try another way ...

    Thanks for any help ...
    --cat

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: VBA formulas (Excel 200 or 2003)

    Check out the Row function
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA formulas (Excel 200 or 2003)

    It might help if we knew what code you used to place a formula in AG3.

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA formulas (Excel 200 or 2003)

    Hi Catherine,

    The Row function gives me the number 3 ... since the cell is AG3.

    I need to grab the '8' from the formula '=E8'

    Thanks,
    --cat

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA formulas (Excel 200 or 2003)

    Hi Hans,

    In another macro, the formula "=E8" was placed in cell AH3. Please note, though, that E8 is not always constant .

    The formula was placed using:

    Range("AH3").formula = "=E" & nextrow

    to place the formula.

    Now, I need to know that row number ...
    I cannot get to this macro in order to keep track of the row number when the link is placed in cell AH3 ... which would be the best way to do this.

    So, I was wondering if there is a way to "pull" the row number out of the formula. The "E" will always be the same ...

    Does any of this make any sense?

    Thank you very much for your time and effort ...

    --cat

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA formulas (Excel 200 or 2003)

    I'm not sure I understand your description, but try something like this:

    Dim lngRow As Long
    ' Retrieve row
    lngRow = Val(Mid(Range("AH3").Formula, 3))
    ' Add 10
    lngRow = lngRow + 10
    ' Change formula
    Range("AH3").Formula = "=E" & lngRow

Posting Permissions

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