# Excel Macro using an if then statement and a left function

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### gundr1kr

I am writing this formula as follows:

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.

#### Zack Barresse

Hi there, welcome to the board!

Maybe you could use this changed slightly...

Code:
``````    Dim i As Long, introwcount As Long
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.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!