If then else problem with multiple criteria

[gginaz2]

I am having difficulty with a long if then else statement. The data in Column A is a GL account number. If cell AC4 contains the word False and If the account number is LT 34000 but GT 34288 but LT 36000 but GT 36288 and not equal to 36290, 36292, 36295, 36296 or 36298 then return No, Else Vlookup the account number from A1 to another spreadsheet. The problem is that it is always returning No as the answer and not doing the Vlookup on the accounts that it should be. Any help would be greatly appreciated. I have a feeling that it has to do with the RC1 designation.

Sub Temp()
'
' Temp Macro
'

'
Dim x As Long
For x = 1 To 1944
If Range("AC4").Value <> False And Range("RC1").Value < 34000 Or Range("RC1").Value > 34288 Or Range("RC1").Value < 36000 Or Range("RC1").Value > 36288 Or Range("RC1").Value <> 36290 Or Range("RC1").Value <> 36292 Or Range("RC1").Value <> 36295 Or Range("RC1").Value <> 36296 Or Range("RC1").Value <> 36298 Then
ActiveCell.FormulaR1C1 = "No"
Else
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC24,Mapping!R2C4:R1200C4,1,FALSE))"
End If
ActiveCell.Offset(1, 0).Activate
Next x
End Sub

 

[Alex Blakenburg]

Can you restate your requirement more in the format below:
(I think in your text you might have your LTs and GTs the wrong way around.)

Sample format
Set to No if:
AC4 = False & Acct between 34000-34288

I assume the <> list are and exception to the rules above.

 

[gginaz2]

AC4 not equal to false
Is less than 34000
Greater than 34288
Less than 36000
Etc

 

[Alex Blakenburg]

AC4 not equal to false
Is less than 34000
Greater than 34288
Less than 36000
Etc

That is not clear which is why asked for a different format.
Are you saying:
AC4 <> False AND < 34000
AC4 <> False AND between 34288 to 36000
AC4 <> False AND > 36288

 

[gginaz2]

Yes, that is what I am looking for

 

[Alex Blakenburg]

I was hoping to stay closer to what you had originally but the changes required went beyond your If Statement issue.
You can do what you are trying to do in a single If statement but I think it is much easier to follow if you break it down into its components.

I have 4 lines flagged with <-- you need to check that they are using the correct column nos.
In the 4th one I am writing to Column 2 which is unlikely to be the column you want to write to so make sure you change that one.

At the moment is assuming GL is being recognised as a number, if that is not the case we will need to make an adjustment.
Also I have used >= and <= for the between criteria, change it to suit if that doesn't work for you.

Sub TestInsertVlookup()

    Dim shtData As Worksheet
    Dim shtMap As Worksheet
    Dim DataLastRow As Long, x As Long
    Dim GL As Long, CheckTF As Variant, cResult As Range
    Dim sFormula As String

    Set shtData = Worksheets("Data")
    Set shtMap = Worksheets("Mapping")
   
    DataLastRow = shtData.Range("A" & Rows.Count).End(xlUp).Row
   
    For x = 2 To DataLastRow
        GL = shtData.Cells(x, 1).Value                                 ' <-- Enter no of column used for GL Account
        CheckTF = shtData.Cells(x, 4).Value                            ' <-- Enter no of column you are Checking for the False
        Set cResult = shtData.Cells(x, 2)                              ' <-- Enter no of output column
        sFormula = ""
       
        If CheckTF <> False Then
            If GL < 34000 _
                Or GL >= 34288 And GL <= 36000 _
                Or GL > 36288 Then
                    If InStr(1, "36290, 36292, 36295, 36296 36298", GL, vbTextCompare) = 0 Then
                        sFormula = "No"
                    End If
            End If
        End If
       
        If sFormula = "No" Then
            cResult.Value = sFormula
        Else
            ' <-- Change the lookup column no of 24 to the required column no
            sFormula = "=VLOOKUP(" & shtData.Cells(x, 24).Address(0, 1) & ",Mapping!$D$2:$D$1200,1,FALSE)"
            cResult.Formula = sFormula
        End If
            
    Next x
   
End Sub

 

[gginaz2]

Thank you so much for all of your help on this. I was able to make a few adjustments to what you sent and it is now working. Part of the problem was that my GL column is set as Text being that some of the accounts are alpha. I never would have gotten this without your help and I truly appreciate it.

 

[Alex Blakenburg]

I appreciate the feedback. It's always nice to hear that the code was clear enough to allow you to make the changes you need. Glad I could help.