Long formula in VBA

[schang_825]

Hi all,

I have created a spreadsheet that contains a summary sheet of all of the projects our company is currently working on as well as individual tabs detailing schedules and costs. When I add a new project to this spreadsheet, I have added a button that creates a new tab with pre-filled information as well as adds the project to the summary sheet with formulas linking to the new project's tab.

I have come across a problem with a certain formula I would like to add to the summary sheet. The formula works if I manually enter it into the cell, but when I want to add it via VBA, I get an "invalid character" error or and "Expected end of statement" error...

This is the formula that I would like to add upon creating a new project:

Worksheets(1).Cells(i + 2, "E").Formula = "=IF(AND(E$2>='" & ShtTitle & "'!$G19,E$2<='" & ShtTitle & "'!$H19),TEXT('" & ShtTitle & "'!$F31, "$#,##0") & CHAR(10) & "Pre-Con","")"

Worksheets(1).Cells(i + 3, "E").Formula = "=IF(AND(E$2>='" & ShtTitle & "'!$G20,E$2<='" & ShtTitle & "'!$H20),TEXT('" & ShtTitle & "'!$F31, "$#,##0") & CHAR(10) & "Const","")"

Worksheets(1).Cells(i + 4, "E").Formula = "=IF(AND(E$2>='" & ShtTitle & "'!$G21,E$2<='" & ShtTitle & "'!$H21),TEXT('" & ShtTitle & "'!$F31, "$#,##0") & CHAR(10) & "Post-Con","")"

I get the invalid character error on the $ and #. In the summary sheet, I have dates (months) going across and the projects down in Column D. The formula is looking at the month at the top of the sheet and if it is within the dates specified in the Project's tab, it lists the $$ amount for that month and whether it is "Pre-Con", "Const" or "Post-Con." I then have conditional formats that colors the cells based on this criteria.

Any help would be greatly appreciated!

Thanks!

 

[cstimart]

Shouldn't it be...


Worksheets(1).Range("E" & i + 2).Formula = ......


What does the formula look like when in the cell?

 

[AlphaFrog]

Your formulas in VBA are text strings surrounded by double quotes. If you want a literal double quote as part of the formula within the string, you represent that literal quote by two double quotes so VBA doesn't think it's the end of your string.

Example (not tested):
Worksheets(1).Cells(i + 2, "E").Formula = "=IF(AND(E$2>='" & ShtTitle & "'!$G19,E$2<='" & ShtTitle & "'!$H19),TEXT('" & ShtTitle & "'!$F31, ""$#,##0"") & CHAR(10) & ""Pre-Con"","""")"

 

[schang_825]

Thank you, that worked!

However, I do have one more question. I wanted to fill the row with those formulas. Currently, it is filling in the formulas in the 3 cells, but the formula that I have to fill the rows does not seem to be working:

Range("E" & i + 2, "E" & i + 4).AutoFill Destination:=Range("E" & i + 2, "X" & i + 4), Type:=xlFillDefault


Any ideas?

 

[AlphaFrog]

Try this...

Worksheets(1).Range("E" & i + 2, "E" & i + 4).AutoFill Destination:=Worksheets(1).Range("E" & i + 2, "X" & i + 4), Type:=xlFillDefault

 

[schang_825]

Goodness...such a simple fix. Thank you!!

 

[Jonmo1]

You might also try putting the formula in ALL cells in one stroke

With Worksheets(1)
    .Range(.Cells(i + 2, "E"), .Cells(i+4, "X")).Formula = "=IF(AND(E$2>='" & ShtTitle & "'!$G19,E$2<='" & ShtTitle & "'!$H19),TEXT('" & ShtTitle & "'!$F31, ""$#,##0"") & CHAR(10) & ""Pre-Con"","""")" 
End With

Hope that helps.

 

[schang_825]

Hi again,

I just came across another problem. I realized that I may need to add columns in the future, so I wanted to be able to autofill the formulas to the last used column (which is a totals column). This is the formula I have:

Dim LastColumn As Integer

If Worksheets(1).WorksheetFunction.CountA(Cells) > 0 Then
LastColumn = Worksheets(1).Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If

Worksheets(1).Range("E" & i + 2, "E" & i + 4).AutoFill Destination:=Worksheets(1).Range("E" & i + 2, LastColumn & i + 4), Type:=xlFillDefault

I can't seem to figure out the problem...Any ideas?

Thanks!

 

[AlphaFrog]

Try this...
Destination:=Worksheets(1).Range("E" & i + 2, Worksheets(1).Cells(i + 4, Lastcolumn))

Or this...
Destination:=Worksheets(1).Range("E" & i + 2).Resize(3, Lastcolumn - 4)

 

[schang_825]

Thanks that worked great!! I used the first one.