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]

Something like this should get you started

Sub BusArea()
Dim Bus, MyBus As String, i As Integer, OK As Boolean
Bus = Array("AA", "BB", "CC", "DD", "EE")
OK = False
Do While OK = False
    MyBus = UCase(InputBox("Enter Business Area"))
    For i = LBound(Bus) To UBound(Bus)
        If MyBus = 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

 

[pan_kaj]

Thanks

there seems to be some problem though... it goes in to a never ending loop of the msg window with the Q for business area even when i fill in soemthing as defined in the array. On terminating the macro and using debug it stops at:

For i = LBound(Bus) To UBound(Bus)

Any ideas what might be wrong?

 

[VoG]

I can't replicate that but this allows you to exit by entering nothing and pressing OK or Cancel.

Sub BusArea()
Dim Bus, MyBus As String, i As Integer, OK As Boolean
Bus = Array("AA", "BB", "CC", "DD", "EE")
OK = False
Do While OK = False
    MyBus = UCase(InputBox("Enter Business Area"))
    If MyBus = "" Then Exit Sub
    For i = LBound(Bus) To UBound(Bus)
        If MyBus = 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

 

[pan_kaj]

There is something wrong here

For i = LBound(Bus) To UBound(Bus)
If MyBus = Bus(i) Then

In terms of a type mismatch

i is an integer and bus is a string - so something is conflicting in these lines.....

Any ideas?

 

[VoG]

Bus is an array and Bus(i) references the ith element of Bus.

Did you try the original code I posted - it works for me.

 

[pan_kaj]

No it didnt work.... infact after entering the Business Area 'AA' in the msg box, when it asked for the business area again, I did a CTRL break. At this point the value of i was being passed as 5, which was why this was failing i presume. Dont know what the problem is.

 

[pan_kaj]

This is how the relevant part of my code now looks like


Sheets("Programme Scope").Select
Dim Bus, MyBus As String, i As Integer, OK As Boolean
Bus = Array("AA", "BB", "CC", "DD", "EE")
OK = False
Do While OK = False
MyBus = UCase(InputBox("Enter Business Area"))
For i = LBound(Bus) To UBound(Bus)
If MyBus = Bus(i) Then
OK = True
Exit For
End If
Next i
Loop
MsgBox "You chose Business Area " & MyBus


Range("Y2").Select
Selection.AutoFilter Field:=6, Criteria1:=MyBus
Selection.AutoFilter Field:=25, Criteria1:="Startup"
Range("E20").Select
Range("E3:E130").Select
Selection.Copy
Sheets("Phase_Wise").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[VoG]

I'm afraid that I cannot replicate the error.

 

[pan_kaj]

VoGII,

I did some tests, it seems as long as I keep the Array definition as AA, BB etc its fine.... but as soon as i put in real values like "Revenue Increase", "Capacity Enhancements" it fails.