VBA If Then Evaluations

NYEXCEL1 · Jul 13, 2020

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

DanteAmor · Jul 14, 2020

I assume your data starts in cell A3.
On a copy of your sheet, try the following:

VBA Code:

Sub IfEvaluations()
  Dim a As Variant, i As Long
  
  a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row).Value2
  For i = 1 To UBound(a)
    If a(i, 5) = "" Then a(i, 5) = "2000"
    If a(i, 10) <> "" Then a(i, 6) = a(i, 6) & " " & a(i, 10)
    If a(i, 11) <> "" Then a(i, 8) = a(i, 11)
    If a(i, 4) = "5080" Or a(i, 4) = 5080 Then
      a(i, 9) = "NOTHING"
    ElseIf Len(a(i, 4)) = 4 Then
      a(i, 9) = "NONEED"
    End If
  Next
  Range("A3").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

NYEXCEL1 · Jul 14, 2020

Amazing! Thank you. Couple of questions if you dont mind.

1. if you can tell me in plain English what these lines of code are doing, so I can learn:

a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row).Value2
For i = 1 To UBound(a)

Range("A3").Resize(UBound(a, 1), UBound(a, 2)).Value = a

2. In the first Column, the code "TRNS" will appear quite often. Is there a line of code that beginning but not including the first instance, that a line is inserted and the code "ENDTRNS" be put in that Column Space?

3. At the Very end of the data set, in column A the code "ENDTRNS" be inserted in the first column after the last line of data

Many thanks again, a true talent!!

DanteAmor · Jul 14, 2020

NYEXCEL1 said:
1. if you can tell me in plain English what these lines of code are doing, so I can learn:

VBA Code:

  'Fill the array 'a' with the data from cell A3 to column L and the last row with data
  a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row).Value2
  
  'cycle from 1 to the number of lines in array 'a'
  For i = 1 To UBound(a)

  'Resize the output, starting at A3 and
  'increasing the number of rows according to the number of rows in array (a,1) and
  'increasing the number of columns according to the number of columns in array (a,2)
  Range("A3").Resize(UBound(a, 1), UBound(a, 2)).Value = a

NYEXCEL1 said:
2. In the first Column, the code "TRNS" will appear quite often. Is there a line of code that beginning but not including the first instance, that a line is inserted and the code "ENDTRNS" be put in that Column Space?

I do not understand, the macro has a problem? or is it a new requirement? You can explain it with an example that you have in your data in post #1.

NYEXCEL1 said:
3. At the Very end of the data set, in column A the code "ENDTRNS" be inserted in the first column after the last line of data

I do not understand, the macro has a problem? or is it a new requirement? You can explain it with an example that you have in your data in post #1.

NYEXCEL1 · Jul 14, 2020

Thanks for your reply. Macro works great. my questions were add on once i ran the program.

For Question #2 & #3, I realize my question is the same and the answer is in Blue Text below. For context there will be many transactions that begin with "TRNS" followed by "SPL" in rows underneath. The number of lines with "SPL" will vary so a blank line will need to be inserted after at the end of the last "SPL" in each segment and then "ENDSTRNS" be added.

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

ENDSTRNS
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

ENDSTRNS
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

ENDSTRNS

DanteAmor · Jul 14, 2020

Try this

VBA Code:

Sub IfEvaluations()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  
  'Fill the array 'a' with the data from cell A3 to column L and the last row with data
  a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row + 1).Value2
  n = WorksheetFunction.CountIf(Range("A:A"), "TRNS")
  ReDim b(1 To UBound(a, 1) + n, 1 To UBound(a, 2))
  'cycle from 1 to the number of lines in array 'a'
  For i = 1 To UBound(a) - 1
    k = k + 1
    For j = 1 To UBound(a, 2)
      b(k, j) = a(i, j)
    Next
    
    If a(i, 5) = "" Then b(k, 5) = "2000"
    If a(i, 10) <> "" Then b(k, 6) = a(i, 6) & " " & a(i, 10)
    If a(i, 11) <> "" Then b(k, 8) = a(i, 11)
    If a(i, 4) = "5080" Or b(k, 4) = 5080 Then
      b(k, 9) = "NOTHING"
    ElseIf Len(a(i, 4)) = 4 Then
      b(k, 9) = "NONEED"
    End If
  
    If a(i + 1, 1) = "TRNS" Then
      k = k + 1
      b(k, 1) = "ENDSTRNS"
    End If
  Next
  '
  b(k + 1, 1) = "ENDSTRNS"
  Range("A3").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

NYEXCEL1 · Jul 14, 2020

Percect.

