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]

Hi, and Welcome to Mr. Excel!

I know that you specified a VBA solution, but wondered whether you wanted this, because you didn't think it could be done with a formula?

I'm assuming that your "Q" data is formatted at text...
If you put this formula into P3:
=CHOOSE(LEFT(Q2,2),"ON","MB","CA","FJ","BH","NJ","KO","PO","gg","KK")
... then replace my "codes" in the formula, with your correct Province codes (based on the first two characters of your Q column values), it should pick them out for you.
As long as P3 works out, just copy (drag) down.

 

[peng_p]

Thanks!

I specified VBA because they're weekly reports (new spreadsheet) and I am building a macro to automate the process of customizing the data for reporting purposes.

There a number of other instructions in the macro and I just needed help with this portion of it.

Is there a way I can incorporate your formula into it?

 

[Fluff]

How many codes like ON, MB are there?

 

[sykes]

If you're already using VBA, then it's probably a good idea to incorporate this into your existing code. In the long run, it'll give you more flexability, too.
If you post your current code, it might be easier to just amend it, to include this requirement.

@Fluff
I'm guessing the OP's data deals with the Canadian provinces, so I've assumed there will be 10-15, or so codes...

 

[peng_p]

Lets go with 5 codes for testing purposes.

 

[sykes]

I'm guessing that Fluff will come back with a solution, and it'll be better than mine (always is!)
However, in the absence of that...

Test on a COPY of your data, until it all works!!

Just as a demo for you:
I put a command button on the sheet in question - just to run the code.
CommandButton 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
    cl.Offset(0, -1).Value = Mid(pcodes, (Left(cl.Value, 2) * 2) - 1, 2)
Next

End Sub

This string contains the province codes (put your real ones in, in their place): pcodes = "ONMBHHYTURFDNMTTBNGHYTRED"
They correspond to the first two numbers in your Q col numbers, so "ON" will correspond to any code beginning in 01 (eg 0103, 0187 etc) "MB" will be entered into your "P" col for any Q codes starting in 02 (eg 0247, 0231 etc). You'll therefore need to replace the dummy letters in my "pcodes" string, with the real ones - so the first 2 letters for 01, next 2 for 02 etc and so on.
I've used the ME keyword, so the button needs to be on the sheet in question, and the code needs to be in the button's _click event. This is just for the purpose of demo - if useable, it would actually get incorporated into the rest of your code.

Let us know how you get on.

 

[Fluff]

Another option

Public Sub peng()
   Dim Cl As Range
   Dim Ary As Variant
  
   Ary = Array("ON", "MB", "QB", "NL")
   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) - 1)
      End If
   Next Cl
End Sub

 

[peng_p]

It's debugging at Cl.Offset(, -1) = Ary(Left(Cl, 2) - 1).

There is nothing being entered in column P. Can you also explain this code to me?

Is there a check in the code that begins by checking if column O greater than zero? I see If Cl.Offset(, -2) > 0 Then, where Cl is defined as the range of Q3 until last row. Shouldn't this be defined as O and then we have another line that defines column Q (Q like Quiet not O like Octopus).

At which point, if the value in column O is greater than zero, it checks column Q for the values starting with 04, 03, 08, etc. I'm not completely understand this portion of the code where it's pulling the Array. How does it match a specific text beginning with?

 

[Fluff]

What are your abreviations & what are the 1st 2 digits of the 4 digit code for each?