Help with Worksheet if function in vba

[Liz_I3]

HI
working with excel 2007 can't get my worksheet if function to work when running vba macro

This gives me a compiler error expected end of statement
ActiveWorkbook.Worksheets(z).Cells(i, 12).Formula = "=IF(D" & i&"=""Door"",""Door"",IF(D" & i&"=""Windows"",""Windows"",IF(MID(K" & i&",SEARCH(""@"",K" & i&",1)+1,5)=""Yes"",""Yes"",""No"")))"

Thanks
L

 

[JoeMo]

Haven't looked at this long enough, but as a quick fix try:

ActiveWorkbook.Worksheets(z).Cells(i, 12).Formula = "=IF(D" &  i & "=""Door"",""Door"",IF(D" &  i & "=""Windows"",""Windows"",IF(MID(K" & i  & ",SEARCH(""@"",K"  & i&",1)+1,5)=""Yes"",""Yes"",""No"")))"

 

[Liz_I3]

Thanks Joe
I did not realize that the spaces were importance.

2 more questions:

I have 11 different worksheet functions they are all working but this one

ActiveWorkbook.Worksheets(z).Cells(i, 14).Formula = "=IF(E" & i & "<>""Resolved"",NETWORKDAYS(V" & i & ",TODAY(),Holidays)-1,"")"

returns a runtime error Application difined or Object defined error. I don't understant it looks the same as all my other statements?

Also I have these worksheet function in a loop, but it takes a very long time to run. I have over 12000 records. Is there a faster way to execute the code?

Thanks so much for your help

L

 

[JoeMo]

Liz,
As for question 1, try:

ActiveWorkbook.Worksheets(z).Cells(i, 14).Formula = "=IF(E" & i  & "<>""Resolved"",NETWORKDAYS(V" & i &  ",TODAY(),Holidays)-1,"""")"

For question 2: If you post your code and a brief explanation of what you want to accomplish it would make an answer more likely.

 

[Liz_I3]

Thanks again that worked

here is my code. it works but very very slow

Sub AddFormulaColA()
Dim i As Integer
Dim st As String
Dim z As String

Application.ScreenUpdating = False
Sheets("Data ").Activate

z = ActiveWorkbook.ActiveSheet.Name
st = "xx"
i = 1

If Len(st) = 0 Then
Exit Sub
Else
Do While ActiveWorkbook.Worksheets(z).Cells(i, 1).Value <> "END"
i = i + 1
If ActiveWorkbook.Worksheets(z).Cells(i, 1).Value = "END" Then
Exit Sub
Else

st = ActiveWorkbook.Worksheets(z).Cells(i + 1, 12).Value
If ActiveWorkbook.Worksheets(z).Cells(i, 12).Value = "" Then

ActiveWorkbook.Worksheets(z).Cells(i, 14).Formula = "=IF(E" & i & "<>""Resolved"",NETWORKDAYS(V" & i & ",TODAY(),Holidays)-1,"""")"
ActiveWorkbook.Worksheets(z).Cells(i, 15).Value = 1
ActiveWorkbook.Worksheets(z).Cells(i, 16).Formula = "=IF(WEEKDAY(B" & i & ")=7,""Yes"",""No"")"
ActiveWorkbook.Worksheets(z).Cells(i, 17).Formula = "=IF(WEEKDAY(B" & i & ")=1,""Yes"",""No"")"
ActiveWorkbook.Worksheets(z).Cells(i, 18).Formula = "=IF(P" & i & "=""No"",B" & i & ",WORKDAY(B" & i & ",1))"
ActiveWorkbook.Worksheets(z).Cells(i, 19).Formula = "=IF(Q" & i & "=""No"",B" & i & ",WORKDAY(B" & i & ",1))"
ActiveWorkbook.Worksheets(z).Cells(i, 20).Formula = "=MAX(R" & i & ":S" & i & ")"
ActiveWorkbook.Worksheets(z).Cells(i, 21).Formula = "=IF(ISNA(MATCH(T" & i & ",Holidays,0))=TRUE,MAX(S" & i & ":T" & i & "),WORKDAY(MAX(S" & i & ":T" & i & "),1,Holidays))"
ActiveWorkbook.Worksheets(z).Cells(i, 22).Formula = "=WORKDAY(U" & i & ",O" & i & ",Holidays)"
ActiveWorkbook.Worksheets(z).Cells(i, 13).Formula = "=IFERROR(aged(N" & i & "),""RESOLVED"")"


End If
End If
Loop
End If


End Sub

 

[JoeMo]

Because you are adding many formulas to your sheet as the macro progresses if calculation is set to automatic, it's likely the calculate time is what's slowing things down.

Replace the Application.Screenupdating line at the start of your code with:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Then just before the end sub line add this:

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

 

[Liz_I3]

Thanks Joe
That did help a bit. It is a large workbook so I guess I just have to live with it. At least I only have to update it once a day.

I also only had the formulas copies into row 2 and then copy and pasted them down to the last record on the worksheet. That seemed a bit faster.

Thanks for all your help it was really appreciated..

L