Last question, it looks like the formatting in the fourth column is being changed from date to general and from general to date for some reason. In summary, what is in blue is accurate, what seems to follow after has issues.

Is there a way to format using the logic "If the first column has "TRNS" then the format of the cell in column 4 in same row is date" "If SPL then general 4 digit number"?

That should be the last change

!TRNS	TRNSID	TRNSTYPE	DATE
!SPL	SPLID	TRNSTYPE	ACCNT
!ENDTRNS
TRNS		BILL	5/5/2020
SPL		BILL	5080
SPL		BILL	5060
ENDSTRNS
TRNS		BILL	43942
SPL		BILL	5080
SPL		BILL	11/27/1913
ENDSTRNS
TRNS		BILL	43970
SPL		BILL	11/27/1913
SPL		BILL	5060
ENDSTRNS
TRNS		BILL	43980
SPL		BILL	11/27/1913

DanteAmor · Jul 14, 2020

Change this
a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row + 1).Value2

For this
a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row + 1).Value

Try again

NYEXCEL1 · Jul 14, 2020

Unfortunately no. Below are the results

!TRNS	TRNSID	TRNSTYPE	DATE
!SPL	SPLID	TRNSTYPE	ACCNT
!ENDTRNS
TRNS		BILL	5/5/2020
SPL		BILL	5080
SPL		BILL	5060
ENDSTRNS
TRNS		BILL	4/21/2020
SPL		BILL	5080
SPL		BILL	11/27/1913
ENDSTRNS
TRNS		BILL	5/19/2020
SPL		BILL	11/27/1913
SPL		BILL	5060
ENDSTRNS
TRNS		BILL	5/29/2020
SPL		BILL	11/27/1913
ENDSTRNS
TRNS		BILL	6/19/2020
SPL		BILL	5080
ENDSTRNS
TRNS		BILL	5/29/2020
SPL		BILL	11/27/1913
ENDSTRNS
TRNS		BILL	5/28/2020
SPL		BILL	5080
ENDSTRNS
TRNS		BILL	6/4/2020
SPL		BILL	5080
ENDSTRNS
TRNS		BILL	6/15/2020
SPL		BILL	5080
ENDSTRNS
TRNS		BILL	4/27/2020
SPL		BILL	11/27/1913
SPL		BILL	5080
ENDSTRNS
TRNS		BILL	6/18/2020
SPL		BILL	5080
ENDSTRNS
TRNS		BILL	4/27/2020
SPL		BILL	11/27/1913
SPL		BILL	5080
ENDSTRNS

DanteAmor · Jul 14, 2020

Try this:

VBA Code:

Sub IfEvaluations()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  
  'Fill the array 'a' with the data from cell A3 to column L and the last row with data
  Columns("D:D").NumberFormat = "General"
  a = Range("A3:L" & Range("A:L").Find("*", , , xlPart, xlByRows, xlPrevious).Row + 1).Value
  n = WorksheetFunction.CountIf(Range("A:A"), "TRNS")
  ReDim b(1 To UBound(a, 1) + n, 1 To UBound(a, 2))
  'cycle from 1 to the number of lines in array 'a'
  For i = 1 To UBound(a) - 1
    k = k + 1
    For j = 1 To UBound(a, 2)
      b(k, j) = a(i, j)
      If j = 4 Then
        If Len(b(k, j)) > 4 Then
          b(k, j) = Format(b(k, j), "mm/dd/yyyy")
        End If
      End If
    Next
    
    If a(i, 5) = "" Then b(k, 5) = "2000"
    If a(i, 10) <> "" Then b(k, 6) = a(i, 6) & " " & a(i, 10)
    If a(i, 11) <> "" Then b(k, 8) = a(i, 11)
    If a(i, 4) = "5080" Or b(k, 4) = 5080 Then
      b(k, 9) = "NOTHING"
    ElseIf Len(a(i, 4)) = 4 Then
      b(k, 9) = "NONEED"
    End If
  
    If a(i + 1, 1) = "TRNS" Then
      k = k + 1
      b(k, 1) = "ENDSTRNS"
    End If
  Next
  '
  b(k + 1, 1) = "ENDSTRNS"
  Range("A3").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

VBA If Then Evaluations

NYEXCEL1

Board Regular

Excel Facts

DanteAmor

Well-known Member

NYEXCEL1

Board Regular

DanteAmor

Well-known Member

NYEXCEL1

Board Regular

DanteAmor

Well-known Member

NYEXCEL1

Board Regular

DanteAmor

Well-known Member

NYEXCEL1

Board Regular

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

VBA If Then Evaluations

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock