Help with Worksheet if function in vba

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Haven't looked at this long enough, but as a quick fix try:
Code:
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"")))"
 
Upvote 0
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
 
Upvote 0
Liz,
As for question 1, try:
Rich (BB code):
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.
 
Upvote 0
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
 
Upvote 0
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:
Code:
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Then just before the end sub line add this:
Code:
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,294
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top