locked
Trying to Dynamically set a formula into a column using VBA - Len function RRS feed

  • Question

  • I'm using Excel 2013 and I'm trying to dynamically set a formula into a cell using a user form.

    The purpose of the userform is to insert data into the sheet

    I have a column that I want a formula put into it. The formula will be Len(a2) for example. Since the row will be determined during runtime using the user form, I don't know the correct syntax in VBA to code this. Below is one variation that I have tried.

    ActiveSheet.Cells(emptyRow, 5).Formula = "=Len(ActiveSheet.Cells(emptyRow, 3))"

    The above returns #NAME? into the cell after I insert the new row.

    The column for this formula will always be the same column. It's just the row that will be determined during runtime.

    What would be the correct syntax to set the row/column combination to equal =Len(A1) where the row will dynamically change, but the column will always stay the same?

    Thank you

    Keith


    Keith Aul

    Friday, May 1, 2015 5:25 PM

Answers

  • Hello Keith,

    You are referencing column C (or column 3) in the example formula and then later in your text you refer to cell A1.

    Going back to the formula example. I am assuming that you simply want to insert a formula in cell emptyRow, column 5 that will return the length of the string in emptyRow, column 3. Is this assumption correct? If so then the following.

    The following inserts a formula with Relative addressing.

    ActiveSheet.Cells(emptyRow, 5).Formula = "=LEN(" & Cells(emptyRow, 3).Address(0, 0) & ")"

    If empytRow = 6 (Meaning the string will be in cell C6) then the formula will be in cell E6. The formula in cell E6 will be like the following.

    =LEN(C6)

    The following inserts a formula with absolute addressing.

    ActiveSheet.Cells(emptyRow, 5).Formula = "=LEN(" & Cells(emptyRow, 3).Address & ")"

    The formula in the cell will be like the following.

    =LEN($C$6)


    Regards, OssieMac

    • Marked as answer by KeithAul Monday, May 4, 2015 3:25 AM
    Sunday, May 3, 2015 1:54 AM

All replies

  • I don't understand what you want to do. Give examples of the resulting formula as you would want it to appear and describe the logic that makes the formula change.
    Saturday, May 2, 2015 8:19 AM
  • Hello Keith,

    You are referencing column C (or column 3) in the example formula and then later in your text you refer to cell A1.

    Going back to the formula example. I am assuming that you simply want to insert a formula in cell emptyRow, column 5 that will return the length of the string in emptyRow, column 3. Is this assumption correct? If so then the following.

    The following inserts a formula with Relative addressing.

    ActiveSheet.Cells(emptyRow, 5).Formula = "=LEN(" & Cells(emptyRow, 3).Address(0, 0) & ")"

    If empytRow = 6 (Meaning the string will be in cell C6) then the formula will be in cell E6. The formula in cell E6 will be like the following.

    =LEN(C6)

    The following inserts a formula with absolute addressing.

    ActiveSheet.Cells(emptyRow, 5).Formula = "=LEN(" & Cells(emptyRow, 3).Address & ")"

    The formula in the cell will be like the following.

    =LEN($C$6)


    Regards, OssieMac

    • Marked as answer by KeithAul Monday, May 4, 2015 3:25 AM
    Sunday, May 3, 2015 1:54 AM
  • Thank you very much for your solution. I'm glad you posted with examples for both relative and absolute addressing. This solution corrected the problem I had

    Keith


    Keith Aul

    Monday, May 4, 2015 3:26 AM