Selective Macro

[pan_kaj]

I have a spreadsheet of information.

One column (column F) header = Business_Area, has 5 unique business areas within it. Lets say they are AA, BB, CC, DD, EE

Column A has the project names and one business Area has anywhere between 20 to 50 project names.

I hv written a macro to pick out project names for business Area AA and paste on a seperate sheet, but how can I customise the Macro, so that when I run it, it gives me a msg box with a drop down with the 5 Business Areas and based on what I select, it gives me the output accordingly?

Any help is much appreciated.

 

[VoG]

I apologise - an elementary error on my part

Try this

Sub BusArea()
Dim Bus, MyBus As String, i As Integer, OK As Boolean
Bus = Array("Revenue Increase", "Capacity Enhancement", "CC", "DD", "EE")
OK = False
Do While OK = False
    MyBus = InputBox("Enter Business Area")
    If MyBus = "" Then Exit Sub
    For i = LBound(Bus) To UBound(Bus)
        If UCase(MyBus) = UCase(Bus(i)) Then
            OK = True
            Exit For
        End If
    Next i
Loop
MsgBox "You chose Business Area " & MyBus
'rest of your code goes here
End Sub

 

[Jonmo1]

If I may interject...

I think the problem is probably the Case Sensitiveness of VBA...

try putting in your Real Values in ALL CAPITAL LETTERS
"REVENUE INCREASE", "CAPAICITY ENHANCEMENTS"

 

[VoG]

If I may interject...

Yes of course you may

I had it in my head (why ) that the business areas were going to be in caps so I initially converted the value from the InputBox directly into caps to allow the user to enter in lower or proper case or whatever.

I think the last code that I posted should fix the problem as I removed the conversion to caps but made the comparison using caps for both the entered value and the array elements.

jonmo1 - I may be dreaming but did I once (or more) see you use WorksheetFunction.Match to do this sort of thing instead of a loop? Or perhaps I've finally lost the plot

 

[pan_kaj]

This works fine VogII.... thanks a lot... (sorry for being a nag!)

Have a nice day!

 

[Jonmo1]

jonmo1 - I may be dreaming but did I once (or more) see you use WorksheetFunction.Match to do this sort of thing instead of a loop? Or perhaps I've finally lost the plot

Yes, I have done stuff like that...But not sure if that will have anything to do with you losing the plot....

you can do something like

x = Application.Match(MyBus,Bus,0)
If IsError(x) Then ' Entered Value Not found in the Array
    Exit Sub
Else
    Msgbox "You chose..." & Bus(x)
End If

I think that will Negate the Case SensitiveNess....

I find that the best method for INcluding / EXcluding Specific Sheets from for Each ws in Sheets Loops..

 

[VoG]

Yes, I have done stuff like that...But not sure if that will have anything to do with you losing the plot....

you can do something like

x = Application.Match(MyBus,Bus,0)
If IsError(x) Then ' Entered Value Not found in the Array
    Exit Sub
Else
    Msgbox "You chose..." & Bus(x)
End If

I think that will Negate the Case SensitiveNess....

I find that the best method for INcluding / EXcluding Specific Sheets from for Each ws in Sheets Loops..

Thanks for that. It makes this a bit neater (if you don't mind GoTos which I try to avoid):

Sub BusArea()
Dim Bus, MyBus As String, i As Integer, OK As Boolean, X As Variant
Bus = Array("Revenue Increase", "Capacity Enhancement", "CC", "DD", "EE")
GetBus:
MyBus = InputBox("Enter Business Area")
If MyBus = "" Then Exit Sub
X = Application.Match(MyBus, Bus, 0)
If IsError(X) Then
    MsgBox "Error: " & MyBus & " not found."
    GoTo GetBus
Else
    MsgBox "You chose Business Area " & MyBus
End If
'rest of your code goes here
End Sub

Interestingly, using WorksheetFunction.Match instead of Application.Match errors out if a non-matching entry is made. Now I just need to figure out why

Thanks again jonmo1

 

[Jonmo1]

Interestingly, using WorksheetFunction.Match instead of Application.Match errors out if a non-matching entry is made. Now I just need to figure out why

Right, I just learned that recently, I think from Schielrn..

I used to do it like this

On Error Resume Next
x = ""
x = WorksheetFunction.Match(Bus,MyBus,0)
On Error Goto 0
If x = "" Then 'Value Not found
    Blah..
Else
    Blah..
End If

It's basically because Application.Match (or just about any of the worksheet functions) will assign the error value to the variable instead of Breaking the code. You can then just test the variable for IsError

Where WorksheetFunction.Match will Break...

 

[Jonmo1]

Actually, nope. I learned it from rorya in this post..
http://www.mrexcel.com/forum/showthread.php?t=318562&highlight=application.match

 

[VoG]

Very helpful jonmo1, thanks again