Re: $$Excel-Macros$$ Read a menu and produce results

dguillett1 Sat, 08 Oct 2011 13:07:27 -0700


I just attach to email. Send to me if you like.

Don Guillett
SalesAid Software
dguille...@gmail.com

-----Original Message-----From: Monizri

Sent: Saturday, October 08, 2011 2:27 PM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ Read a menu and produce results

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 belowlinkhttp://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 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

--
----------------------------------------------------------------------------------
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

Re: $$Excel-Macros$$ Read a menu and produce results

Reply via email to