DJMXM
New Member
- Joined
- Jun 19, 2013
- Messages
- 45
Hello everyone - I am continuing work on this project and have bumped into another VBA Question. I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below. Thank You again for all of you that have helped me get up to speed with VBA.
For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.
What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between
Thank You!
Mike
<tbody>
</tbody>
For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.
What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between
Thank You!
Mike
<tbody>
</tbody>
Code:
Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long
Application.ScreenUpdating = False
Set wsSource = Worksheets("Catering and Rental Worksheet")
Set wsDest = Worksheets("Catering BEO")
' Food Check Box Section
Set FoundX = wsSource.Range("AI3:AI52").Find(What:="TRUE", _
LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
If FoundX Is Nothing Then
Exit Sub
Else
FirstFound = FoundX.Address
Do
lastrow = wsDest.Range("I" & Rows.Count).End(xlUp).Row + 1
If lastrow < 3 Then lastrow = 3
wsDest.Range("I" & lastrow).Resize(1, 1).Value = FoundX.Offset(0, -7).Resize(1, 1).Value
wsDest.Range("J" & lastrow).Resize(1, 2).Value = FoundX.Offset(0, -5).Resize(1, 2).Value
wsDest.Range("L" & lastrow).Resize(1, 1).Value = FoundX.Offset(0, 1).Resize(1, 1).Value
lastrow = lastrow + 1
FoundX.Value = "TRUE"
Set FoundX = wsSource.Range("AI3:AI52").FindNext(FoundX)
Loop Until FoundX.Address = FirstFound
End If
End Sub