VBA code to return text in a cell if another cell starts with numbers or 2 letters

[SamCha]

Hi all,

Please can you help me with a code where the Column D till the last row (range varies) will return the letters:

1). 'SW' : if values in Column F till the last row ( range varies) starts with numbers

2). 'IB' : if values in Column F starts with two letters ( just like IBAN numbers) and the numbers - the character length is greater than equal to 15.

Thanks in advance

 

[Joe4]

You haven't accounted for entries which do not meet either of your criteria (if that is even possible).
If that is a possibility (i.e. column F starts with just one letter), what do you want to return in those instances?

Please post some examples of what your data looks like, and your expected results.
Please be sure to try to cover all the different scenarios in your example.

 

[SamCha]

Hi

You haven't accounted for entries which do not meet either of your criteria (if that is even possible).
If that is a possibility (i.e. column F starts with just one letter), what do you want to return in those instances?

Please post some examples of what your data looks like, and your expected results.
Please be sure to try to cover all the different scenarios in your example.

Hi Joe4,

If a particular cell in Column F starts with one letter and then numbers for eg A1234567 it will show in Column D as 'SW'.

So, what I meant to say is any cell other than having the format for eg:
AD1400080001001234567890 ( starting with 2 letters and then charaters greater than equal to 15) will show 'SW' or else will show 'IB'.

Thanks

 

[Joe4]

Try this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    
'   Loop through all rows in column F starting in row 1
    For r = 1 To lr
'       Check entries in column F
        If IsNumeric(Left(Cells(r, "F"), 1)) Or _
            IsNumeric(Mid(Cells(r, "F"), 2, 1)) Or _
            (Len(Cells(r, "F")) <= 15) Then
            Cells(r, "D") = "IB"
        Else
            Cells(r, "D") = "SW"
        End If
    Next r
        
    Application.ScreenUpdating = True
    
End Sub

Note that if you are starting anywhere other than row 1, change the starting row number in this line:
For r = 1 To lr

 

[SamCha]

Try this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row
   
'   Loop through all rows in column F starting in row 1
    For r = 1 To lr
'       Check entries in column F
        If IsNumeric(Left(Cells(r, "F"), 1)) Or _
            IsNumeric(Mid(Cells(r, "F"), 2, 1)) Or _
            (Len(Cells(r, "F")) <= 15) Then
            Cells(r, "D") = "IB"
        Else
            Cells(r, "D") = "SW"
        End If
    Next r
       
    Application.ScreenUpdating = True
   
End Sub

Note that if you are starting anywhere other than row 1, change the starting row number in this line:
For r = 1 To lr

Thanks a lot Joe4. This worked perfect to what was required, however, its also filling up the cells in Column D to the last row which have formulas but no values. As the range will keep varying I will not be able to apply the code to a specific range. Are you able to suggest any solution the issue please.

 

[Joe4]

Thanks a lot Joe4. This worked perfect to what was required, however, its also filling up the cells in Column D to the last row which have formulas but no values. As the range will keep varying I will not be able to apply the code to a specific range. Are you able to suggest any solution the issue please.

What exactly is in column F? Formulas or hard-coded values? If formula, what is the formula?
How are these (new values) being added to the worksheet?

 

[SamCha]

What exactly is in column F? Formulas or hard-coded values? If formula, what is the formula?
How are these (new values) being added to the worksheet?

Hi Joe4,
Column F doesnt have any formula neither does Column D. The formulae are in Column A5:C600. A simple IF function and vlookup functions. So apparently this code is assuming that the last row is 600th row

 

[Joe4]

Column F doesnt have any formula neither does Column D. The formulae are in Column A5:C600. A simple IF function and vlookup functions. So apparently this code is assuming that the last row is 600th row

The code makes no such assumptions.

It is dynamically finding the last row by looking for the last entry in column F here:

'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row

If it is going down to line 600, then that means that you have an entry in cell F600.

If you enter this formula in any blank cell, what does it return?

=LEN(F600)

If it returns anything other than 0, then you have something in cell F600 (whether it is visible or not).

 

[SamCha]

The code makes no such assumptions.

It is dynamically finding the last row by looking for the last entry in column F here:

'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row

If it is going down to line 600, then that means that you have an entry in cell F600.

If you enter this formula in any blank cell, what does it return?

=LEN(F600)

If it returns anything other than 0, then you have something in cell F600 (whether it is visible or not).

Thanks a lot Joe4. It was my bad. There were formula in cell F. All good now the code is working perfectly

 

[Joe4]

You are welcome.

If there is another column that makes more sense to look at to find the last row of data, you can just change the "F" in that formula to whatever column works better.