[Solved] VBA: Entering formula with relative row ref?

    I have a formula as follows:
    =LEFT(A2&".0.0.",FIND("$",SUBSTITUTE(A2&".0.0.",".","$",3))-1)


    This takes a long string of numbers in A formats into something meaningful to what I'm doing. Naturally, when I enter this in B2 and run it to the bottom, the cell reference changes and picks up the adjacent cell data al the way down.


    I'm trying to create a macro that will enter this in any cell and pick up the ActiveCell row reference. So when entering the formula in B2003, the cell reference would be "A & ActiveCellRow", and it would pick up the data in A2003.


    I've tried several ways, but get lost in parenthesis, quotes and
    double-quotes, and VBA Expected End of Statement errors.


    Any assistance is greatly appreciated.


    Ed

    Without seeing your code it's a bit difficult to comment accurately, but one thing to keep in mind, is that if you are using VBA to enter a formula into cell B2003, that cell B2003 may or may not be the active cell. It depends on how your code is written.


    As you debug your code, make sure you are keepingtrack of which cell(range) is active and what is selected. They might not be the same, and they may not be the object your currently are working on.

    When I would activate the macro to enter the formula, that cell would be clicked on and activated. That cell reference would be showing in the top left area.


    One thing I tried was:


    Dim thisRow As Integer


    thisRow = ActiveCell.Row


    ActiveCell.Formula = "=LEFT(B" & thisRow & "& "".0.0."" ",FIND("$",SUBSTITUTE(B" & thisRow & "& "".0.0."",""."","$",3))-1)


    trying to pick up the Row reference and use it in the formula. But I keep getting errors - either I've got parentesis in the wrong place, too many or not enough quotes, or Expected End of Statement.


    I was reluctant to post this code, because I didn't want someone to think I was stuck on making *that* work, when I don't know enough yet to know if that's even the right way to do it.


    If you can help at all, I would be very grateful.


    Ed

    rather than trying to code in the row, it might be easier to simply copy and paste special from a cell that you know has the correct formula. For example, the following code will paste (with relative referencing addjusted) the formula in cell B2 into what cell is selected when the macro is run.


    Code
    Sub CopyB2Formula()
    Range("B2").Copy
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login