Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: insert formula with vb

    Hello, ppl

    I have 2 sets of columns - one with values and one with formulas that use values. Formulas are meant to change depending on a certain condition so I want to insert them with VB macro.

    For example I want cells in column E to equal cell B- cellA from the same row.
    So, questions:
    1)how do I explain to vb that I want this formula applied to whole range (i.e. how do I define row number)
    2) How do I define column name - in my prog I use column numbers - is there any way to translate column number into column name (1=A, 2=B)

  2. #2
    Join Date
    Sep 2004
    Posts
    16
    in case someone else has similar problem the solution is
    Selection.FormulaR1C1=R[-5]-R[-6]
    where number in brackets is disstance to left of cell in question measured in columns

  3. #3
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    The quickest way to get the correct syntax for almost any formula in VBA is to record a macro, F2 on the cell containing the formula, then enter and stop recording.

    That will give you the R1C1 rference style that you posted. You can use actual cell references as well, and you can also look into the helpfile for Application.WorksheetFunction.

    Hope that helps,

    Smitty

Posting Permissions

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