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

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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
 
Solution

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
'   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?
Excel Formula:
=LEN(F600)
If it returns anything other than 0, then you have something in cell F600 (whether it is visible or not).
 

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
The code makes no such assumptions.

It is dynamically finding the last row by looking for the last entry in column F here:
VBA Code:
'   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?
Excel Formula:
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,724
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top