Vba code for a formula

BG1983 · Aug 9th 2015

Hi, I need a macro that will utilize the below formula for column D(Starting at D3), range = # of rows that have data in column A. Thank you for any help you can provide, I have been trying to get this for quite some time!

=IFERROR(IF(C3<>"",IF(AND(VLOOKUP(A3,$H$2:$I$413,2,0)="Assigned Attorney",OR(B3="Jimmy Edwards",B3="Kathleen McCarthy")),"Sales Team",IF(AND(VLOOKUP(A3,$H$2:$I$413,2,0)="Intake Team, Assigned Attorney, or Sales Team",B3<>"Jimmy Edwards",B3<>"Kathleen McCarthy"),B3,IF(AND(VLOOKUP(A3,$H$2:$I$413,2,0)="Intake Team, Assigned Attorney, or Sales Team",OR(B3="Jimmy Edwards",B3="Kathleen McCarthy")),"Sales Team",IF(VLOOKUP(F3,$P$2:$Q$363,2,0)="Assigned Attorney",B3,IF(AND(VLOOKUP(A3,$H$2:$I$413,2,0)="Sales Team",OR(B3="Jimmy Edwards",B3="Kathleen McCarthy")),"Sales Team",IF(C3<>"",VLOOKUP(A3,$H$2:$I$413,2,0),"Intake Team")))))),"No Match"),"")

**Batman** · Aug 9th 2015

Re: Vba code for a formula

HI BG1983,

Welcome to the Ozgrid forum.

The best way to assign formulas to cells with VBA is to use the R1C1 notation format. Type the formula you want into the first destination cell, make sure the cell is selected, then go to the VBE Immediate Pane (Alt + F11, Ctrl + G) and type

Code

? Activecell.FormulaR1C1

and press Enter. You will need to edit the result by replacing each instance of " with "" and you will probably want to spread the code over several lines.

Code

Range(Cells(3, 1), Cells(3, 1).End(xlDown)).Offset(0, 3).FormulaR1C1 = _
        "=IFERROR(IF(RC[-1]<>"""",IF(AND(VLOOKUP(RC[-3],R2C8:R413C9,2,0)=""Assigned Attorney"",OR(RC[-2]=""Jimmy Edwards""," & _
        "RC[-2]=""Kathleen McCarthy"")),""Sales Team"",IF(AND(VLOOKUP(RC[-3],R2C8:R413C9,2,0)=""Intake Team, Assigned Attorney, or Sales Team""," & _
        "RC[-2]<>""Jimmy Edwards"",RC[-2]<>""Kathleen McCarthy""),RC[-2],IF(AND(VLOOKUP(RC[-3],R2C8:R413C9,2,0)=""Intake Team, Assigned Attorney, or Sales Team""," & _
        "OR(RC[-2]=""Jimmy Edwards"",RC[-2]=""Kathleen McCarthy"")),""Sales Team"",IF(VLOOKUP(RC[2],R2C16:R363C17,2,0)=""Assigned Attorney""," & _
        "RC[-2],IF(AND(VLOOKUP(RC[-3],R2C8:R413C9,2,0)=""Sales Team"",OR(RC[-2]=""Jimmy Edwards"",RC[-2]=""Kathleen McCarthy"")),""Sales Team""," & _
        "IF(RC[-1]<>"""",VLOOKUP(RC[-3],R2C8:R413C9,2,0),""Intake Team"")))))),""No Match""),"""")"

As there is no indication of the intended usage of this code, it's impossible to determine whether the ending row numbers (413 & 363) in your formula need to be replaced with VBA variables to make the code more flexible.

BG1983 · Aug 9th 2015

Re: Vba code for a formula

The data in columns H-I are the lookup values. Each cell in column H is unique - it is every possible lookup value, coming from column A. H is the "assigned team" which is what I am trying to find. Will that change the macro you provided? I ran it and it did not cause any errors, but it also did not provide the correct results so I am hoping the info above will change the macro

BG1983 · Aug 9th 2015

Re: Vba code for a formula

[TABLE="class: grid, width: 1891"]

[tr]

[td]

LPS Process

[/td]

[td]

Assigned attorney

[/td]

[td]

Complaint Filed Milestone

[/td]

[td][/td]

[td]

Process Lookup

[/td]

[td]

Match1

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_Other_

[/td]

[td]

William Cobb Esq.

[/td]

[td][/td]

[td]

3rdPty_FL_

[/td]

[td]

Sales Team

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_Other_

[/td]

[td]

William Cobb Esq.

[/td]

[td][/td]

[td]

No Match

[/td]

[td][/td]

[td]

3rdPty_FL_Other_FHLMC_

[/td]

[td]

Sales Team

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_Other_

[/td]

[td]

William Cobb Esq.

[/td]

[td][/td]

[td]

No Match

[/td]

[td][/td]

[td]

3rdPty_FL_Other_Other_

[/td]

[td]

Sales Team

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_Other_

[/td]

[td]

William Cobb Esq.

[/td]

[td][/td]

[td]

No Match

[/td]

[td][/td]

[td]

3rdPty_FL_PMI_FHLMC_

[/td]

[td]

Sales Team

[/td]

[/tr]

[tr]

[td]

FC_FL_FHA_BO_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

5/31/10

[/td]

[td][/td]

[td]

AITNO_Vesting_client_response_FC

[/td]

[td]

Title Team, Assigned Attorney, or Sales Team

[/td]

[/tr]

[tr]

[td]

PS_FL_FHA_2Deed

[/td]

[td]

Jimmy Edwards

[/td]

[td]

7/9/10

[/td]

[td][/td]

[td]

AITNO_Vesting_Request_FC

[/td]

[td]

Title Team, Assigned Attorney, or Sales Team

[/td]

[/tr]

[tr]

[td]

FC_Probate_admin1

[/td]

[td]

Jimmy Edwards

[/td]

[td]

7/30/10

[/td]

[td][/td]

[td]

BID_ALL_Other_BO_

[/td]

[td]

Use Step

[/td]

[/tr]

[tr]

[td]

FC_Probate_admin1

[/td]

[td]

Jimmy Edwards

[/td]

[td]

7/30/10

[/td]

[td][/td]

[td]

BID_ALL_Other_FHLMC_

[/td]

[td]

Sales Team

[/td]

[/tr]

[tr]

[td]

BK_MFRRlf

[/td]

[td]

Jimmy Edwards

[/td]

[td]

9/2/10

[/td]

[td][/td]

[td]

BID_ALL_Other_Other_

[/td]

[td]

Fees/Cost Team

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Notification

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/7/10

[/td]

[td]

Kathleen McCarthy

[/td]

[td][/td]

[td]

BID_ALL_PMI_BO_

[/td]

[td]

Fees/Cost Team

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Notification

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/7/10

[/td]

[td]

Kathleen McCarthy

[/td]

[td][/td]

[td]

BID_ALL_PMI_Other_

[/td]

[td]

Fees/Cost Team

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Notification

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/7/10

[/td]

[td]

Kathleen McCarthy

[/td]

[td][/td]

[td]

BK_AddFees_Client

[/td]

[td]

BK Team

[/td]

[/tr]

[tr]

[td]

FC_Atty_Presale_Review

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/7/10

[/td]

[td][/td]

[td]

BK_AddFees_Review1

[/td]

[td]

BK Team

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Notification

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/7/10

[/td]

[td]

Kathleen McCarthy

[/td]

[td][/td]

[td]

BK_AddFeesDenied

[/td]

[td]

BK Team

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Notification

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/7/10

[/td]

[td]

Kathleen McCarthy

[/td]

[td][/td]

[td]

BK_Amended_LM_POC

[/td]

[td]

BK Team

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_BO_

[/td]

[td]

Jacquelyn Beik

[/td]

[td]

9/9/09

[/td]

[td][/td]

[td]

FC_Amended_Bid

[/td]

[td]

Intake Team, Assigned Attorney, or Sales Team

[/td]

[/tr]

[tr]

[td]

FC_Servicer_Presale_Review1

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/9/09

[/td]

[td][/td]

[td]

FC_ANFM_Complaint

[/td]

[td]

WBClient SYS

[/td]

[/tr]

[tr]

[td]

FC_FL_PMI_Other_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/9/09

[/td]

[td][/td]

[td]

FC_ANFM_Judgment

[/td]

[td]

WBClient SYS

[/td]

[/tr]

[tr]

[td]

FC_FL_VA_Other_

[/td]

[td]

Jimmy Edwards

[/td]

[td]

9/10/09

[/td]

[td][/td]

[td]

FC_Answer_Filed_W_Defenses1

[/td]

[td]

Assigned Attorney

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Cust_Notification

[/td]

[td]

Jimmy Edwards

[/td]

[td]

9/10/09

[/td]

[td][/td]

[td]

FC_Answer_Filed1

[/td]

[td]

Assigned Attorney

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_BO_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

9/16/09

[/td]

[td][/td]

[td]

FC_AnswerFiled

[/td]

[td]

Assigned Attorney

[/td]

[/tr]

[tr]

[td]

FC_Hold_BK

[/td]

[td]

Jimmy Edwards

[/td]

[td]

10/15/09

[/td]

[td][/td]

[td]

FC_AOI_ MSJ_ Affidavit

[/td]

[td]

Assigned Attorney

[/td]

[/tr]

[tr]

[td]

3rdPty_FL_Other_Other_

[/td]

[td]

Jimmy Edwards

[/td]

[td]

10/15/09

[/td]

[td][/td]

[td]

FC_AOI_Request_FLAOIID4

[/td]

[td]

ERICA CORTES (BANA ONLY)

[/td]

[/tr]

[tr]

[td]

FC_FL_PMI_FHLMC_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

10/20/09

[/td]

[td][/td]

[td]

FC_AOM_MERS

[/td]

[td]

Use Step

[/td]

[/tr]

[tr]

[td]

FC_FL_PMI_FHLMC_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

10/20/09

[/td]

[td][/td]

[td]

FC_AOM_Recording_Info_Attorney

[/td]

[td]

Original Docs Team

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_Other_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

11/6/09

[/td]

[td][/td]

[td]

FC_ArtifactGathering_Complaint

[/td]

[td]

WBClient SYS

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_BO_

[/td]

[td]

Jacquelyn Beik

[/td]

[td]

11/10/09

[/td]

[td][/td]

[td]

FC_ArtifactGathering_JudgmentOrder

[/td]

[td]

Intake Team, Assigned Attorney, or Sales Team

[/td]

[/tr]

[tr]

[td]

BK_MFR

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

11/12/09

[/td]

[td][/td]

[td]

FC_ArtifactGathering_NoticeofService

[/td]

[td]

Assigned Attorney

[/td]

[/tr]

[tr]

[td]

BK_MFR

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

11/12/09

[/td]

[td][/td]

[td]

FC_ArtifactGathering_TitleReport

[/td]

[td]

Title Team

[/td]

[/tr]

[tr]

[td]

FC_Postponement_Notification

[/td]

[td]

Elizabeth Kim

[/td]

[td]

7/5/11

[/td]

[td]

Elizabeth Kim

[/td]

[td][/td]

[td]

BK_DocFwd_Revision

[/td]

[td]

BK Team

[/td]

[/tr]

[tr]

[td]

FC_Client_Sale_Change_Review

[/td]

[td]

Elizabeth Kim

[/td]

[td]

7/5/11

[/td]

[td][/td]

[td]

BK_Escrow_Change_Letter

[/td]

[td]

BK Team

[/td]

[/tr]

[tr]

[td]

FC_FL_Other_FNMA_

[/td]

[td]

Kathleen McCarthy

[/td]

[td]

7/6/11

[/td]

[td]

Kathleen McCarthy

[/td]

[td][/td]

[td]

BK_FeesApproved

[/td]

[td]

BK Team

[/td]

[/tr]

[/TABLE]

**Batman** · Aug 9th 2015

Re: Vba code for a formula

The macro I provided was only your formula populating the cells in column D. If your formula works when entered in the first cell and filled down, then the macro should work too.

As I had no data to work with, or any indication of what it was trying to do, it was impossible for me to say whether or not the formula itself would work. My assumption, based on your original post, was that the formula worked and all you wanted to do was to automate populating it into the cells in column D.

As the data in columns A and H only corresponds in a very few instances (I assume the actual lists are much bigger), it would be easier to figure out if you could attach the data in a workbook.

BG1983 · Aug 9th 2015

Re: Vba code for a formula

Disregard - I got it working, thanks!!

BG1983 · Aug 9th 2015

Re: Vba code for a formula

So i have just one more question but want to provide the excel shet to make it easier for you, how do i do that?

**Batman** · Aug 10th 2015

Re: Vba code for a formula

Hi,

Click ' Go Advanced', scroll down and click on 'Manage Attachments', click 'Add Files' then 'Basic Uploader' and browse to select the file.

Vba code for a formula

Participate now!

Share