Setting formulas through VBA

[rdw72777]

So I'm still re-writing some code froma macro we use and I'm trying to make it more adaptable to future changes in the workbook.

I have the following code:

ActiveCell.Offset(0, 2).Value = "=IF(OR(RC[-2]=""Unplanned"",RC[-2]=""Cancelled""),0,IF(OR(RC[-2]=""Completed"", RC[-2]=""Terminated""), RC[5],VLOOKUP(RC[-5],'PCD Work Packages'!C[-5]:C[-1],5,0)))"

It creates the following formula:

'NOTE: this is the formula that results from the above code, it is not VBA
=IF(OR(K5="Unplanned",K5="Cancelled"),0,IF(OR(K5="Completed", K5="Terminated"), R5,VLOOKUP(H5,H:L,5,0)))

This code works fine and puts the correct formula in the correct cell, but I'm a little concerned that if the columns ever move/change this formula goes awry.

If in my VBA variables I've setup the following:

column K is known as DS_ActStatus_Col
column R is known as DS_YTDActuals_Col
column H is known as DS_WPC_Col
column L is known as DS_IntMed_Col
and row 5 is known as myDSStartRow

Is it possible to write a code that will mimic what my above code does (create the formula correctly) using the variables in my VBA (listed above). I've never tried to set cell formulas through VBA using VBA variables before, so I'm not sure what to do.

For those who can't follow/understand my example, if you have your own examples those would be appreciated; I can usually figure out the code of others

 

[Norie]

Ryan

I don't quite follow.

If the columns are going to change surely the formula and the code will need to change too.

The only thing I can think that might be of use would be to use named ranges for the columns.

Then when they are moved about they can still be referenced using their name instead of the cell/range reference.

Do you actually mean you want to incorporate your variables into the formula using code?

If you do then I'm not sure how you would do that with R1C1 referencing.

That's what your code uses at the moment, and the references in the formula could depend on where you put the formula.

 

[rdw72777]

The variables for the columns and rows are set in the macro. Basically earlier in the macro I go through all cells on my header row and if the absed on teh column name set the variable to that column

For instance, in my prior example, all of the columns variables as set in VBA would adapt if the columns moved/changed order. So I've taken care of that.

ANY example of using VBA variables (of any kind) to create formulas to put in a cell on the spreadsheet would be helpful....I personally don't care if its R1C1 syntax or some other way. Just don't want my formulas to always choose column H if column H doesn't hold the same infor next month.

 

[AlphaFrog]

Try something like this...

    DS_ActStatus_Col = 11   ' column K
    DS_YTDActuals_Col = 18  ' column R
    DS_WPC_Col = 8          ' column H
    DS_IntMed_Col = 12      ' column L
    myDSStartRow = 5        ' row 5
    
    ' Example
    Range("D2").Select
    ActiveCell.Offset(0, 2).FormulaR1C1 = "=IF(OR(RC" & DS_ActStatus_Col & _
                                          "=""Unplanned"",RC" & DS_ActStatus_Col & _
                                          "=""Cancelled""),0,IF(OR(RC" & DS_ActStatus_Col & _
                                          "=""Completed"", RC" & DS_ActStatus_Col & _
                                          "=""Terminated""), RC" & DS_YTDActuals_Col & _
                                          ",VLOOKUP(RC" & DS_WPC_Col & _
                                          ",'PCD Work Packages'!C" & DS_WPC_Col & _
                                          ":C" & DS_IntMed_Col & _
                                          ",5,0)))"

 

[rdw72777]

This is where I ended up. I basically took all of my column numbers in my Excel variables and converted them to column letter using the ColumnLetter function i found posted on this site.

All this for a simple 2-deep nested if formula...come VBA, there has to be a better way.

ActiveCell.Offset(0, 2).Value = "=IF(OR($" & DS_ActStatus_Col_Letter & myDSStartRow & "=""Unplanned"", $" _
                & DS_ActStatus_Col_Letter & myDSStartRow & "=""Cancelled""),0," & _
                "IF(OR($" & DS_ActStatus_Col_Letter & myDSStartRow & "=""Completed"", $" & _
                DS_ActStatus_Col_Letter & myDSStartRow & "=""Terminated""), $" & DS_YTDActuals_Col_Letter & _
                myDSStartRow & ", VLOOKUP($" & DS_WPC_Col_Letter & myDSStartRow & _
                ",'PCD Work Packages'!$" & PCDWP_WPC_col_Letter & myPCDWPHeaderRow & ":$" & _
                PCDWP_STDEst_Col_Letter & PCDWPDataRows & ", " & _
                (PCDWP_STDEst_Col - PCDWP_WPC_col + 1) & " ,0)))"

For fun, here is the columnLetter function, again not my code by just as an FYI

Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

 

[AlphaFrog]

Here's a shorter version of the ColumnLetter function for what it's worth...

Function ColumnLetter(ColumnNumber As Long) As String
    ColumnLetter = Replace(Cells(1, ColumnNumber).Address(0, 0), "1", "")
End Function

Or you could just replace say this...
DS_ActStatus_Col_Letter & myDSStartRow

...with
Cells(myDSStartRow, DS_ActStatus_Col).Address(0, 0)

 

[Norie]

Ryan

There is one way you could make the code shorter - don't use the ColumnLetter function and use the column number.

That's what's AlphaFrog has done as far as I can see.

Couple of other options:

1 Use named ranges in the formula and you could create those named ranges earlier in your code.

2 Don't use a nested IF formula.

I'll admit that 2 might be a bit tricky but I think it can be done.