Insert Formula into VBA Code

J

Jones1413

New Member
Joined
Jul 26, 2019
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am trying to insert the following formula in to my VBA and then have it auto fill down to row 2000. I get the following error:

"Compile error" Expected: end of statement"

When I receive this error it highlights the word "Invite" in "Invite to Round 1" in my IFS statement.

VBA Code: 
Sub Update_File()

Range("V1").Select
    ActiveCell.Name = "Consolidated Status"
    Range("V2").Select
    Columns("V:V").EntireColumn.AutoFit
    Range("V2").Formula = "=IFS(OR(E2="Invite to Round 1",E2="Invite to Round 2",E2="Invite to Round 3",E2="Invite to Round 4",E2="Invite to Round 5",E2="Invite to Round 6",E2="Round 1 Conducted", _
    E2="Round 2 Conducted",E2="Round 3 Conducted",E2="Round 4 Conducted",E2="Round 5 Conducted",E2="Round 6 Conducted"),"Interviewing",OR(E2="Offer Recommended", _
    E2="Offer Candidate Under Review by Approver",E2="Offer Candidate Flagged for Recruiter Review",E2="Offer Approved to Draft",E2="Generate Offer Letter", _
    E2="Offer Letter Revisions Required",E2="Verbal Offer Extended",E2="Writtend Offer Extended",E2="Offer Not Approved"),"Offer Stage",OR(E2="Offer Accepted", _
    E2="Ready to Onboard",E2="Hired"),"RTO/Hired",OR(E2="HireVue On-Demand interview selections",E2="HireVue On-Demand interview triggered", _
    E2="Invited to Recruiter Screen",E2="Recruiter Screen Scheduled",E2="Recruiter Screen Conducted",E2="Invited to Business Screen",E2="Business Screen Scheduled", _
    E2="Business Screen Conducted",E2="Meets Basics Qualifications"),"Screening")"
    Selection.AutoFill Destination:=Range("V2:V2000"), Type:=xlFillDefault
    Range("A1").Select

End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Without looking too far into the formula, ALL of your strings within that formula that have quotes around them, will need double quotes...like this

""Invite to Round 1""
 
Upvote 0
That worked. However, under the word "Range" it now highlights E2 and says "expected end of statement".
 
Upvote 0
sounds like your are missing a bracket within that formula......
I don't have EXcel at the moment,but does the formula work when typed into the cell ??
AND
you don't need to select cells to work with them...see below
VBA Code: 
Sub Update_File()
Range("V1") = "Consolidated Status"
    Columns("V:V").EntireColumn.AutoFit
    Range("V2:V2000").Formula = "=IFS(OR(E2=""Invite to Round 1"",E2=""Invite to Round 2"",E2=""Invite to Round 3"",E2=""Invite to Round 4"",E2=""Invite to Round 5"",E2=""Invite to Round 6"",E2=""Round 1 Conducted"", E2=""Round 2 Conducted"",E2=""Round 3 Conducted"",E2=""Round 4 Conducted"",E2=""Round 5 Conducted"",E2=""Round 6 Conducted""),""Interviewing"",OR(E2=""Offer Recommended"", _
    E2=""Offer Candidate Under Review by Approver"",E2=""Offer Candidate Flagged for Recruiter Review"",E2=""Offer Approved to Draft"",E2=""Generate Offer Letter"", _
    E2=""Offer Letter Revisions Required"",E2=""Verbal Offer Extended"",E2=""Writtend Offer Extended"",E2=""Offer Not Approved""),""Offer Stage"",OR(E2=""Offer Accepted"", _
    E2=""Ready to Onboard"",E2=""Hired""),""RTO/Hired"",OR(E2=""HireVue On-Demand interview selections"",E2=""HireVue On-Demand interview triggered"", _
    E2=""Invited to Recruiter Screen"",E2=""Recruiter Screen Scheduled"",E2=""Recruiter Screen Conducted"",E2=""Invited to Business Screen"",E2=""Business Screen Scheduled"", _
    E2=""Business Screen Conducted"",E2=""Meets Basics Qualifications""),"""",""Screening"")"
End Sub
 
Upvote 0
It does not work when typing it in to the cell. I've never had to use double "" with formulas before. Very confused.
 
Upvote 0
As well as doubling up on the quote marks you also need to break & re-join the text string for each line continuation. (There's no missing brackets)
Also,
- your named range line would not work like that as names cannot contain spaces so I'm wondering if you copied your actual code to the forum?
- selecting is rarely needed and slows your code considerably
- you can put all the formulas in at once rather then entering one and then filling down.
- notice that I have also used ".Formula2" not ".Formula"
- do you have a typo in that formula? Writtend Offer Extended

Anyway try this with a copy of your workbook.
VBA Code: 
Sub Update_File_v2()

  Range("V1").Name = "Consolidated_Status"
  Columns("V:V").EntireColumn.AutoFit
  Range("V2:V2000").Formula2 = "=IFS(OR(E2=""Invite to Round 1"",E2=""Invite to Round 2"",E2=""Invite to Round 3"",E2=""Invite to Round 4"",E2=""Invite to Round 5"",E2=""Invite to Round 6"",E2=""Round 1 Conducted""," _
  & "E2=""Round 2 Conducted"",E2=""Round 3 Conducted"",E2=""Round 4 Conducted"",E2=""Round 5 Conducted"",E2=""Round 6 Conducted""),""Interviewing"",OR(E2=""Offer Recommended""," _
  & "E2=""Offer Candidate Under Review by Approver"",E2=""Offer Candidate Flagged for Recruiter Review"",E2=""Offer Approved to Draft"",E2=""Generate Offer Letter""," _
  & "E2=""Offer Letter Revisions Required"",E2=""Verbal Offer Extended"",E2=""Writtend Offer Extended"",E2=""Offer Not Approved""),""Offer Stage"",OR(E2=""Offer Accepted""," _
  & "E2=""Ready to Onboard"",E2=""Hired""),""RTO/Hired"",OR(E2=""HireVue On-Demand interview selections"",E2=""HireVue On-Demand interview triggered""," _
  & "E2=""Invited to Recruiter Screen"",E2=""Recruiter Screen Scheduled"",E2=""Recruiter Screen Conducted"",E2=""Invited to Business Screen"",E2=""Business Screen Scheduled""," _
  & "E2=""Business Screen Conducted"",E2=""Meets Basics Qualifications""),""Screening"")"

End Sub
 
Upvote 0
Solution
Peter_SSs said:
As well as doubling up on the quote marks you also need to break & re-join the text string for each line continuation. (There's no missing brackets)
Also,
- your named range line would not work like that as names cannot contain spaces so I'm wondering if you copied your actual code to the forum?
- selecting is rarely needed and slows your code considerably
- you can put all the formulas in at once rather then entering one and then filling down.
- notice that I have also used ".Formula2" not ".Formula"
- do you have a typo in that formula? Writtend Offer Extended

Anyway try this with a copy of your workbook.
VBA Code: 
Sub Update_File_v2()

  Range("V1").Name = "Consolidated_Status"
  Columns("V:V").EntireColumn.AutoFit
  Range("V2:V2000").Formula2 = "=IFS(OR(E2=""Invite to Round 1"",E2=""Invite to Round 2"",E2=""Invite to Round 3"",E2=""Invite to Round 4"",E2=""Invite to Round 5"",E2=""Invite to Round 6"",E2=""Round 1 Conducted""," _
  & "E2=""Round 2 Conducted"",E2=""Round 3 Conducted"",E2=""Round 4 Conducted"",E2=""Round 5 Conducted"",E2=""Round 6 Conducted""),""Interviewing"",OR(E2=""Offer Recommended""," _
  & "E2=""Offer Candidate Under Review by Approver"",E2=""Offer Candidate Flagged for Recruiter Review"",E2=""Offer Approved to Draft"",E2=""Generate Offer Letter""," _
  & "E2=""Offer Letter Revisions Required"",E2=""Verbal Offer Extended"",E2=""Writtend Offer Extended"",E2=""Offer Not Approved""),""Offer Stage"",OR(E2=""Offer Accepted""," _
  & "E2=""Ready to Onboard"",E2=""Hired""),""RTO/Hired"",OR(E2=""HireVue On-Demand interview selections"",E2=""HireVue On-Demand interview triggered""," _
  & "E2=""Invited to Recruiter Screen"",E2=""Recruiter Screen Scheduled"",E2=""Recruiter Screen Conducted"",E2=""Invited to Business Screen"",E2=""Business Screen Scheduled""," _
  & "E2=""Business Screen Conducted"",E2=""Meets Basics Qualifications""),""Screening"")"

End Sub
Click to expand...
This worked great, thank you! How would I name the cell V1 to "Consolidated Status" since the way I have it written won't work?
 
Upvote 0
Range("V1").value="consolidation status"
 
Upvote 0
Jones1413 said:
How would I name the cell V1 to "Consolidated Status"
Click to expand...
To name the cell, try as I already suggested in my code above
1670034797006.png


If you are trying to put that text in the cell, then as Michael has suggested.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA code to create macro, insert form control button, assign macro to button
Replies
1
Views
5K
Harksey
H

Forum statistics

Threads
1,214,523
Messages
6,120,031
Members
448,940
Latest member
mdusw

Share this page

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