Fill Cells Based on Condition - VBA

[jollywood]

I need to be able to fill a cell in the same row based on values in two other cells in different columns.


Current Layout. Call these columns A, B, and C






I have 3 different states I work with, OH, IL and PA. There are different tax exemptions based on whether or not the class is RES or COM.
Conditions:
State = PA, Class = RES, Tax Exempt = 1
State = PA, Class = COM, Tax Exempt = Null (not "0")
For all other states, Tax Exempt will always = 1

I need the VBA code that will autofill column B with the proper Tax Exempt status based upon columns A and C.

The code I have now just looks at the first value in columns A and C then fills all of column B with that number. I hope this wasn't too confusing!!

 

[doofusboy]

Why not just put a formula like this in column "B" and copy down as needed?

=IF(AND(A2="COM",C2="PA"),"",1)

 

[Andrew Poulsom]

What code do you have now?

 

[jollywood]

Well, unfortunately, I deleted the code when I found that it didn't work.

I will try and rewrite what I had then post it here.

 

[jollywood]

I rewrote the code in a different format with (what seems like) better logic. However, now I get a type mismatch when I use "AND"

Sub DataScrubber()

'//STEP NINE -DETERMINES TAX EXEMPT STATUS FOR EACH STATE
'//FOR PA: IF ACCOUNT IS RES, TAX EXEMPT = 1, IF ACCOUNT IS COM, TAX EXEMPT = 0
'//ALL OTHERS: RES AND COM = 1

Dim classRange As Range
Dim taxRange As Range
Dim srvState As Range
Dim lastrowrange As Long


lastrowrange = [A65536].End(xlUp).Row

Set taxRange = Range("V1", Cells(lastrowrange, 22))
Set classRange = Range("J1", Cells(lastrowrange, 10))
Set srvState = Range("Z1", Cells(lastrowrange, 26))

If srvState.Cells.Value = "PA" And classRange.Cells.Value = "COM" Then
    taxRange.Cells.Value = vbNullString
    Else
    taxRange.Cells.Value = "1"
End If
    
End Sub

A few things to note here:
This is my first attempt at using the "lastrowrange" to find the last row with populated data. A problem with this is that some columns may not be the same number of cells. For example:
The Tax Exempt column may only contain 40 cells of populated data while the SRV STATE and CLASS columns contain 100. This formula should not only correct the first 40 but also complete the last 60.
**I get a new workbook everyday so the ranges are dynamic.

 

[Andrew Poulsom]

You need to loop around the cells in srvState and classRange, testing one cell at a time. Try the untested:

Sub DataScrubber()
'//STEP NINE -DETERMINES TAX EXEMPT STATUS FOR EACH STATE
'//FOR PA: IF ACCOUNT IS RES, TAX EXEMPT = 1, IF ACCOUNT IS COM, TAX EXEMPT = 0
'//ALL OTHERS: RES AND COM = 1
Dim classRange As Range
Dim taxRange As Range
Dim srvState As Range
Dim lastrowrange As Long
Dim i As Long
lastrowrange = [A65536].End(xlUp).Row
Set taxRange = Range("V1", Cells(lastrowrange, 22))
Set classRange = Range("J1", Cells(lastrowrange, 10))
Set srvState = Range("Z1", Cells(lastrowrange, 26))
For i = 1 To lastrowrange
    If srvState.Cells(i, 1).Value = "PA" And classRange.Cells(i, 1).Value = "COM" Then
        taxRange.Cells(i, 1).Value = vbNullString
        Else
        taxRange.Cells(i, 1).Value = 1
    End If
Next i
End Sub

 

[jollywood]

That worked!!! Thanks for your help!!!!