If Statement with multiple criteria + Concatenate Function

[WantToMasterExcel]

Employee ID	Fund	Property	General Expense	Expense Description	VBA Coded Needed in this Column
JK	CPCP	CORP	March Misc Exp	JK - cell phone Bill	JK - cell phone Bill
LT	CPCP	CORP	Property Visits	LT - taxi from airport to hotel	LT - taxi from airport to hotel
SN	DCUI	DCUI-Fund	SN - travel agent fee	SN - booking fee	SN DCHI DCHUI-Fund SN - travel agent fee
AN	CPCP	CORP	AN - Tours in DC	AN - airfare to Seattle	AN - airfare to Seattle
RB	DCUI	DCUI-Fund	RB - Museum Tower visit	RB - taxi from NOBE tour to hotel	RB DCUI DCUI-Fund RB - Museum Tower visit
OS	CPCP	CORP	OS - Meal - Self	OS - Lunch with Donna Gayles	OS - Lunch with Donna Gayles
TR	RCS	RCS-Fund	TR - RealShare conference registration	TR - Diverse Investor Forum	TR RCS RCS-Fund TR - Real Share conference registration
LM	DCUI	NORE	LM - data plan	LM - Personal	LM - Personal
FW	FRCA	FRCA Corp	FW - ISC conference registration	FW - Spring Conference	FW FRCA FRCA Corp FW - ISC conference registration
HT	TRSIL	Victor	Attending the Conference	HT - hotel in Houston	HT TRSIL Victor Attending the Conference
DS	GALER	Tower1	DS - Breakfast	DS - Dinner w/ VIPs	DS GALER Tower1 DS - Breakfast
Required VBA in COLUMN "F":
If the text in Column B is CPCP and the text in column C is Corp, Then Return or copy the value from Column E to Column F
If the text in Column B is DCUI and the text in column C is NORE, Then Return or copy the value from Column E to Column F
For the REST of the Value in Columns B & C, apply CONCATENATE Function(Return the value in Column "F" = Column A "Employee ID" + Column B "Fund" + Column C "Property" + Column D "General Expense"
Also, there are hundreds of ROWS in this Sheet named "Sheet1". Therefore, I need vba to perform till the end of the Last ROW

<tbody>
</tbody>

 

[WantToMasterExcel]

Any VB Maestro out there to Help me out here please. I need it tonight to submit this project, in order to be considered for a job. PLEASE HELP !!!!!!

 

[WantToMasterExcel]

I guess this is one of the toughest macros to create! 80 views, not a SINGLE reply.

 

[Matty]

Why a macro?

 

[MarcelBeug]

I guess the lack of response may be due to the VBA requirement. You don't need VBA.

Enter in cell F2 and copy down:

=IF(OR(B2&C2="CPCPCORP",B2&C2="DCUINORE"),E2,A2&" "&B2&" "&C2&" "&D2)

 

[WantToMasterExcel]

Marcel,
I do need a VBA for this formula you just provided =IF(OR(B2&C2="CPCPCORP",B2&C2="DCUINORE"),E2,A2&" "&B2&" "&C2&" "&D2). Thanks a lot for your response

I guess the lack of response may be due to the VBA requirement. You don't need VBA.

Enter in cell F2 and copy down:

=IF(OR(B2&C2="CPCPCORP",B2&C2="DCUINORE"),E2,A2&" "&B2&" "&C2&" "&D2)

 

[MarcelBeug]

Marcel,
I do need a VBA for this formula you just provided =IF(OR(B2&C2="CPCPCORP",B2&C2="DCUINORE"),E2,A2&" "&B2&" "&C2&" "&D2). Thanks a lot for your response

You're welcome, but can you please explain why you would need VBA?
I can think of either you would regard a formula as being VBA (which it is not), or you might not be aware that formulas are auto-adjusted to the applicable row if you copy it downwards. Or something else I can't think of .

E.g. if you copy the formula in F2 downwards to F3, in F3 it will read automatically:

=IF(OR(B3&C3="CPCPCORP",B3&C3="DCUINORE"),E3,A3&" "&B3&" "&C3&" "&D3)

Alternatives to copy formulas downwards to all rows of data (let's say F2:F300):
1. Enter the formula in cell F2 and double click the lower right corner of the cell. This requires all non-empty cells in E2:E300.
2. Enter the formula in F2, select range F2:F300 and press CTRL d.
3. First select range F2:F300, than enter your formula and confirm it with CTRL-ENTER (not CTRL-SHIFT-ENTER).

Thanks,
Marcel

 

[WantToMasterExcel]

Sir,
I am creating a macro to automate a monthly expense( a project to provide to my new TO BE Boss, if they are satisfied , they will hire me) I understand, I can pull the plus sign all the way downwards in that column and it will automatically copy the formula. I know basic macros, copy paste, select, cut paste, etc. AS it gets complicated, I sort of lose my mind, as I am just an Excel user not an expert on Macros/VBA.
Thanks again

 

[WantToMasterExcel]

OK. Here is the macro now. I am getting "Application-defined or Object Defined error". ANYONE here please:

Sub Zacro()
' Zacro Macro

Dim LR As Range

With Sheets("Sheet1")
Set LR = .Cells(.Rows.Count, "A").End(xlUp)
.Range("F2:F" & LR.Row).Formula = "= IF(OR(B2&C2=CPCPCORP, B2&C2=DCUINORE),E2,A2&""& B2& ""&
C2&""& D2)"
End With


End Sub

Thanks.

You're welcome, but can you please explain why you would need VBA?
I can think of either you would regard a formula as being VBA (which it is not), or you might not be aware that formulas are auto-adjusted to the applicable row if you copy it downwards. Or something else I can't think of .

E.g. if you copy the formula in F2 downwards to F3, in F3 it will read automatically:

=IF(OR(B3&C3="CPCPCORP",B3&C3="DCUINORE"),E3,A3&" "&B3&" "&C3&" "&D3)

Alternatives to copy formulas downwards to all rows of data (let's say F2:F300):
1. Enter the formula in cell F2 and double click the lower right corner of the cell. This requires all non-empty cells in E2:E300.
2. Enter the formula in F2, select range F2:F300 and press CTRL d.
3. First select range F2:F300, than enter your formula and confirm it with CTRL-ENTER (not CTRL-SHIFT-ENTER).

Thanks,
Marcel

 

[MarcelBeug]

Hi,

Well, I'm not a VBA expert, but I see there are no quotes around CPCPCORP and DCUINORE.