Tech Support Guy banner
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
19 Posts
Discussion Starter · #1 ·
I am writing this formula as follows:

Application.DisplayAlerts = False
Sheets("Q Results - Before Lender List").Select
Dim i As Long, introwcount As Long
For i = 1 To Cells(Rows.Count, 5).End(xlUp).Row
Range("G2").Select
If Cells(i, 6) = "TEXAS BRANCHES" and Cells(i, 2) = left([-5], 2) <> "80" Then
Cells(i, 7).Value = "XXXXXX"
ElseIf Cells(i, 6) = "BANKING CENTERS - TEXAS" Then
Cells(i, 7).Value = "XXXXXX"
ElseIf Cells(i, 5) = "111111" And Cells(i, 6) = "" Then
Cells(i, 7).Value = "Any Corrections?"
ElseIf Cells(i, 5) = "000000" And Cells(i, 6) = "" Then
Cells(i, 7).Value = "Any Corrections?"

Else
Cells(i, 7).Value = "No Criteria Met"
End If
Next i

how do i use the left function to see if '80' is the first two numbers...and if it isare: to make it 'XXXXXX'. I need the result in Column G and I am looking in column B to see if 80 is the first two numbers.

Thanks in advance
 

·
Registered
Joined
·
5,459 Posts
Hi there, welcome to the board!

Maybe you could use this changed slightly...

Code:
    Dim i As Long, introwcount As Long
    Application.DisplayAlerts = False
    With Sheets("Q Results - Before Lender List")
        For i = 1 To .Cells(.Rows.Count, 5).End(xlUp).Row
            If .Cells(i, 6) = "TEXAS BRANCHES" And Left(.Cells(i, 2), 2) <> "80" Then
                .Cells(i, 7).Value = "XXXXXX"
            ElseIf .Cells(i, 6) = "BANKING CENTERS - TEXAS" Then
                .Cells(i, 7).Value = "XXXXXX"
            ElseIf .Cells(i, 5) = "111111" And .Cells(i, 6) = "" Then
                .Cells(i, 7).Value = "Any Corrections?"
            ElseIf .Cells(i, 5) = "000000" And .Cells(i, 6) = "" Then
                .Cells(i, 7).Value = "Any Corrections?"
            Else
                .Cells(i, 7).Value = "No Criteria Met"
            End If
        Next i
    End With
Note this will probably error out if the length of that cell is less than 2 characters.
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top