VBA If Then Evaluations

[NYEXCEL1]

Having trouble creating a macro to both evaluate and/or replace data in the table (Headers in Blue) with the last 3 columns (smile headers). The result of the macro is shown below in Blue. The criteria is as follows:

• In COlumn E (ACCT), for each row, if the cell is blank then the Number "2000" needs to be inserted otherwise what is there should not be affected
• IN Column F (MEMO), What is there should stay but if there is text in the column titled "Business Purpose" then that should be added to the text in column F AFTER the Colon
• IN COlumn H (INvoice Approver), The Column titled "DEPARTMENT" needs to be evaluated and if there is text in the cell it should replace the text in Column H otherwise nothing.
• In Column I (Waiver Markup), For each row Column D should be evaluated, if the number is "5080" the corresponding row in column I should say "NOTHING", if another 4 digit number it should say "NONEED", if a date then leave what is already there.

The data sheet will be made hundreds of rows should the above should cover the entire worksheet to be safe.

Any assitance here would be great as the task is not hard but VBA seems to be the only way as formulas dont seem to be the most efficient.

Many thanks members!!

A	B	C	D	E	F	G	H	I
!TRNS	TRNSID	TRNSTYPE	DATE	ACCNT	NAME	AMOUNT	DOCNUM	MEMO
!SPL	SPLID	TRNSTYPE	ACCNT	AMOUNT	MEMO	Client	Invoice Approver	Waive Markup (Finance Approval Required)	Business Purpose	Department	REIMBEXP
TRNS		BILL	5/5/2020​		PR Newswire	-1500​	103588132​	PR Newswire
SPL		BILL	5080​	2395​	Program Expense:	Joe's Store	John Doe	N	Entertainment Industry	Corp/Tech:Tech:CET	NOTHING
SPL		BILL	5060​	-895​	Program Expense - Nonbillable:	Joe's Store	John Doe		Entertainment Industry	Corp/Tech:Tech:CET	NONEED

Final Result

!TRNS	TRNSID	TRNSTYPE	DATE	ACCNT	NAME	AMOUNT	DOCNUM	MEMO
!SPL	SPLID	TRNSTYPE	ACCNT	AMOUNT	MEMO	Client	Invoice Approver	Waive Markup (Finance Approval Required)	Business Purpose	Department	REIMBEXP
TRNS		BILL	5/5/2020​	2000	PR Newswire	-1500​	103588132​	PR Newswire
SPL		BILL	5080​	2395​	Program Expense: Entertainment Industry	Joe's Store	Corp	NOTHING	Entertainment Industry	Corp	NOTHING
SPL		BILL	5060​	-895​	Program Expense - Nonbillable: Entertainment Industry	Joe's Store	Corp	NONEED	Entertainment Industry	Corp	NONEED

 

[NYEXCEL1]

Amazing! Thank you. Do you teach this at all? tried online but hard until you put into practical use. never heard of the "Unbound" Function

 

[DanteAmor]

"Unbound" Function

You mean ubound, Returns containing the largest available subscript for the indicated dimension of an array.

Amazing! Thank you

I'm glad to help you. Thanks for the feedback.