Re: $$Excel-Macros$$ A macro pauses execution for no reason
Use application.enableCancelKey false at the top of module and true
at the end of module
On 09/10/2011, hanumant shinde wrote:
> Hi Paul,
>
> this is superb i never knew this thing. never even thought this might be
> happening.
> thanks for sharing it.
>
>
>
>
>>
>>From: Paul Schreiner
>>To: excel-macros@googlegroups.com
>>Sent: Friday, 7 October 2011 4:05 PM
>>Subject: Re: $$Excel-Macros$$ A macro pauses execution for no reason
>>
>>
>>You said that it stops at a "random" point.
>>It it the same point every time? or a different ("random") point?
>>
>>I've encountered something similar, it's as if a breakpoint is being saved.
>>Even when I exit Excel and re-open the file.
>>
>>When it stops, I was able to select Debug-> Clear_All_Breakpoints
>> (ctrl-shift-F9)
>>then save it, and the stopping... stopped.
>>
>>Paul
>>-
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-
>>
>>
>>
>>
>>
>>
>> From: carl.malmquist
>>To: MS EXCEL AND VBA MACROS
>>Sent: Thu, October 6, 2011 6:16:36 PM
>>Subject: $$Excel-Macros$$ A macro pauses execution for no reason
>>
>>A coworker sent me a workbook with a macro that runs fine on my
>>laptop. On her laptop, the macro runs and at a random point within
>>the macro, it pauses, as if that spot in the code has a break point.
>>Hitting run restores the macro to running and it completes normally.
>>
>>Has anyone experienced with with their own code? Does anyone have any
>>suggestions for a fix?
>>
>>Thanks.
>>
>>--
>>--
>>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
>>
>>
>>
>
> --
> --
> 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
>
--
Sent from my mobile device
Regards
Rajan verma
+91 9158998701
--
--
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$$ Working out of Statistics on Exam Results URGENT solution needed!
Hi I just had a query from someone asking me if I want the stats worked out from the SCHEDULE sheet of the Grade 8 file, yes that is correct as that sheet contains all the codes per subject /learning area for each student/learner On Sat, Oct 8, 2011 at 5:51 PM, shaneallen wrote: > http://www.box.net/shared/et0q8my9qpvjefeciryx > > HI > > This is the link to the Grade 8 file for which I want stats to be worked > out like the Grade 9 schedule > > -- > > -- > 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
I take back my comment earlier, this code works great thank you!
On Oct 8, 4:51 pm, Sam Mathai Chacko wrote:
> Try this. Code with file attached.
>
> Sub Consolidator()
>
> Dim rngCell As Range
> Dim wks As Worksheet
>
> Application.ScreenUpdating = False
> For Each rngCell In Worksheets("Selections").Range("XColumn").Cells
> 'Where XColumns is a named range having the 3 columns where user marks X
> If rngCell.Value = "X" Then
> Worksheets("Data").UsedRange.AutoFilter 2, rngCell.Offset(,
> 1).Value
> If Worksheets("Data").Cells(Rows.Count, 2).End(xlUp).Row > 1
> Then
> On Error Resume Next
> Set wks = Worksheets(rngCell.Offset(, 1).Value)
> Err.Clear: On Error GoTo -1: On Error GoTo 0
> If wks Is Nothing Then
> Set wks =
> ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
> wks.Name = Left(rngCell.Offset(, 1).Value, 31)
> Worksheets("Data").UsedRange.Copy wks.Cells(1)
> With wks.UsedRange
> .EntireRow.RowHeight = 40
> .EntireColumn.ColumnWidth = 200
> .EntireColumn.AutoFit
> .EntireRow.AutoFit
> .WrapText = True
> End With
> Else
> Worksheets("Data").Range(Worksheets("Data").Cells(2, 1),
> Worksheets("Data").Cells(Worksheets("Data").Cells(Rows.Count,
> 2).End(xlUp).Row, 5)).Copy wks.Cells(wks.Rows.Count, 2).End(xlUp).Offset(1,
> -1)
> End If
> Set wks = Nothing
> End If
> End If
> Next rngCell
> Worksheets("Data").AutoFilterMode = False
> Application.Goto Worksheets("Selections").Cells(1)
> Application.ScreenUpdating = True
>
> Set rngCell = Nothing
>
> End Sub
>
> On Sun, Oct 9, 2011 at 2:19 AM, Monizri wrote:
> > The autofilter can be an alternative. Is it possible get the loop to
> > read into the second bit of code where it has "FHCX" and spit out
> > code that way? Thats where ive been racking my brain.
>
> > I do appreciate this Don
>
> > On Oct 8, 4:29 pm, "dguillett1" wrote:
> > > After looking at your file on the link provided it seems that you want to
> > > simply use data>autofilter>copy. If that is correct, it is fairly easy to
> > > write a macro to loop thru looking for X and then filter the data sheet
> > by
> > > that value and copy to the other sheets. Is that what you want?
>
> > > Sub filterandcopy()
> > > 'for each c in activesheet.usedrange.specialcells(xlce
> > > For Each c In Range("b2:b21,f2:f21,j2,j21")
> > > If UCase(c) = "X" Then
> > > 'MsgBox c.Row
> > > End If
> > > Next c
> > > End Sub
>
> > > Don Guillett
> > > SalesAid Software
> > > dguille...@gmail.com
>
> > > -Original Message-
> > > From: Monizri
> > > Sent: Saturday, October 08, 2011 2:56 PM
> > > To: MS EXCEL AND VBA MACROS
> > > Subject: Re: $$Excel-Macros$$ Read a menu and produce results
>
> > > awesome, here's a linkhttp://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 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 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" 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 t
Re: $$Excel-Macros$$ Read a menu and produce results
The autofilter can be an alternative. Is it possible get the loop to
read into the second bit of code where it has "FHCX" and spit out
code that way? Thats where ive been racking my brain.
I do appreciate this Don
On Oct 8, 4:29 pm, "dguillett1" wrote:
> After looking at your file on the link provided it seems that you want to
> simply use data>autofilter>copy. If that is correct, it is fairly easy to
> write a macro to loop thru looking for X and then filter the data sheet by
> that value and copy to the other sheets. Is that what you want?
>
> Sub filterandcopy()
> 'for each c in activesheet.usedrange.specialcells(xlce
> For Each c In Range("b2:b21,f2:f21,j2,j21")
> If UCase(c) = "X" Then
> 'MsgBox c.Row
> End If
> Next c
> End Sub
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>
>
> -Original Message-
> From: Monizri
> Sent: Saturday, October 08, 2011 2:56 PM
> To: MS EXCEL AND VBA MACROS
> Subject: Re: $$Excel-Macros$$ Read a menu and produce results
>
> awesome, here's a linkhttp://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 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 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" 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.Sele
Re: $$Excel-Macros$$ Read a menu and produce results
After looking at your file on the link provided it seems that you want to
simply use data>autofilter>copy. If that is correct, it is fairly easy to
write a macro to loop thru looking for X and then filter the data sheet by
that value and copy to the other sheets. Is that what you want?
Sub filterandcopy()
'for each c in activesheet.usedrange.specialcells(xlce
For Each c In Range("b2:b21,f2:f21,j2,j21")
If UCase(c) = "X" Then
'MsgBox c.Row
End If
Next c
End Sub
Don Guillett
SalesAid Software
dguille...@gmail.com
-Original Message-
From: Monizri
Sent: Saturday, October 08, 2011 2:56 PM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ Read a menu and produce results
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 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 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" 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 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 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 pos
Re: $$Excel-Macros$$ Read a menu and produce results
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" 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 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 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
Re: $$Excel-Macros$$ Read a menu and produce results
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 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 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" 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 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 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 = Wor
Re: $$Excel-Macros$$ A macro pauses execution for no reason
Hi Paul,
this is superb i never knew this thing. never even thought this might be
happening.
thanks for sharing it.
>
>From: Paul Schreiner
>To: excel-macros@googlegroups.com
>Sent: Friday, 7 October 2011 4:05 PM
>Subject: Re: $$Excel-Macros$$ A macro pauses execution for no reason
>
>
>You said that it stops at a "random" point.
>It it the same point every time? or a different ("random") point?
>
>I've encountered something similar, it's as if a breakpoint is being saved.
>Even when I exit Excel and re-open the file.
>
>When it stops, I was able to select Debug-> Clear_All_Breakpoints
>(ctrl-shift-F9)
>then save it, and the stopping... stopped.
>
>Paul
>-
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-
>
>
>
>
>
>
> From: carl.malmquist
>To: MS EXCEL AND VBA MACROS
>Sent: Thu, October 6, 2011 6:16:36 PM
>Subject: $$Excel-Macros$$ A macro pauses execution for no reason
>
>A coworker sent me a workbook with a macro that runs fine on my
>laptop. On her laptop, the macro runs and at a random point within
>the macro, it pauses, as if that spot in the code has a break point.
>Hitting run restores the macro to running and it completes normally.
>
>Has anyone experienced with with their own code? Does anyone have any
>suggestions for a fix?
>
>Thanks.
>
>--
>--
>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
>
>
>
--
--
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
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 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" 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 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 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
> > > >
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" 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 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 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 r
Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!
http://www.box.net/shared/et0q8my9qpvjefeciryx HI This is the link to the Grade 8 file for which I want stats to be worked out like the Grade 9 schedule -- -- 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$$ Querry ncg
I suggest simply selecting your name cells in col B>data>text to columns>space>finish Don Guillett SalesAid Software dguille...@gmail.com From: Nemi Gandhi Sent: Saturday, October 08, 2011 4:37 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Querry ncg Plaease provide solution. -- Nemi Gandhi 98204 92963 -- -- 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
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 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 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
Re: $$Excel-Macros$$ Querry ncg
check the attachment On Sat, Oct 8, 2011 at 3:07 PM, Nemi Gandhi wrote: > Plaease provide solution. > > -- > Nemi Gandhi > 98204 92963 > > -- > > -- > 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 > -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* P Before printing, think about the environment. -- -- 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 querry ncg.xlsm Description: Binary data
$$Excel-Macros$$ Querry ncg
Plaease provide solution. -- Nemi Gandhi 98204 92963 -- -- 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 querry ncg.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
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 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 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
--
Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!
I hope u are able to access the link so you can see the example in the Grade 9 schedule regarding the stats I want to generate in the Grade 8 schedule -- -- 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$$ Working out of Statistics on Exam Results URGENT solution needed!
People working on this solution must remember I paste this solution in the schedules of the other classes in Grade 8 as well.Unless I can paste all the schedules for the different Grade 8 classes together and get all the stats for all the classes at one go -- -- 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$$ Want to Highlight find Cell
Hi Try: Sub Mtest() Dim found As Range Dim m As String, Temp As String Dim count As Integer Dim ws As Worksheet count = 0 m = InputBox(prompt:="Enter value for search", Title:="Excel Find") For Each ws In ActiveWorkbook.Worksheets Set found = ws.Cells.Find(What:=m, LookIn:=xlValues, lookat:=xlPart) If Not found Is Nothing Then count = count + 1 MsgBox found.Worksheet.Name & found.Cells.Address, Title:="Excel Find" found.Cells.Interior.ColorIndex = 6 Temp = MsgBox(prompt:="Clear highlighting", Title:="Excel Find", Buttons:=vbOKCancel + vbQuestion) If Temp = vbOK Then found.Cells.Interior.ColorIndex = xlNone End If Next ws If count = 0 Then MsgBox prompt:="Not found", Title:="Excel Find" End Sub HTH Mahesh On Sat, Oct 8, 2011 at 12:41 PM, B.N.Chethan kumar < chetankumar1...@gmail.com> wrote: > Hi, > > you give a name to cell...by using name managerand later need u call > name to . > > i have attached sample file for easy understanding > > Regards > Chethan Kumar BN > > On Fri, Oct 7, 2011 at 7:36 PM, satish > wrote: > >> Dear all, >> >> Sometimes I work with large data, if i want to find some data, >> normally I type the key word in find option, I want to highlight the >> cell with some colour to view it clearly, which cell is highlighted. >> Normally it goes to cell but i have some problem ( eye sight). >> >> Thanks in advance >> >> -- >> >> -- >> 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 >> > > > > -- > B.N Chetan kumar > > -- > > -- > 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
