VBA IF Statement based on multiple cell values and return text based on a value range/beginning with

[peng_p]

Hi, I'm trying to learn how to code a macro for a spreadsheet I am working on and I am stuck at this part of the process:

What I am looking for:
If column O value is greater than zero then
If column Q value is between 0400 to 0499, then print "ON" in column P else
If column Q value is between 0300 to 0399, then print "MB" in column P else
So on and so forth...
It is a running list, so there could be 10 rows of data or 1000.

Alternatively, instead of a between check for column Q, would it be easier to have it check the first two digits instead? i.e. 04, 03, etc.

Sorry if this is very simplistic, I am still trying to learn and my online resources haven't helped me. Thank you for helping.

 

[sykes]

...I've only just seen that I didn't include a test for col "O" values being >0.
Here's the amended code:

Private Sub CommandButton1_Click()
Dim pcodes As String
Dim cl As Range
Dim rng As Range
Dim lstrw As Integer

lstrw = Me.Range("Q10000").End(xlUp).Row
Set rng = Me.Range("Q2:Q" & lstrw)
pcodes = "ONMBHHYTURFDNMTTBNGHYTRED"
For Each cl In rng
    If cl.Offset(0, -2).Value > 0 Then  cl.Offset(0, -1).Value = Mid(pcodes, (Left(cl.Value, 2) * 2) - 1, 2)
Next
End Sub

 

[peng_p]

04 - ON
03 - QB
05 - MB
08 - BC
etc.

I also want to make sure that it is only checking this column after checking that column O is greater value than zero, else it should leave it blank.

 

[Fluff]

Saying etc. does not help. I need to know all the possible codes in order to come up with a solution.

 

[peng_p]

Use the four, as they're the core of the data.

 

[Fluff]

For one last time, can you please supply all the codes/abbreviations.

 

[peng_p]

I'm basing it that those are the only ones that would be used, sorry.

 

[Fluff]

In that case try

Public Sub peng()
   Dim Cl As Range
   Dim Ary As Variant
  
   Ary = Array("QB", "ON", "MB", "", "", "BC")
   For Each Cl In Range("Q3", Range("Q" & Rows.Count).End(xlUp))
      If Cl.Offset(, -2) > 0 Then
         Cl.Offset(, -1) = Ary(Left(Cl, 2) - 3)
      End If
   Next Cl
End Sub

 

[peng_p]

Amazing, thank you so much! It works well!

I think I understand the coding and your reasoning to know the constant value/abbreviation now when I review it.

 

[Fluff]

You're welcome & thanks for the feedback