awesome, here's a link
http://www.box.net/shared/o0uley3sbbh3fxu315ou
The tabs that say FHCX, FHMX, and FNMX are what i wanted the results
to be once the loop happened...
any ideas for code or logic i can use?
On Oct 8, 3:29 pm, Sam Mathai Chacko <samde...@gmail.com> wrote:
> Just go to your gmail inbox, and reply to the mail.
>
> OR
>
> Go to box.net, and you can upload your file there, and provide a link here.
>
> Regards,
>
> Sam
>
>
>
>
>
>
>
>
>
> On Sun, Oct 9, 2011 at 12:57 AM, Monizri <moni...@gmail.com> wrote:
> > Thanks Don, I can try FIND instead of looping, i am trying to add my
> > file but i don't see anywhere i can attach my file. Do you know where?
>
> > Tx
>
> > Matt
>
> > On Oct 8, 11:47 am, "dguillett1" <dguille...@gmail.com> wrote:
> > > It would be helpful to post your files with before/after examples. I
> > would
> > > think that instead of using a loop you would use FIND (look in vba help)
>
> > > Don Guillett
> > > SalesAid Software
> > > dguille...@gmail.com
>
> > > -----Original Message-----
> > > From: Monizri
> > > Sent: Saturday, October 08, 2011 9:01 AM
> > > To: MS EXCEL AND VBA MACROS
> > > Subject: Re: $$Excel-Macros$$ Read a menu and produce results
>
> > > Hi Sam, I apologize for not being clear, i was sure how to pose the
> > > question.
> > > I have two spreadsheets. One with a menu with a bunch of criteria (of
> > > which you can select the criteria you want by placing an "X" in a
> > > designated area ). The second spreadsheet will have data i need to
> > > search through. So based on the selections on the menu page, i want it
> > > to loop through the data on the data page a find the criteria
> > > selections in the data sheet and pick the data in that row and post it
> > > in a new tab/worksheet.
>
> > > -Matt
>
> > > I have two pieces of code i've been trying to work with but am unsure
> > > how to connect them. The first one reads a menu and the ranges and
> > > thats it. The second one, will create a new tab with search results.
> > > How can i get the two codes to work together? to read the menu and
> > > produce the results...
>
> > > 1) 'reads the menu
>
> > > Sub Read_Geographic_Menu()
> > > Sheets("Selections").Select
> > > Range("A1").Select
>
> > > i_state = 0
> > > g_num_select_states = 0
>
> > > For icol = 1 To STATES_NUM_COLS
> > > For irow = 1 To STATES_PER_COL
> > > i_state = i_state + 1
> > > g_state_select(i_state) = False
> > > g_state_postal(i_state) = Trim(Range("m6StatePostal" &
> > > icol).Cells(irow))
> > > g_state_name(i_state) = Trim(Range("m6StatesNames" &
> > > icol).Cells(irow))
> > > g_state_id_number(i_state) = i_state
> > > If Range("m6States" & icol).Cells(irow) <> "" Then
> > > g_state_select(i_state) = True
> > > g_num_select_states = g_num_select_states + 1
> > > End If
> > > Next irow
> > > Next icol
> > > End Sub
>
> > > 2) searches for data (in this case "FHCX") and pulls the column data i
> > > need.
> > > Sub ServiceReport()
> > > ' Copy the selected range to the Report worksheet
> > > Dim WSD As Worksheet ' Data worksheet
> > > Dim WSR As Worksheet ' Report worksheet
>
> > > Set WSD = Worksheets("Data")
>
> > > ' Add a new worksheet to this workbook
> > > Set WSR = Worksheets.Add(after:=Worksheets("Data"))
>
> > > ' Rename the new worksheet & set up titles
> > > WSR.Name = "Service"
> > > WSR.Cells(1, 1) = "Service Report"
> > > WSR.Cells(1, 1).Font.Size = 14
>
> > > WSD.Range("A1:E1").Copy Destination:=WSR.Cells(3, 1)
> > > NextRow = 4
>
> > > ' Loop through all records on WSD
> > > FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
> > > For i = 2 To FinalRow
> > > If WSD.Cells(i, 2) = "FHCX" Then
> > > ' Copy this record to the next row on WSR
> > > WSD.Cells(i, 1).Resize(1, 5).Copy Destination:=WSR.Cells(NextRow, 1)
> > > NextRow = NextRow + 1
> > > End If
> > > Next i
>
> > > ' Make sure WSR is the active sheet
> > > WSR.Select
>
> > > ' Report that the macro is done
> > > MsgBox Prompt:=NextRow - 4 & " service records copied to the service
> > > report."
>
> > > End Sub
>
> > > On Oct 8, 12:17 am, Sam Mathai Chacko <samde...@gmail.com> wrote:
> > > > I believe the forum is not able to comprehend what 'can i get excel to
> > > > read
> > > > a menu with selections using "X" in a cell' is trying to convey. Maybe
> > you
> > > > want to rephrase it, or share an example of what you are trying to do.
>
> > > > Sam
>
> > > > On Fri, Oct 7, 2011 at 10:55 PM, Monizri <moni...@gmail.com> wrote:
> > > > > Hi, I have the following code that searches for data based on what i
> > > > > type in it (in this case it is "FHCX") and posts it in a new sheet.
> > > > > How can i get excel to read a menu with selections using "X" in a
> > cell
> > > > > and feed it into the code below to produce results on a new tab?
>
> > > > > Sub ServiceReport()
> > > > > ' Copy the selected range to the Report worksheet
> > > > > Dim WSD As Worksheet ' Data worksheet
> > > > > Dim WSR As Worksheet ' Report worksheet
>
> > > > > Set WSD = Worksheets("Data")
>
> > > > > ' Add a new worksheet to this workbook
> > > > > Set WSR = Worksheets.Add(after:=Worksheets("Data"))
>
> > > > > ' Rename the new worksheet & set up titles
> > > > > WSR.Name = "Service"
> > > > > WSR.Cells(1, 1) = "Service Report"
> > > > > WSR.Cells(1, 1).Font.Size = 14
>
> > > > > WSD.Range("A1:E1").Copy Destination:=WSR.Cells(3, 1)
> > > > > NextRow = 4
>
> > > > > ' Loop through all records on WSD
> > > > > FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
> > > > > For i = 2 To FinalRow
> > > > > If WSD.Cells(i, 2) = "FHCX" Then
> > > > > ' Copy this record to the next row on WSR
> > > > > WSD.Cells(i, 1).Resize(1, 5).Copy
> > > > > Destination:=WSR.Cells(NextRow, 1)
> > > > > NextRow = NextRow + 1
> > > > > End If
> > > > > Next i
>
> > > > > ' Make sure WSR is the active sheet
> > > > > WSR.Select
>
> > > > > ' Report that the macro is done
> > > > > MsgBox Prompt:=NextRow - 4 & " service records copied to the
> > > > > service report."
>
> > > > > End Sub
>
> > > > > --
>
> > ----------------------------------------------------------------------------------
> > > > > Some important links for excel users:
> > > > > 1. Follow us on TWITTER for tips tricks and links :
> > > > >http://twitter.com/exceldailytip
> > > > > 2. Join our LinkedIN group @
> >http://www.linkedin.com/groups?gid=1871310
> > > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > > Like our page on facebook , Just follow below link
> > > > >http://www.facebook.com/discussexcel
>
> > > > --
> > > > Sam Mathai Chacko
>
> > > --
>
> > ----------------------------------------------------------------------------------
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below linkhttp://
> >www.facebook.com/discussexcel
>
> > --
>
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
