$$Excel-Macros$$ Fwd: regarding pivot table problem

[Sanjib Chatterjee]

Dear Experts,

Would you please help me regarding the problem.

Thanking you

Sanjib


-- Forwarded message --
From: Sanjib Chatterjee 
Date: Mon, Apr 9, 2012 at 4:48 PM
Subject: regarding pivot table problem
To: excel-macros 



Dear Sir,
previously i sent you the mail.  But I got the error message that the
message I am sending
is cross the allocated limit so I am sending you the fresh message.

Please see the attachment.  I have created the Pivot table based on the
data of sheet1.  Then
follow the step for creating pivot table.  But I want in same different
format.  In BS column
referred to buy and sale of scripts.  If there is B in the column then
quantity should be put
as buy qty and next buy rate.  If there is S in the column then quantity
should be put as
sale and next sale rate.  Net qty should be buy - sale quantity.

Please help to solve the problem

Thanking you in Advance.

Sanjib


-- 
-



-- 
-

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Copy of EXAMPLE_VKGOEL.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Excel - Useful Web Links

[VARUN CHAWLA]

On Sat, Apr 7, 2012 at 3:09 AM, Maries  wrote:

> Dear Friends,
>
> I have accumulate the my collection for Excel. Please update, If you
> anything more.
>
> I hope these links are very useful everyone.
>
>   http://www.andrewsexceltips.net/ 
>  http://www.andypope.info/ 
>  http://members.aol.com/Machero 
>  http://www.appspro.com 
>  http://www.ashishmathur.com 
> http://www.bettersolutions.com/excel.aspx
>  http://www.beyondtechnology.com 
>  http://akoul.blogspot.com 
>  http://exceler.blogspot.com 
> http://ashish-msaccess.blogspot.com/  http://exceldailytip.blogspot.com/
> http://exceltnt.blogspot.com/  http://liyakatalilal.blogspot.com/
> http://quickvba.blogspot.com/  http://learningiseasy.co.uk/Home.aspx
> peltiertech.com   http://ExcelExperts.com 
>  http://spreadsheetpage.com 
> http://clearlyandsimply.com/  http://excelunusual.com/
> http://exceluser.com/blog/
> http://peltiertech.com/Excel/ChartsHowTo/index.html
> http://www.computergaga.com/excel/msxl_training.html
> http://www.contextures.com/  www.cpearson.com   
> http://www.cpearson.com
> http://www.cpearson.com/excel/MainPage.aspx  www.dailydoseofexcel.com/
>  http://www.dailydoseofexcel.com/ 
> www.datapigtechnologies.com   
> http://www.datapigtechnologies.com
> http://www.datapigtechnologies.com/ExcelMain.htm
>  http://www.discussexcel.com 
> http://www.dzikosoft.com/gmexcel/   
> http://www.edferrero.com
> www.exceler.blogspot.com  www.ExcelExperts.com
> www.ExcelExpertTraining.com 
> www.excelforum.com  www.excelfox.com  www.excelpoweruser.blogspot.com
> www.exceluser.com/   
> http://www.ExcelExpertTraining.com
>  http://www.excelguru.ca 
>  http://www.exceluser.com/ 
> http://www.excel4apps.com/  http://www.excel-exercice.com/
> http://www.excelforum.com/  http://www.excelhero.com/
> http://www.exceltip.com/index.php  http://www.excel-vba.com/
> http://www.excelvbamacros.com/  https://www.facebook.com/ExcelLover
> https://www.facebook.com/l.php?u=http%3A%2F%2Fwww.excelitems.com%2F&h=014d2&ref=ts
> https://www.facebook.com/microsoftexcel
> https://www.facebook.com/pages/Excel-and-VBA-Codes-Macros/15180389897
> http://www.facebook.com/groups/163491717053198/
> http://www.facebook.com/groups/214214011997936/
> http://www.facebook.com/pages/Access-VBA-Macros-Codes/212032078844161
> http://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897
> http://www1.finance30.com/  http://www.free-training-tutorial.com/
>  http://xcell05.free.fr   www.freevbcode.com
> http://www.functionx.com/excel/
> http://www.functionx.com/vbaexcel/index.htm
> http://www.getexceltraining.com/
> https://sites.google.com/site/chilexcel/Home
> https://people.highline.edu/mgirvin/ExcelIsFun.htm
> http://www.homeandlearn.co.uk/ME/MicrosoftExcel.html
> http://www.jiguparmar.com/   http://www.jkp-ads.com
>  http://dj.joss.free.fr/    
> http://www.jumper.ch
>  http://www.lacher.com    
> http://www.longhead.com/
> http://www.lynda.com/
> http://msdn.microsoft.com/en-in/office/aa905411.aspx
> http://technet.microsoft.com/en-us/office/gg605176.aspx
> http://www.mrexcel.com  http://blogs.msdn.com/b/excel/
> www.mvps.org/dmcritchie/excel/excel.htm  www.mvps.org/links.html
>  http://orlando.mvps.org 
>  
> http://www.mvps.org/dmcritchie/excel/excel.htm
> http://www.mvps.org/links.html#Excel   
> http://puremis.net/excel/
> www.nickhodge.co.uk   http://www.nickhodge.co.uk
> www.oaltd.co.uk   http://www.oaltd.co.uk 
> http://blogs.office.com/b/microsoft-excel/  chandoo.org 
> http://chandoo.org/  www.ozgrid.com  http://www.ozgrid.com/
>  http://home.pacbell.net/beban 
> www.peltiertech.com/   
> http://www.peltiertech.com/
> akoul.posterous.com  http://www.powerutilsnet.com/default.htm
> www.prodomosua.eu   http://www.prodomosua.eu 
>  http://users.quick-line.ch/ramel/ 
> www.r-cor.com   http://www.r-cor.com 
>  http://www.rondebruin.nl 
> www.spreadsheetpage.com  http://www.strax

RE: $$Excel-Macros$$ count formula problem...

[Mohammed Muneer]

Thank u Noor...

 

 

Regards,

Muneer,

CC...

 

 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ count formula problem...

[Mohammed Muneer]

Thanks Group..

 

 

Regards,

Muneer,

CC...

 

 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ SPLIT into many FILES , depeding UPON...

[Somnath Khadilkar]

Sir,
A while ago, you had sent me a file,  [ which I am enclosing ] to
convert the DATA file into MANY SHEETS depeding upon filter condition,
That is V useful, but I would like to generate NewFIles [ instead of
New Sheets in the same XL file, since, i want to email them [using
AUTOmailer, but I reqd separate files], hence sir, i need your help.

=mangal ho

On 4/10/12, dguillett1  wrote:
> A looping macro can be developed to filter the data and send it to each.
> Properly done you need only ONE mouse click. Or, if it takes awhile to run,
> set to run after you go home for the day.
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message-
> From: Somnath Khadilkar
> Sent: Monday, April 09, 2012 11:58 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ SPLIT into many FILES , depeding UPON...
>
> Dear Sir,
> The foremost reason is I need to EMAIL this file  to the
> Respective CityCircle, & they require ONLY there own CITY info. The
> separate files so created will be saving lot of our SPACE .Currently
> anyway I am doing it MANUALLY [ rathersorry state!! ] Print out of
> the repo, we do at our HdOfice. and w/o a macro it takes too much of
> time...[ over five hundred LEFT clicks alone!! ]
>
> =mangal ho
>
> On 4/9/12, dguillett1  wrote:
>> I haven't followed all of this but it seems to me that there is no reason
>> for separate files. To get reports or print you can use filtering
>> with/without macros and use only ONE file.  Good design???
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message-
>> From: Somnath Khadilkar
>> Sent: Monday, April 09, 2012 9:58 AM
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
>> condition
>>
>> Sir,
>>there are many many colm.in the sheets  [ that mighht complex the
>> issue ] , but as a sample I am able to limit to the TWO fields and one
>> QTY field. I need all the colm A thru C here in the RESPECTIVE files.
>> ---
>>
>> issue 2.
>>I am able to generate a  datafile [ thru some 'C' code , can you
>> guide me to 'CONVERT' these into XLS files thru some tool, [ w/o
>> openining/saving individuay, as these are TOO many, I need to
>> AUTOMAIL ] THIS is altogether a diff. issue, but any help will be
>> highly appreciated ]
>>
>> =Mangal Ho
>>
>> On 4/9/12, Rajan_Verma  wrote:
>>> So , what the excel file will conatin? Only one Row?
>>>
>>>
>>> -Original Message-
>>> From: excel-macros@googlegroups.com
>>> [mailto:excel-macros@googlegroups.com]
>>> On Behalf Of Somnath Khadilkar
>>> Sent: Apr/Mon/2012 08:11
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
>>> condition
>>>
>>> Dear Sir,
>>>
>>>The attachment is file with more data in it [ only ONE work SHeet ,
>>> say
>>> sheet1 ]
>>>   1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL,
>>> DEL..
>>> ]
>>>   2. the col.B contains item-code say item-1, item-2, item-3...[ for
>>> that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
>>> 3. Colm C contains just a number showing how many of these exists say
>>> 1,2,
>>> 600 etc..
>>>
>>> now the output after running thr macro should be containining a subfolder
>>> say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [
>>> if
>>> quantity exists for the perticular city/item-codeXX ]
>>>
>>> =mangal ho
>>>
>>> On 4/9/12, Rajan_Verma  wrote:
 Can you add more data and explain how you want to bifurcate the data?

 Rajan.

 -Original Message-
 From: excel-macros@googlegroups.com
 [mailto:excel-macros@googlegroups.com]
 On Behalf Of Somnath Khadilkar
 Sent: Apr/Mon/2012 07:37
 To: excel-macros@googlegroups.com
 Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
 condition

 Dear Sir,
I reqd the output to be upto say 25 * 10 distict FILES [ sir, not
 diff WorkSheets ] the names of these files will be combination of the
 'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named
 subfolder, so that I can routinely keep track. ]

 thanks in adv.
 PS:  if the file exists --- may be just OVERwrite it...[ if possible
 w/o prompting the user]

 =mangal ho

 On 4/9/12, Somnath Khadilkar  wrote:
> Dear Sir,
> MY request is to get the data into MULTIFLE FILES depending upon
> FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
> etc.. [ these will be unique combination] in a subdirectory  named
> say XXYY, which will cotain ALL colms for the selected filters on
> col. A & B so a unique list of cities say 10, and uniq list of items
> say 25 could generate upto 250 files.
>
> Pl help, attachment enclosed.
>
> =mangal ho
>
> --
> 

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

Here is what I finally did with your example.
I did have problems with date and time columns getting rounded so I wanted to
just round the data columns.  I first did a resize but just thought
that looked clumsy though maybe it
was efficient.  Then I did a test if the column index was any of the 5
correct indexes round the value.
I settled on this below where I defined a range spanning the
contiguous column set as a range
(will need to change of course if a column ever inserted in the range
but that is unlikely in the extreme) .
Then rounded each cell in the range.  Is that really more efficient
than the original loop code shown
below that is all commented out?

'   For rounding off the values define a range just on the numeric columns
Set tmprange = Sheets(RawData).Range(Cells(2, BarOpen),
Cells(stoprawdata, StopCol))
For Each onecell In tmprange
onecell.Value = WorksheetFunction.Round(onecell, 2)
Next onecell

'   Round off the raw data values
'With Sheets(RawData)'original version just for reference
'For index = startrawdata To stoprawdata
'Cells(index, BarOpen).Value =
WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
'Cells(index, BarHigh).Value =
WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
'Cells(index, BarLow).Value =
WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
'Cells(index, BarClose).Value =
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
'Cells(index, StopCol).Value =
WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
'Next index
'End With

On Mon, Apr 9, 2012 at 12:02 PM, dguillett1  wrote:
> You should ALWAYS post your final result for the benefit of all.
>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 1:42 PM
> To: excel-macros@googlegroups.com
>
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> No need.  I figured out my last question and your method works fine, thanks.
>
> On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:
>>
>> Send your file to ME with an explanation.
>>
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 11:50 AM
>>
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> That link fails.  What you said below I think gives a range that
>> includes columns that have numbers I would not want rounded.
>> I can see how it would work I think to eliminate text columns for
>> example, but not numeric columns where rounding is wrong.
>> So still back to the best way to create a used range on each column?
>>
>> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>>>
>>>
>>>
>>> read this
>>> http://support.microsoft.com/kb/194983f
>>>
>>> simple change. Keep the special cells just in case or try without
>>> For Each c In activesheet.usedrange
>>> but blanks will now be 0 instead of blank
>>>
>>>
>>> Sub roundvalues()
>>> Dim c As Range
>>> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>>>
>>> c.Value = Application.Round(c, 2)
>>>
>>> ‘OR vba round which may round down instead of rounding OFF
>>> ‘ c = Round(c, 2)
>>>
>>> Next c
>>> End Sub
>>>
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>> -Original Message- From: Domain Admin
>>> Sent: Monday, April 09, 2012 11:21 AM
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>>
>>> All  my values are numbers so can I eliminate the specialcells part?
>>> To define the ranges can I use
>>> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>>>  using the real name for each column and then use columnA as the
>>> range?
>>> But that would I think give me the entire column and I only need to
>>> check the used range
>>> The round I used is giving the correct round up at .005 so it is not
>>> the VBA round I guess though I am not sure I see the distinction in
>>> your example other
>>> than the removal of application.
>>>
>>> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:



 Sub roundvalues()
 Dim c As Range
 For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
 c.Value = Application.Round(c, 2)

 ‘OR vba round which may round down instead of rounding OFF
 ‘ c = Round(c, 2)

 Next c
 End Sub


 Don Guillett
 Microsoft MVP Excel
 SalesAid Software
 dguille...@gmail.com

 From: tangledweb
 Sent: Sunday, April 08, 2012 5:42 PM
 To: excel-macros@googlegroups.com
 Subject: $$Excel-Macros$$ Could this loop be replaced by some range
 method?

Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Domain Admin]

I partially get this but not I think totally.  One particular thing thoug...
if not returning a value, why go with functions over subs?

On Mon, Apr 9, 2012 at 2:27 PM, Asa Rossoff  wrote:
>> YES.  You got it.
> I've probably said it before, but I could add that your progam could most
> likely be organized in such a way that you don't need any globals.
>
> I think this example I just typed up for you might be unneccessarily complex
> to make the point, but see if it makes sense.  You can share variables
> without using globals.  Note that since this first Sub,
> MakeAWidgetAndPlayWithIt(), is orchestrating things, and it's the entry
> point (the point where code execution will begin), it has local variables
> that hold all the "globally" significant values.  It passes them to other
> procedures as needed.  However, note that since these variables are local to
> MakeAWidgetAndPlayWithItIfFunny(), you could actually have some concurrency
> in a sense.  Either MakeAWidgetAndPlayWithItIfFunny(), one of it's "child"
> procedures, or an event macro that executes automatically, could call
> MakeAWidgetAndPlayWithItIfFunny() again.  Both instances of
> MakeAWidgetAndPlayWithItIfFunny() would have their own separate versions of
> all their variables and objects.  They could be different and changes to
> them won't effect the other instance of MakeAWidgetAndPlayWithItIfFunny().
> Even if that possibility is of no value in your current project, coding this
> way allows for distinct instances of procedures, objects, etc., which adds
> flexibility to you once you realize how to harness it.  It is often
> essential in Excel where event-driven programming is often needed, and
> multiple copies of the same event macro or child procedures modifying the
> same variables would cause havoc.
>
> Sub MakeAWidgetAndPlayWithIt()
>    Dim part1 as widgetpart, part2 as widgetpart, newpart as widgetpart
>    dim widget as widgetobject
>    set part1=.
>    set part2=.
>    set newpart=BestFeaturesOfBoth(part1, part2)
>    set widget=MakeWidget(newpart)
>    If widget.funny then widget.playwith
>    ModifyWidgetWithASub widget
>    If widget.funny then
>        widget.break
>    else
>        set widget.features=BestFeaturesOfBoth(widget.features, newpart)
>        do
>            widget.playwith
>        loop until widget.wornout
>    endif
> End Sub
>
> Function BestFeaturesOfBoth(thing1 as widgetpart, thing2 as widgetpart) As
> widgetpart
>    Set BestFeaturesOfBoth = New widgetpart
>    With BestFeaturesOfBoth
>        .feature1 = worksheetfunction.max(thing1.feature1, thing2.feature1)
>        .feature2 = worksheetfunction.max(thing1.feature2, thing2.feature2)
>    End With
> End Function
>
> Function MakeWidget(widgetpart as widgetpart) As widget
>    set MakeWidget = new widget
>    With MakeWidget
>        .sjfdsjd = "dfsgdsfg"
>        Set .Features = widgetpart
>    End with
> End Function
>
> ' parameters/arguments in VBA default to ByRef - see SUB/FUNCTION in Help
> ' widget here is actually the calling procedure's variable
> Sub ModifyWidgetWithASub(widget as widget)
>    With widget.features
>        .feature1 = .feature1 / 2
>        .feature2 = .feature2 * 2
>    End With
> End Sub
>
>
> Asa
>
> -Original Message-
> From: Asa Rossoff [mailto:a...@lovetour.info]
> Sent: Monday, April 09, 2012 1:19 PM
> To: 'excel-macros@googlegroups.com'
> Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
> spreadsheet?
>
>>> Ok but if I  use Set BarDate = ... where BarDate is a range
>>> object and I do this inside a procedure, but BarDate is defined
>>> at the modulelevel, then is BarDate the range available to
>>> other procedures?
>> If this is the answer to my last question
>> Oh what a tangled web we weave
>>
> http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declare
> d-globally-in-excel-vba
>
> Althhough the discussion at that link is reasonable.. and yes, ther are
> different ways of doing things.. no, it does not exactly answer your
> question.
> The answer to your question is much shorter:
> YES.  You got it.
> What scope of other procedures depends on whether you use Public or
> Private/Dim to declare the range obejct at the module level.  Private/Dim
> and the value will be accessible from any procedure in the module, Public
> from at least any procedure in the project (the workbook).
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 09, 2012 1:03 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
> spreadsheet?
>
> If this is the answer to my last question
> Oh what a tangled web we weave
> http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declare
> d-globally-in-excel-vba
>
> Another thought though... if I put all the Set range assignments in a
> procedure that is call

RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Asa Rossoff]

> YES.  You got it.
I've probably said it before, but I could add that your progam could most
likely be organized in such a way that you don't need any globals.

I think this example I just typed up for you might be unneccessarily complex
to make the point, but see if it makes sense.  You can share variables
without using globals.  Note that since this first Sub,
MakeAWidgetAndPlayWithIt(), is orchestrating things, and it's the entry
point (the point where code execution will begin), it has local variables
that hold all the "globally" significant values.  It passes them to other
procedures as needed.  However, note that since these variables are local to
MakeAWidgetAndPlayWithItIfFunny(), you could actually have some concurrency
in a sense.  Either MakeAWidgetAndPlayWithItIfFunny(), one of it's "child"
procedures, or an event macro that executes automatically, could call
MakeAWidgetAndPlayWithItIfFunny() again.  Both instances of
MakeAWidgetAndPlayWithItIfFunny() would have their own separate versions of
all their variables and objects.  They could be different and changes to
them won't effect the other instance of MakeAWidgetAndPlayWithItIfFunny().
Even if that possibility is of no value in your current project, coding this
way allows for distinct instances of procedures, objects, etc., which adds
flexibility to you once you realize how to harness it.  It is often
essential in Excel where event-driven programming is often needed, and
multiple copies of the same event macro or child procedures modifying the
same variables would cause havoc.

Sub MakeAWidgetAndPlayWithIt()
Dim part1 as widgetpart, part2 as widgetpart, newpart as widgetpart
dim widget as widgetobject
set part1=.
set part2=.
set newpart=BestFeaturesOfBoth(part1, part2)
set widget=MakeWidget(newpart)
If widget.funny then widget.playwith
ModifyWidgetWithASub widget
If widget.funny then
widget.break
else
set widget.features=BestFeaturesOfBoth(widget.features, newpart)
do
widget.playwith
loop until widget.wornout
endif
End Sub

Function BestFeaturesOfBoth(thing1 as widgetpart, thing2 as widgetpart) As
widgetpart
Set BestFeaturesOfBoth = New widgetpart
With BestFeaturesOfBoth
.feature1 = worksheetfunction.max(thing1.feature1, thing2.feature1)
.feature2 = worksheetfunction.max(thing1.feature2, thing2.feature2)
End With
End Function

Function MakeWidget(widgetpart as widgetpart) As widget
set MakeWidget = new widget
With MakeWidget
.sjfdsjd = "dfsgdsfg"
Set .Features = widgetpart
End with
End Function

' parameters/arguments in VBA default to ByRef - see SUB/FUNCTION in Help
' widget here is actually the calling procedure's variable
Sub ModifyWidgetWithASub(widget as widget)
With widget.features
.feature1 = .feature1 / 2
.feature2 = .feature2 * 2
End With
End Sub


Asa

-Original Message-
From: Asa Rossoff [mailto:a...@lovetour.info] 
Sent: Monday, April 09, 2012 1:19 PM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

>> Ok but if I  use Set BarDate = ... where BarDate is a range
>> object and I do this inside a procedure, but BarDate is defined
>> at the modulelevel, then is BarDate the range available to
>> other procedures?
> If this is the answer to my last question
> Oh what a tangled web we weave
>
http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declare
d-globally-in-excel-vba

Althhough the discussion at that link is reasonable.. and yes, ther are
different ways of doing things.. no, it does not exactly answer your
question.
The answer to your question is much shorter:
YES.  You got it.
What scope of other procedures depends on whether you use Public or
Private/Dim to declare the range obejct at the module level.  Private/Dim
and the value will be accessible from any procedure in the module, Public
from at least any procedure in the project (the workbook).

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Monday, April 09, 2012 1:03 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

If this is the answer to my last question
Oh what a tangled web we weave
http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declare
d-globally-in-excel-vba

Another thought though... if I put all the Set range assignments in a
procedure that is called at the start of other procedures, does the
calling procedure inherit the assignments?

On Mon, Apr 9, 2012 at 11:17 AM, Domain Admin  wrote:
> Ok but if I  use Set BarDate = ... where BarDate is a range object
> and I do this inside a procedure, but BarDate is defined at the module
> level, then is BarDate the range available to other procedures?
>
> On Mon, Apr 9,

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

Got it, missed that meant literal posting of an attached file.

On Mon, Apr 9, 2012 at 1:42 PM, Asa Rossoff  wrote:
> Haha :) yes, we never know for sure how much we know.
>
> "I think the only times I did not send the file..."
> If you have ever sent your file, I missed it.  You posted your code, which
> helped, but there is a lot of context missing.  You can post file
> attachments to this group, up to 100KB.  If your file is larger, you can
> either wittle it down in size (and run the risk of changing the behavior so
> we can't see what you are seeing) or post your file on a file sharing site
> such as Box.com (populare here), Google Docs (use the option to NOT convert
> your file to Docs format), or Windows Live SkyDrive.
>
> With your actuual file, we can see exactly what you see, and identify issues
> hard to describe.  It also saves us the trouble of creating a workbook
> ourselves and trying to set it up the same way and generate test data if we
> want to test your code.
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 09, 2012 1:17 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> I saw this context as different so did not seem like exactly the same
> question as before.
> But you are right I still was not getting it and that is why.  I think
> this time it sunk in
> with your specific about worksheet.worksheetfunction.  Time will
> probably prove me
> wrong though :)
>
> I think the only times I did not send the file was when I already had
> a solution but
> will try to be more proactive about that.  Nothing proprietary to
> worry about but the length
> can be a problem at times.
>
> On Mon, Apr 9, 2012 at 1:09 PM, Asa Rossoff  wrote:
>> Hi -
>>
>> Actually.. I think you asked this question previously.   From your
>> description, I'm still not sure you understand.
>>
>> It all comes down to the object model.  WorksheetFunction is not a
> property
>> of Worksheet.  WORKSHEET.WorksheetFunction is never valid.
>> WorksheetFunction is only a property of Application, nothing else.
>>
>> There is no need for it to be a property of a worksheet.  If you are
> working
>> with a worksheet, the parameters to the function will include a Range.
> Range
>> objects specify the location of the Range to Excel, worksheet included.
>>
>>
>> P.S.  Don has asked a few times for a sample file, and I will admit that
> it
>> would make it easier to help you with your project.  Anything you can do
> to
>> make things easier on us would be appreciated.  If needed, change
>> names/numbers in your data to not include anything confidential.
>>
>> Asa
>>
>> -Original Message-
>> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Domain Admin
>> Sent: Monday, April 09, 2012 12:16 PM
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> Sorry, no intent to break protocol.  I just figured out why
>> I could not replace application in your solution with
>> sheets(rawdata).worksheetfunction.
>> I was already associated with the sheet inside the loop.  So I could
>> replace application with
>> just worksheetfunction but not sheets(rawdata).worksheetfunction
>>
>> I did not change your solution so did not think a post was in order.
>> I stand corrected.
>>
>> On Mon, Apr 9, 2012 at 12:02 PM, dguillett1  wrote:
>>> You should ALWAYS post your final result for the benefit of all.
>>>
>>>
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>> -Original Message- From: Domain Admin
>>> Sent: Monday, April 09, 2012 1:42 PM
>>> To: excel-macros@googlegroups.com
>>>
>>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>> No need.  I figured out my last question and your method works fine,
>> thanks.
>>>
>>> On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:

 Send your file to ME with an explanation.




 Don Guillett
 Microsoft MVP Excel
 SalesAid Software
 dguille...@gmail.com
 -Original Message- From: Domain Admin
 Sent: Monday, April 09, 2012 11:50 AM

 To: excel-macros@googlegroups.com
 Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
 method?

 That link fails.  What you said below I think gives a range that
 includes columns that have numbers I would not want rounded.
 I can see how it would work I think to eliminate text columns for
 example, but not numeric columns where rounding is wrong.
 So still back to the best way to create a used range on each column?

 On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>
>
>
> read this
> http://support.microsoft.com/kb/194983f
>
> simpl

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Asa Rossoff]

Haha :) yes, we never know for sure how much we know.

"I think the only times I did not send the file..."
If you have ever sent your file, I missed it.  You posted your code, which
helped, but there is a lot of context missing.  You can post file
attachments to this group, up to 100KB.  If your file is larger, you can
either wittle it down in size (and run the risk of changing the behavior so
we can't see what you are seeing) or post your file on a file sharing site
such as Box.com (populare here), Google Docs (use the option to NOT convert
your file to Docs format), or Windows Live SkyDrive.

With your actuual file, we can see exactly what you see, and identify issues
hard to describe.  It also saves us the trouble of creating a workbook
ourselves and trying to set it up the same way and generate test data if we
want to test your code.
Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Monday, April 09, 2012 1:17 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

I saw this context as different so did not seem like exactly the same
question as before.
But you are right I still was not getting it and that is why.  I think
this time it sunk in
with your specific about worksheet.worksheetfunction.  Time will
probably prove me
wrong though :)

I think the only times I did not send the file was when I already had
a solution but
will try to be more proactive about that.  Nothing proprietary to
worry about but the length
can be a problem at times.

On Mon, Apr 9, 2012 at 1:09 PM, Asa Rossoff  wrote:
> Hi -
>
> Actually.. I think you asked this question previously.   From your
> description, I'm still not sure you understand.
>
> It all comes down to the object model.  WorksheetFunction is not a
property
> of Worksheet.  WORKSHEET.WorksheetFunction is never valid.
> WorksheetFunction is only a property of Application, nothing else.
>
> There is no need for it to be a property of a worksheet.  If you are
working
> with a worksheet, the parameters to the function will include a Range.
Range
> objects specify the location of the Range to Excel, worksheet included.
>
>
> P.S.  Don has asked a few times for a sample file, and I will admit that
it
> would make it easier to help you with your project.  Anything you can do
to
> make things easier on us would be appreciated.  If needed, change
> names/numbers in your data to not include anything confidential.
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 09, 2012 12:16 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> Sorry, no intent to break protocol.  I just figured out why
> I could not replace application in your solution with
> sheets(rawdata).worksheetfunction.
> I was already associated with the sheet inside the loop.  So I could
> replace application with
> just worksheetfunction but not sheets(rawdata).worksheetfunction
>
> I did not change your solution so did not think a post was in order.
> I stand corrected.
>
> On Mon, Apr 9, 2012 at 12:02 PM, dguillett1  wrote:
>> You should ALWAYS post your final result for the benefit of all.
>>
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 1:42 PM
>> To: excel-macros@googlegroups.com
>>
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> No need.  I figured out my last question and your method works fine,
> thanks.
>>
>> On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:
>>>
>>> Send your file to ME with an explanation.
>>>
>>>
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>> -Original Message- From: Domain Admin
>>> Sent: Monday, April 09, 2012 11:50 AM
>>>
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>> That link fails.  What you said below I think gives a range that
>>> includes columns that have numbers I would not want rounded.
>>> I can see how it would work I think to eliminate text columns for
>>> example, but not numeric columns where rounding is wrong.
>>> So still back to the best way to create a used range on each column?
>>>
>>> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:



 read this
 http://support.microsoft.com/kb/194983f

 simple change. Keep the special cells just in case or try without
 For Each c In activesheet.usedrange
 but blanks will now be 0 instead of blank


 Sub roundvalues()
 Dim c As Range
 For Each c In activesheet.usedrange.SpecialCells(xlConstants,
xlNumbers)

 c

RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Asa Rossoff]

>> Ok but if I  use Set BarDate = ... where BarDate is a range
>> object and I do this inside a procedure, but BarDate is defined
>> at the modulelevel, then is BarDate the range available to
>> other procedures?
> If this is the answer to my last question
> Oh what a tangled web we weave
>
http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declare
d-globally-in-excel-vba

Althhough the discussion at that link is reasonable.. and yes, ther are
different ways of doing things.. no, it does not exactly answer your
question.
The answer to your question is much shorter:
YES.  You got it.
What scope of other procedures depends on whether you use Public or
Private/Dim to declare the range obejct at the module level.  Private/Dim
and the value will be accessible from any procedure in the module, Public
from at least any procedure in the project (the workbook).

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Monday, April 09, 2012 1:03 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

If this is the answer to my last question
Oh what a tangled web we weave
http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declare
d-globally-in-excel-vba

Another thought though... if I put all the Set range assignments in a
procedure that is called at the start of other procedures, does the
calling procedure inherit the assignments?

On Mon, Apr 9, 2012 at 11:17 AM, Domain Admin  wrote:
> Ok but if I  use Set BarDate = ... where BarDate is a range object
> and I do this inside a procedure, but BarDate is defined at the module
> level, then is BarDate the range available to other procedures?
>
> On Mon, Apr 9, 2012 at 2:35 AM, Asa Rossoff  wrote:
>>> This works.  Is there a reason why it is not better?
>> Yes.  It won't always work.  RANGE.Columns is relative to that range (as
is
>> RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet.  UsedRange
>> doesn't always start in A1.
>>
>>> This is puzzling.  In the case of .rows(1) it can handle the range as
>>> the array but in this case you have to explicitly convert to an array.
>> Perhaps because in Excel 2007+ there are 16,384 columns, much less than
the
>> million+ rows,
>> Your syntax might well work in prior versions of Excel, where there are
just
>> 65,536 rows.
>>
>>> And then further down to this though not as easy to read and understand
>>>
>>>     contangoindex = _
>>> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
>>> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
>>>
>>> So what is happening here?  Is this still just by adding the .value
>>> converting the range into an array as some secret VBA thing where you
>>> are extracting the values out of the range and they have to go
>>> somewhere to it creates and array for them?
>> Once you specify the .Value property of a range larger than one cell, you
>> are specifying an array.  It doesn't matter if you don't create a
variable
>> to refer to it.  If you read the Help entry for WorksheetFunction.Match
>> you'll see that the data type for all the arguments is Variant.  Your
array
>> will be stored in memory as an array of Variant (the .Value property is
>> Variant) and stored inside another Variant (full name "Variant Array of
>> Variant"), and a pointer to it will be passed to WorksheetFunction.Match.
>> When Match is done with it's work, the array will be destroyed from
memory
>> since there are no other pointers to it.  If you had a need to refer to
it
>> again, it would be a very good idea to create the variable first and not
>> have VBA waste energy creating and destroying the whole array multiple
>> times.
>>
>>> You are right though.  Once you find a working bone you can keep
>>> gnawing on it until down to minimal shard that still functions.
>> It can help in understanding what's what.
>>
>>> When you use SET to create pointers they can only be done inside a
>>> Sub.  If you want to use them in another SUB do you have to do the SET
>>> again or if in the same module will the SET be know by other Subs?
>> It's not the Set statement that matters for your question.  All
variables,
>> as well as procedures (be they Sub or Function procedures), and modules,
>> have a certain scope within which they can be referred to.  What
determines
>> the scope, in the case of a variable, is where it is declared, what
>> statement was used to declare it, and whether it is passed ByRef to any
>> other Sub or Function (although in the last case the other procedure
would
>> have it's own local name for the variable, even though it points to the
same
>> memory location).
>>
>> Variables can be declared at the module level as:
>>  Public varname As vartype ' uses global or optionally workbook scope
>>  Private varname As vartype ' uses module scope
>>  Dim varname As vartype ' uses module scope
>>
>> In a

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

I saw this context as different so did not seem like exactly the same
question as before.
But you are right I still was not getting it and that is why.  I think
this time it sunk in
with your specific about worksheet.worksheetfunction.  Time will
probably prove me
wrong though :)

I think the only times I did not send the file was when I already had
a solution but
will try to be more proactive about that.  Nothing proprietary to
worry about but the length
can be a problem at times.

On Mon, Apr 9, 2012 at 1:09 PM, Asa Rossoff  wrote:
> Hi -
>
> Actually.. I think you asked this question previously.   From your
> description, I'm still not sure you understand.
>
> It all comes down to the object model.  WorksheetFunction is not a property
> of Worksheet.  WORKSHEET.WorksheetFunction is never valid.
> WorksheetFunction is only a property of Application, nothing else.
>
> There is no need for it to be a property of a worksheet.  If you are working
> with a worksheet, the parameters to the function will include a Range. Range
> objects specify the location of the Range to Excel, worksheet included.
>
>
> P.S.  Don has asked a few times for a sample file, and I will admit that it
> would make it easier to help you with your project.  Anything you can do to
> make things easier on us would be appreciated.  If needed, change
> names/numbers in your data to not include anything confidential.
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 09, 2012 12:16 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> Sorry, no intent to break protocol.  I just figured out why
> I could not replace application in your solution with
> sheets(rawdata).worksheetfunction.
> I was already associated with the sheet inside the loop.  So I could
> replace application with
> just worksheetfunction but not sheets(rawdata).worksheetfunction
>
> I did not change your solution so did not think a post was in order.
> I stand corrected.
>
> On Mon, Apr 9, 2012 at 12:02 PM, dguillett1  wrote:
>> You should ALWAYS post your final result for the benefit of all.
>>
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 1:42 PM
>> To: excel-macros@googlegroups.com
>>
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> No need.  I figured out my last question and your method works fine,
> thanks.
>>
>> On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:
>>>
>>> Send your file to ME with an explanation.
>>>
>>>
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>> -Original Message- From: Domain Admin
>>> Sent: Monday, April 09, 2012 11:50 AM
>>>
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>> That link fails.  What you said below I think gives a range that
>>> includes columns that have numbers I would not want rounded.
>>> I can see how it would work I think to eliminate text columns for
>>> example, but not numeric columns where rounding is wrong.
>>> So still back to the best way to create a used range on each column?
>>>
>>> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:



 read this
 http://support.microsoft.com/kb/194983f

 simple change. Keep the special cells just in case or try without
 For Each c In activesheet.usedrange
 but blanks will now be 0 instead of blank


 Sub roundvalues()
 Dim c As Range
 For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)

 c.Value = Application.Round(c, 2)

 ‘OR vba round which may round down instead of rounding OFF
 ‘ c = Round(c, 2)

 Next c
 End Sub



 Don Guillett
 Microsoft MVP Excel
 SalesAid Software
 dguille...@gmail.com
 -Original Message- From: Domain Admin
 Sent: Monday, April 09, 2012 11:21 AM
 To: excel-macros@googlegroups.com
 Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
 method?


 All  my values are numbers so can I eliminate the specialcells part?
 To define the ranges can I use
 set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
  using the real name for each column and then use columnA as the
 range?
 But that would I think give me the entire column and I only need to
 check the used range
 The round I used is giving the correct round up at .005 so it is not
 the VBA round I guess though I am not sure I see the distinction in
 your example other
 than the removal of application.

 On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Asa Rossoff]

Hi -

Actually.. I think you asked this question previously.   From your
description, I'm still not sure you understand.

It all comes down to the object model.  WorksheetFunction is not a property
of Worksheet.  WORKSHEET.WorksheetFunction is never valid.
WorksheetFunction is only a property of Application, nothing else.

There is no need for it to be a property of a worksheet.  If you are working
with a worksheet, the parameters to the function will include a Range. Range
objects specify the location of the Range to Excel, worksheet included.


P.S.  Don has asked a few times for a sample file, and I will admit that it
would make it easier to help you with your project.  Anything you can do to
make things easier on us would be appreciated.  If needed, change
names/numbers in your data to not include anything confidential.

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Monday, April 09, 2012 12:16 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

Sorry, no intent to break protocol.  I just figured out why
I could not replace application in your solution with
sheets(rawdata).worksheetfunction.
I was already associated with the sheet inside the loop.  So I could
replace application with
just worksheetfunction but not sheets(rawdata).worksheetfunction

I did not change your solution so did not think a post was in order.
I stand corrected.

On Mon, Apr 9, 2012 at 12:02 PM, dguillett1  wrote:
> You should ALWAYS post your final result for the benefit of all.
>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 1:42 PM
> To: excel-macros@googlegroups.com
>
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> No need.  I figured out my last question and your method works fine,
thanks.
>
> On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:
>>
>> Send your file to ME with an explanation.
>>
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 11:50 AM
>>
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> That link fails.  What you said below I think gives a range that
>> includes columns that have numbers I would not want rounded.
>> I can see how it would work I think to eliminate text columns for
>> example, but not numeric columns where rounding is wrong.
>> So still back to the best way to create a used range on each column?
>>
>> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>>>
>>>
>>>
>>> read this
>>> http://support.microsoft.com/kb/194983f
>>>
>>> simple change. Keep the special cells just in case or try without
>>> For Each c In activesheet.usedrange
>>> but blanks will now be 0 instead of blank
>>>
>>>
>>> Sub roundvalues()
>>> Dim c As Range
>>> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>>>
>>> c.Value = Application.Round(c, 2)
>>>
>>> ‘OR vba round which may round down instead of rounding OFF
>>> ‘ c = Round(c, 2)
>>>
>>> Next c
>>> End Sub
>>>
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>> -Original Message- From: Domain Admin
>>> Sent: Monday, April 09, 2012 11:21 AM
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>>
>>> All  my values are numbers so can I eliminate the specialcells part?
>>> To define the ranges can I use
>>> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>>>  using the real name for each column and then use columnA as the
>>> range?
>>> But that would I think give me the entire column and I only need to
>>> check the used range
>>> The round I used is giving the correct round up at .005 so it is not
>>> the VBA round I guess though I am not sure I see the distinction in
>>> your example other
>>> than the removal of application.
>>>
>>> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:



 Sub roundvalues()
 Dim c As Range
 For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
 c.Value = Application.Round(c, 2)

 ‘OR vba round which may round down instead of rounding OFF
 ‘ c = Round(c, 2)

 Next c
 End Sub


 Don Guillett
 Microsoft MVP Excel
 SalesAid Software
 dguille...@gmail.com

 From: tangledweb
 Sent: Sunday, April 08, 2012 5:42 PM
 To: excel-macros@googlegroups.com
 Subject: $$Excel-Macros$$ Could this loop be replaced by some range
 method?

 I am trying to get more used to using ranges instead of addressing
 individual cells by indexes.  The loop 

Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Domain Admin]

If this is the answer to my last question
Oh what a tangled web we weave
http://stackoverflow.com/questions/1176743/can-a-worksheet-object-be-declared-globally-in-excel-vba

Another thought though... if I put all the Set range assignments in a
procedure that is called at the start of other procedures, does the
calling procedure inherit the assignments?

On Mon, Apr 9, 2012 at 11:17 AM, Domain Admin  wrote:
> Ok but if I  use Set BarDate = ... where BarDate is a range object
> and I do this inside a procedure, but BarDate is defined at the module
> level, then is BarDate the range available to other procedures?
>
> On Mon, Apr 9, 2012 at 2:35 AM, Asa Rossoff  wrote:
>>> This works.  Is there a reason why it is not better?
>> Yes.  It won't always work.  RANGE.Columns is relative to that range (as is
>> RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet.  UsedRange
>> doesn't always start in A1.
>>
>>> This is puzzling.  In the case of .rows(1) it can handle the range as
>>> the array but in this case you have to explicitly convert to an array.
>> Perhaps because in Excel 2007+ there are 16,384 columns, much less than the
>> million+ rows,
>> Your syntax might well work in prior versions of Excel, where there are just
>> 65,536 rows.
>>
>>> And then further down to this though not as easy to read and understand
>>>
>>>     contangoindex = _
>>> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
>>> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
>>>
>>> So what is happening here?  Is this still just by adding the .value
>>> converting the range into an array as some secret VBA thing where you
>>> are extracting the values out of the range and they have to go
>>> somewhere to it creates and array for them?
>> Once you specify the .Value property of a range larger than one cell, you
>> are specifying an array.  It doesn't matter if you don't create a variable
>> to refer to it.  If you read the Help entry for WorksheetFunction.Match
>> you'll see that the data type for all the arguments is Variant.  Your array
>> will be stored in memory as an array of Variant (the .Value property is
>> Variant) and stored inside another Variant (full name "Variant Array of
>> Variant"), and a pointer to it will be passed to WorksheetFunction.Match.
>> When Match is done with it's work, the array will be destroyed from memory
>> since there are no other pointers to it.  If you had a need to refer to it
>> again, it would be a very good idea to create the variable first and not
>> have VBA waste energy creating and destroying the whole array multiple
>> times.
>>
>>> You are right though.  Once you find a working bone you can keep
>>> gnawing on it until down to minimal shard that still functions.
>> It can help in understanding what's what.
>>
>>> When you use SET to create pointers they can only be done inside a
>>> Sub.  If you want to use them in another SUB do you have to do the SET
>>> again or if in the same module will the SET be know by other Subs?
>> It's not the Set statement that matters for your question.  All variables,
>> as well as procedures (be they Sub or Function procedures), and modules,
>> have a certain scope within which they can be referred to.  What determines
>> the scope, in the case of a variable, is where it is declared, what
>> statement was used to declare it, and whether it is passed ByRef to any
>> other Sub or Function (although in the last case the other procedure would
>> have it's own local name for the variable, even though it points to the same
>> memory location).
>>
>> Variables can be declared at the module level as:
>>  Public varname As vartype ' uses global or optionally workbook scope
>>  Private varname As vartype ' uses module scope
>>  Dim varname As vartype ' uses module scope
>>
>> In a procedure they can be declared:
>>  Dim varname As vartype ' uses local (procedure) scope
>>  Static varname as vartype ' uses local (procedure) scope and retains value
>> between calls
>>
>> Then use Set if you are assigning an object to a variable, or use Let or the
>> usual shorthand of varname=value if you are assigning any other data type.
>>
>> Asa
>>
>> -Original Message-
>> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Domain Admin
>> Sent: Sunday, April 08, 2012 1:36 AM
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
>> spreadsheet?
>>
>> This works.  Is there a reason why it is not better?
>>
>> I did your array replacement but I changed this
>>
>> Set InputRange =
>> Application.Intersect(Sheets(ContangoSource).UsedRange,
>> Sheets(ContangoSource).Columns(ConDate))
>>
>> with this
>>
>> Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)
>>
>> but it does not work to get rid of the array replacement and go all
>> the way to this
>>
>> contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2,
>> BarDate).Value

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

Sorry, no intent to break protocol.  I just figured out why
I could not replace application in your solution with
sheets(rawdata).worksheetfunction.
I was already associated with the sheet inside the loop.  So I could
replace application with
just worksheetfunction but not sheets(rawdata).worksheetfunction

I did not change your solution so did not think a post was in order.
I stand corrected.

On Mon, Apr 9, 2012 at 12:02 PM, dguillett1  wrote:
> You should ALWAYS post your final result for the benefit of all.
>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 1:42 PM
> To: excel-macros@googlegroups.com
>
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> No need.  I figured out my last question and your method works fine, thanks.
>
> On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:
>>
>> Send your file to ME with an explanation.
>>
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 11:50 AM
>>
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> That link fails.  What you said below I think gives a range that
>> includes columns that have numbers I would not want rounded.
>> I can see how it would work I think to eliminate text columns for
>> example, but not numeric columns where rounding is wrong.
>> So still back to the best way to create a used range on each column?
>>
>> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>>>
>>>
>>>
>>> read this
>>> http://support.microsoft.com/kb/194983f
>>>
>>> simple change. Keep the special cells just in case or try without
>>> For Each c In activesheet.usedrange
>>> but blanks will now be 0 instead of blank
>>>
>>>
>>> Sub roundvalues()
>>> Dim c As Range
>>> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>>>
>>> c.Value = Application.Round(c, 2)
>>>
>>> ‘OR vba round which may round down instead of rounding OFF
>>> ‘ c = Round(c, 2)
>>>
>>> Next c
>>> End Sub
>>>
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>> -Original Message- From: Domain Admin
>>> Sent: Monday, April 09, 2012 11:21 AM
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>>
>>> All  my values are numbers so can I eliminate the specialcells part?
>>> To define the ranges can I use
>>> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>>>  using the real name for each column and then use columnA as the
>>> range?
>>> But that would I think give me the entire column and I only need to
>>> check the used range
>>> The round I used is giving the correct round up at .005 so it is not
>>> the VBA round I guess though I am not sure I see the distinction in
>>> your example other
>>> than the removal of application.
>>>
>>> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:



 Sub roundvalues()
 Dim c As Range
 For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
 c.Value = Application.Round(c, 2)

 ‘OR vba round which may round down instead of rounding OFF
 ‘ c = Round(c, 2)

 Next c
 End Sub


 Don Guillett
 Microsoft MVP Excel
 SalesAid Software
 dguille...@gmail.com

 From: tangledweb
 Sent: Sunday, April 08, 2012 5:42 PM
 To: excel-macros@googlegroups.com
 Subject: $$Excel-Macros$$ Could this loop be replaced by some range
 method?

 I am trying to get more used to using ranges instead of addressing
 individual cells by indexes.  The loop below is rounding off the entries
 in
 all the columns
 where BarOpen for example is just the column index in the sheet.  Is
 there
 a
 way to do this more efficiently by using the used range portion of each
 column?
 In this case all the columns are the same length.


 '   Round off the raw data values
  With Sheets(RawData)
      For index = startrawdata To stoprawdata
          Cells(index, BarOpen).Value =
 WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
          Cells(index, BarHigh).Value =
 WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
          Cells(index, BarLow).Value =
 WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
          Cells(index, BarClose).Value =
 WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
          Cells(index, StopCol).Value =
 WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
      Next index
  End With
 --
 FORUM RULES (986+ members already BANNED for violation)

 1) Use concise, accurate thread titles. Poor thread 

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[dguillett1]

You should ALWAYS post your final result for the benefit of all.



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Domain Admin

Sent: Monday, April 09, 2012 1:42 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range 
method?


No need.  I figured out my last question and your method works fine, thanks.

On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:

Send your file to ME with an explanation.




Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- From: Domain Admin
Sent: Monday, April 09, 2012 11:50 AM

To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

That link fails.  What you said below I think gives a range that
includes columns that have numbers I would not want rounded.
I can see how it would work I think to eliminate text columns for
example, but not numeric columns where rounding is wrong.
So still back to the best way to create a used range on each column?

On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:



read this
http://support.microsoft.com/kb/194983f

simple change. Keep the special cells just in case or try without
For Each c In activesheet.usedrange
but blanks will now be 0 instead of blank


Sub roundvalues()
Dim c As Range
For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)

c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- From: Domain Admin
Sent: Monday, April 09, 2012 11:21 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?


All  my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
 using the real name for each column and then use columnA as the
range?
But that would I think give me the entire column and I only need to
check the used range
The round I used is giving the correct round up at .005 so it is not
the VBA round I guess though I am not sure I see the distinction in
your example other
than the removal of application.

On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:



Sub roundvalues()
Dim c As Range
For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub


Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: tangledweb
Sent: Sunday, April 08, 2012 5:42 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Could this loop be replaced by some range
method?

I am trying to get more used to using ranges instead of addressing
individual cells by indexes.  The loop below is rounding off the entries
in
all the columns
where BarOpen for example is just the column index in the sheet.  Is
there
a
way to do this more efficiently by using the used range portion of each
column?
In this case all the columns are the same length.


'   Round off the raw data values
  With Sheets(RawData)
  For index = startrawdata To stoprawdata
  Cells(index, BarOpen).Value =
WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
  Cells(index, BarHigh).Value =
WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
  Cells(index, BarLow).Value =
WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
  Cells(index, BarClose).Value =
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
  Cells(index, StopCol).Value =
WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
  Next index
  End With
--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. 
Forum

owners and members are not responsible for any loss.



--
To post to this group, send email to excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not 

Re: $$Excel-Macros$$ SPLIT into many FILES , depeding UPON...

[dguillett1]

A looping macro can be developed to filter the data and send it to each. 
Properly done you need only ONE mouse click. Or, if it takes awhile to run, 
set to run after you go home for the day.




Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Somnath Khadilkar

Sent: Monday, April 09, 2012 11:58 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ SPLIT into many FILES , depeding UPON...

Dear Sir,
   The foremost reason is I need to EMAIL this file  to the
Respective CityCircle, & they require ONLY there own CITY info. The
separate files so created will be saving lot of our SPACE .Currently
anyway I am doing it MANUALLY [ rathersorry state!! ] Print out of
the repo, we do at our HdOfice. and w/o a macro it takes too much of
time...[ over five hundred LEFT clicks alone!! ]

=mangal ho

On 4/9/12, dguillett1  wrote:

I haven't followed all of this but it seems to me that there is no reason
for separate files. To get reports or print you can use filtering
with/without macros and use only ONE file.  Good design???



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message-
From: Somnath Khadilkar
Sent: Monday, April 09, 2012 9:58 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Sir,
   there are many many colm.in the sheets  [ that mighht complex the
issue ] , but as a sample I am able to limit to the TWO fields and one
QTY field. I need all the colm A thru C here in the RESPECTIVE files.
---

issue 2.
   I am able to generate a  datafile [ thru some 'C' code , can you
guide me to 'CONVERT' these into XLS files thru some tool, [ w/o
openining/saving individuay, as these are TOO many, I need to
AUTOMAIL ] THIS is altogether a diff. issue, but any help will be
highly appreciated ]

=Mangal Ho

On 4/9/12, Rajan_Verma  wrote:

So , what the excel file will conatin? Only one Row?


-Original Message-
From: excel-macros@googlegroups.com 
[mailto:excel-macros@googlegroups.com]

On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 08:11
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Dear Sir,

   The attachment is file with more data in it [ only ONE work SHeet , 
say

sheet1 ]
  1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL,
DEL..
]
  2. the col.B contains item-code say item-1, item-2, item-3...[ for
that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
3. Colm C contains just a number showing how many of these exists say 
1,2,

600 etc..

now the output after running thr macro should be containining a subfolder
say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [
if
quantity exists for the perticular city/item-codeXX ]

=mangal ho

On 4/9/12, Rajan_Verma  wrote:

Can you add more data and explain how you want to bifurcate the data?

Rajan.

-Original Message-
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com]
On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 07:37
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Dear Sir,
   I reqd the output to be upto say 25 * 10 distict FILES [ sir, not
diff WorkSheets ] the names of these files will be combination of the
'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named
subfolder, so that I can routinely keep track. ]

thanks in adv.
PS:  if the file exists --- may be just OVERwrite it...[ if possible
w/o prompting the user]

=mangal ho

On 4/9/12, Somnath Khadilkar  wrote:

Dear Sir,
MY request is to get the data into MULTIFLE FILES depending upon
FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
etc.. [ these will be unique combination] in a subdirectory  named
say XXYY, which will cotain ALL colms for the selected filters on
col. A & B so a unique list of cities say 10, and uniq list of items
say 25 could generate upto 250 files.

Pl help, attachment enclosed.

=mangal ho

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum
in signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.

-
-
 To post to this group, send email to
excel-macros@googlegroups.com



-

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

No need.  I figured out my last question and your method works fine, thanks.

On Mon, Apr 9, 2012 at 11:40 AM, dguillett1  wrote:
> Send your file to ME with an explanation.
>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 11:50 AM
>
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> That link fails.  What you said below I think gives a range that
> includes columns that have numbers I would not want rounded.
> I can see how it would work I think to eliminate text columns for
> example, but not numeric columns where rounding is wrong.
> So still back to the best way to create a used range on each column?
>
> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>>
>>
>> read this
>> http://support.microsoft.com/kb/194983f
>>
>> simple change. Keep the special cells just in case or try without
>> For Each c In activesheet.usedrange
>> but blanks will now be 0 instead of blank
>>
>>
>> Sub roundvalues()
>> Dim c As Range
>> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>>
>> c.Value = Application.Round(c, 2)
>>
>> ‘OR vba round which may round down instead of rounding OFF
>> ‘ c = Round(c, 2)
>>
>> Next c
>> End Sub
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 11:21 AM
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>>
>> All  my values are numbers so can I eliminate the specialcells part?
>> To define the ranges can I use
>> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>>  using the real name for each column and then use columnA as the
>> range?
>> But that would I think give me the entire column and I only need to
>> check the used range
>> The round I used is giving the correct round up at .005 so it is not
>> the VBA round I guess though I am not sure I see the distinction in
>> your example other
>> than the removal of application.
>>
>> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
>>>
>>>
>>> Sub roundvalues()
>>> Dim c As Range
>>> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
>>> c.Value = Application.Round(c, 2)
>>>
>>> ‘OR vba round which may round down instead of rounding OFF
>>> ‘ c = Round(c, 2)
>>>
>>> Next c
>>> End Sub
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> From: tangledweb
>>> Sent: Sunday, April 08, 2012 5:42 PM
>>> To: excel-macros@googlegroups.com
>>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>> I am trying to get more used to using ranges instead of addressing
>>> individual cells by indexes.  The loop below is rounding off the entries
>>> in
>>> all the columns
>>> where BarOpen for example is just the column index in the sheet.  Is
>>> there
>>> a
>>> way to do this more efficiently by using the used range portion of each
>>> column?
>>> In this case all the columns are the same length.
>>>
>>>
>>> '   Round off the raw data values
>>>   With Sheets(RawData)
>>>       For index = startrawdata To stoprawdata
>>>           Cells(index, BarOpen).Value =
>>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>>           Cells(index, BarHigh).Value =
>>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>>           Cells(index, BarLow).Value =
>>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>>           Cells(index, BarClose).Value =
>>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>>           Cells(index, StopCol).Value =
>>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>>       Next index
>>>   End With
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will
>>> not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>>> owners and members are not responsible for any loss.
>>>
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titl

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[dguillett1]

Send your file to ME with an explanation.



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Domain Admin

Sent: Monday, April 09, 2012 11:50 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range 
method?


That link fails.  What you said below I think gives a range that
includes columns that have numbers I would not want rounded.
I can see how it would work I think to eliminate text columns for
example, but not numeric columns where rounding is wrong.
So still back to the best way to create a used range on each column?

On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:


read this
http://support.microsoft.com/kb/194983f

simple change. Keep the special cells just in case or try without
For Each c In activesheet.usedrange
but blanks will now be 0 instead of blank


Sub roundvalues()
Dim c As Range
For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)

c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- From: Domain Admin
Sent: Monday, April 09, 2012 11:21 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?


All  my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
 using the real name for each column and then use columnA as the
range?
But that would I think give me the entire column and I only need to
check the used range
The round I used is giving the correct round up at .005 so it is not
the VBA round I guess though I am not sure I see the distinction in
your example other
than the removal of application.

On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:


Sub roundvalues()
Dim c As Range
For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub


Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: tangledweb
Sent: Sunday, April 08, 2012 5:42 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Could this loop be replaced by some range
method?

I am trying to get more used to using ranges instead of addressing
individual cells by indexes.  The loop below is rounding off the entries
in
all the columns
where BarOpen for example is just the column index in the sheet.  Is 
there

a
way to do this more efficiently by using the used range portion of each
column?
In this case all the columns are the same length.


'   Round off the raw data values
   With Sheets(RawData)
   For index = startrawdata To stoprawdata
   Cells(index, BarOpen).Value =
WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
   Cells(index, BarHigh).Value =
WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
   Cells(index, BarLow).Value =
WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
   Cells(index, BarClose).Value =
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
   Cells(index, StopCol).Value =
WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
   Next index
   End With
--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

Never mind, figured it out.

On Mon, Apr 9, 2012 at 11:06 AM, Domain Admin  wrote:
> Ok this works.  But why does it work fine if I replace activesheet
> with sheets(rawdata)
> but I can not replace application with sheets(rawdata).worksheetfunction
>
> which gives error object does not support property or method
>
> On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>>
>> read this
>> http://support.microsoft.com/kb/194983f
>>
>> simple change. Keep the special cells just in case or try without
>> For Each c In activesheet.usedrange
>> but blanks will now be 0 instead of blank
>>
>>
>> Sub roundvalues()
>> Dim c As Range
>> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>>
>> c.Value = Application.Round(c, 2)
>>
>> ‘OR vba round which may round down instead of rounding OFF
>> ‘ c = Round(c, 2)
>>
>> Next c
>> End Sub
>>
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>> -Original Message- From: Domain Admin
>> Sent: Monday, April 09, 2012 11:21 AM
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>>
>> All  my values are numbers so can I eliminate the specialcells part?
>> To define the ranges can I use
>> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>>  using the real name for each column and then use columnA as the
>> range?
>> But that would I think give me the entire column and I only need to
>> check the used range
>> The round I used is giving the correct round up at .005 so it is not
>> the VBA round I guess though I am not sure I see the distinction in
>> your example other
>> than the removal of application.
>>
>> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
>>>
>>> Sub roundvalues()
>>> Dim c As Range
>>> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
>>> c.Value = Application.Round(c, 2)
>>>
>>> ‘OR vba round which may round down instead of rounding OFF
>>> ‘ c = Round(c, 2)
>>>
>>> Next c
>>> End Sub
>>>
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> From: tangledweb
>>> Sent: Sunday, April 08, 2012 5:42 PM
>>> To: excel-macros@googlegroups.com
>>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
>>> method?
>>>
>>> I am trying to get more used to using ranges instead of addressing
>>> individual cells by indexes.  The loop below is rounding off the entries
>>> in
>>> all the columns
>>> where BarOpen for example is just the column index in the sheet.  Is there
>>> a
>>> way to do this more efficiently by using the used range portion of each
>>> column?
>>> In this case all the columns are the same length.
>>>
>>>
>>> '   Round off the raw data values
>>>    With Sheets(RawData)
>>>        For index = startrawdata To stoprawdata
>>>            Cells(index, BarOpen).Value =
>>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>>            Cells(index, BarHigh).Value =
>>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>>            Cells(index, BarLow).Value =
>>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>>            Cells(index, BarClose).Value =
>>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>>            Cells(index, StopCol).Value =
>>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>>        Next index
>>>    End With
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will
>>> not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>>> owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will
>>> not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOT

Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Domain Admin]

Ok but if I  use Set BarDate = ... where BarDate is a range object
and I do this inside a procedure, but BarDate is defined at the module
level, then is BarDate the range available to other procedures?

On Mon, Apr 9, 2012 at 2:35 AM, Asa Rossoff  wrote:
>> This works.  Is there a reason why it is not better?
> Yes.  It won't always work.  RANGE.Columns is relative to that range (as is
> RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet.  UsedRange
> doesn't always start in A1.
>
>> This is puzzling.  In the case of .rows(1) it can handle the range as
>> the array but in this case you have to explicitly convert to an array.
> Perhaps because in Excel 2007+ there are 16,384 columns, much less than the
> million+ rows,
> Your syntax might well work in prior versions of Excel, where there are just
> 65,536 rows.
>
>> And then further down to this though not as easy to read and understand
>>
>>     contangoindex = _
>> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
>> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
>>
>> So what is happening here?  Is this still just by adding the .value
>> converting the range into an array as some secret VBA thing where you
>> are extracting the values out of the range and they have to go
>> somewhere to it creates and array for them?
> Once you specify the .Value property of a range larger than one cell, you
> are specifying an array.  It doesn't matter if you don't create a variable
> to refer to it.  If you read the Help entry for WorksheetFunction.Match
> you'll see that the data type for all the arguments is Variant.  Your array
> will be stored in memory as an array of Variant (the .Value property is
> Variant) and stored inside another Variant (full name "Variant Array of
> Variant"), and a pointer to it will be passed to WorksheetFunction.Match.
> When Match is done with it's work, the array will be destroyed from memory
> since there are no other pointers to it.  If you had a need to refer to it
> again, it would be a very good idea to create the variable first and not
> have VBA waste energy creating and destroying the whole array multiple
> times.
>
>> You are right though.  Once you find a working bone you can keep
>> gnawing on it until down to minimal shard that still functions.
> It can help in understanding what's what.
>
>> When you use SET to create pointers they can only be done inside a
>> Sub.  If you want to use them in another SUB do you have to do the SET
>> again or if in the same module will the SET be know by other Subs?
> It's not the Set statement that matters for your question.  All variables,
> as well as procedures (be they Sub or Function procedures), and modules,
> have a certain scope within which they can be referred to.  What determines
> the scope, in the case of a variable, is where it is declared, what
> statement was used to declare it, and whether it is passed ByRef to any
> other Sub or Function (although in the last case the other procedure would
> have it's own local name for the variable, even though it points to the same
> memory location).
>
> Variables can be declared at the module level as:
>  Public varname As vartype ' uses global or optionally workbook scope
>  Private varname As vartype ' uses module scope
>  Dim varname As vartype ' uses module scope
>
> In a procedure they can be declared:
>  Dim varname As vartype ' uses local (procedure) scope
>  Static varname as vartype ' uses local (procedure) scope and retains value
> between calls
>
> Then use Set if you are assigning an object to a variable, or use Let or the
> usual shorthand of varname=value if you are assigning any other data type.
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Sunday, April 08, 2012 1:36 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
> spreadsheet?
>
> This works.  Is there a reason why it is not better?
>
> I did your array replacement but I changed this
>
> Set InputRange =
> Application.Intersect(Sheets(ContangoSource).UsedRange,
> Sheets(ContangoSource).Columns(ConDate))
>
> with this
>
> Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)
>
> but it does not work to get rid of the array replacement and go all
> the way to this
>
> contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2,
> BarDate).Value, Sheets(ContangoSource).UsedRange.Columns(ConDate), 0)
>
> This is puzzling.  In the case of .rows(1) it can handle the range as
> the array but in this case you have to explicitly convert to an array.
>
> But it can be further whittled down to this
>
> CellsOfInterest = Sheets(ContangoSource).UsedRange.Columns(ConDate).Value
> without these lines
>
> '    Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)
> '    CellsOfInterest = InputRange.Value
>
> And then further down to this though 

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

Ok this works.  But why does it work fine if I replace activesheet
with sheets(rawdata)
but I can not replace application with sheets(rawdata).worksheetfunction

which gives error object does not support property or method

On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>
> read this
> http://support.microsoft.com/kb/194983f
>
> simple change. Keep the special cells just in case or try without
> For Each c In activesheet.usedrange
> but blanks will now be 0 instead of blank
>
>
> Sub roundvalues()
> Dim c As Range
> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>
> c.Value = Application.Round(c, 2)
>
> ‘OR vba round which may round down instead of rounding OFF
> ‘ c = Round(c, 2)
>
> Next c
> End Sub
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 11:21 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
>
> All  my values are numbers so can I eliminate the specialcells part?
> To define the ranges can I use
> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>  using the real name for each column and then use columnA as the
> range?
> But that would I think give me the entire column and I only need to
> check the used range
> The round I used is giving the correct round up at .005 so it is not
> the VBA round I guess though I am not sure I see the distinction in
> your example other
> than the removal of application.
>
> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
>>
>> Sub roundvalues()
>> Dim c As Range
>> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
>> c.Value = Application.Round(c, 2)
>>
>> ‘OR vba round which may round down instead of rounding OFF
>> ‘ c = Round(c, 2)
>>
>> Next c
>> End Sub
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>>
>> From: tangledweb
>> Sent: Sunday, April 08, 2012 5:42 PM
>> To: excel-macros@googlegroups.com
>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> I am trying to get more used to using ranges instead of addressing
>> individual cells by indexes.  The loop below is rounding off the entries
>> in
>> all the columns
>> where BarOpen for example is just the column index in the sheet.  Is there
>> a
>> way to do this more efficiently by using the used range portion of each
>> column?
>> In this case all the columns are the same length.
>>
>>
>> '   Round off the raw data values
>>    With Sheets(RawData)
>>        For index = startrawdata To stoprawdata
>>            Cells(index, BarOpen).Value =
>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>            Cells(index, BarHigh).Value =
>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>            Cells(index, BarLow).Value =
>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>            Cells(index, BarClose).Value =
>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>            Cells(index, StopCol).Value =
>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>        Next index
>>    End With
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> 

Re: $$Excel-Macros$$ SPLIT into many FILES , depeding UPON...

[Somnath Khadilkar]

Dear Sir,
The foremost reason is I need to EMAIL this file  to the
Respective CityCircle, & they require ONLY there own CITY info. The
separate files so created will be saving lot of our SPACE .Currently
anyway I am doing it MANUALLY [ rathersorry state!! ] Print out of
the repo, we do at our HdOfice. and w/o a macro it takes too much of
time...[ over five hundred LEFT clicks alone!! ]

=mangal ho

On 4/9/12, dguillett1  wrote:
> I haven't followed all of this but it seems to me that there is no reason
> for separate files. To get reports or print you can use filtering
> with/without macros and use only ONE file.  Good design???
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message-
> From: Somnath Khadilkar
> Sent: Monday, April 09, 2012 9:58 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
> condition
>
> Sir,
>there are many many colm.in the sheets  [ that mighht complex the
> issue ] , but as a sample I am able to limit to the TWO fields and one
> QTY field. I need all the colm A thru C here in the RESPECTIVE files.
> ---
>
> issue 2.
>I am able to generate a  datafile [ thru some 'C' code , can you
> guide me to 'CONVERT' these into XLS files thru some tool, [ w/o
> openining/saving individuay, as these are TOO many, I need to
> AUTOMAIL ] THIS is altogether a diff. issue, but any help will be
> highly appreciated ]
>
> =Mangal Ho
>
> On 4/9/12, Rajan_Verma  wrote:
>> So , what the excel file will conatin? Only one Row?
>>
>>
>> -Original Message-
>> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Somnath Khadilkar
>> Sent: Apr/Mon/2012 08:11
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
>> condition
>>
>> Dear Sir,
>>
>>The attachment is file with more data in it [ only ONE work SHeet , say
>> sheet1 ]
>>   1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL,
>> DEL..
>> ]
>>   2. the col.B contains item-code say item-1, item-2, item-3...[ for
>> that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
>> 3. Colm C contains just a number showing how many of these exists say 1,2,
>> 600 etc..
>>
>> now the output after running thr macro should be containining a subfolder
>> say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [
>> if
>> quantity exists for the perticular city/item-codeXX ]
>>
>> =mangal ho
>>
>> On 4/9/12, Rajan_Verma  wrote:
>>> Can you add more data and explain how you want to bifurcate the data?
>>>
>>> Rajan.
>>>
>>> -Original Message-
>>> From: excel-macros@googlegroups.com
>>> [mailto:excel-macros@googlegroups.com]
>>> On Behalf Of Somnath Khadilkar
>>> Sent: Apr/Mon/2012 07:37
>>> To: excel-macros@googlegroups.com
>>> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
>>> condition
>>>
>>> Dear Sir,
>>>I reqd the output to be upto say 25 * 10 distict FILES [ sir, not
>>> diff WorkSheets ] the names of these files will be combination of the
>>> 'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named
>>> subfolder, so that I can routinely keep track. ]
>>>
>>> thanks in adv.
>>> PS:  if the file exists --- may be just OVERwrite it...[ if possible
>>> w/o prompting the user]
>>>
>>> =mangal ho
>>>
>>> On 4/9/12, Somnath Khadilkar  wrote:
 Dear Sir,
 MY request is to get the data into MULTIFLE FILES depending upon
 FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
 etc.. [ these will be unique combination] in a subdirectory  named
 say XXYY, which will cotain ALL colms for the selected filters on
 col. A & B so a unique list of cities say 10, and uniq list of items
 say 25 could generate upto 250 files.

 Pl help, attachment enclosed.

 =mangal ho

 --
 FORUM RULES (986+ members already BANNED for violation)

 1) Use concise, accurate thread titles. Poor thread titles, like
 Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
 Need Advice will not get quick attention or may not be answered.

 2) Don't post a question in the thread of another member.

 3) Don't post questions regarding breaking or bypassing any security
 measure.

 4) Acknowledge the responses you receive, good or bad.

 5)  Cross-promotion of, or links to, forums competitive to this forum
 in signatures are prohibited.

 NOTE  : Don't ever post personal or confidential data in a workbook.
 Forum owners and members are not responsible for any loss.

 -
 -
  To post to this group, send email to
 excel-macros@googlegroups.com

>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for v

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Rajan_Verma]

Hi,
I think don concern is to take minimum cells in range for looping, because
Loops on range can make the process slow ,but can take it in array.

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Mon/2012 10:21
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

That link fails.  What you said below I think gives a range that includes
columns that have numbers I would not want rounded.
I can see how it would work I think to eliminate text columns for example,
but not numeric columns where rounding is wrong.
So still back to the best way to create a used range on each column?

On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>
> read this
> http://support.microsoft.com/kb/194983f
>
> simple change. Keep the special cells just in case or try without For 
> Each c In activesheet.usedrange but blanks will now be 0 instead of 
> blank
>
>
> Sub roundvalues()
> Dim c As Range
> For Each c In activesheet.usedrange.SpecialCells(xlConstants, 
> xlNumbers)
>
> c.Value = Application.Round(c, 2)
>
> ‘OR vba round which may round down instead of rounding OFF ‘ c = 
> Round(c, 2)
>
> Next c
> End Sub
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 11:21 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some 
> range method?
>
>
> All  my values are numbers so can I eliminate the specialcells part?
> To define the ranges can I use
> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>  using the real name for each column and then use columnA as the 
> range?
> But that would I think give me the entire column and I only need to 
> check the used range The round I used is giving the correct round up 
> at .005 so it is not the VBA round I guess though I am not sure I see 
> the distinction in your example other than the removal of application.
>
> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
>>
>> Sub roundvalues()
>> Dim c As Range
>> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers) 
>> c.Value = Application.Round(c, 2)
>>
>> ‘OR vba round which may round down instead of rounding OFF ‘ c = 
>> Round(c, 2)
>>
>> Next c
>> End Sub
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>>
>> From: tangledweb
>> Sent: Sunday, April 08, 2012 5:42 PM
>> To: excel-macros@googlegroups.com
>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range 
>> method?
>>
>> I am trying to get more used to using ranges instead of addressing 
>> individual cells by indexes.  The loop below is rounding off the 
>> entries in all the columns where BarOpen for example is just the 
>> column index in the sheet.  Is there a way to do this more 
>> efficiently by using the used range portion of each column?
>> In this case all the columns are the same length.
>>
>>
>> '   Round off the raw data values
>>    With Sheets(RawData)
>>        For index = startrawdata To stoprawdata
>>            Cells(index, BarOpen).Value = 
>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>            Cells(index, BarHigh).Value = 
>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>            Cells(index, BarLow).Value = 
>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>            Cells(index, BarClose).Value = 
>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>            Cells(index, StopCol).Value = 
>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>        Next index
>>    End With
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum 
>> in signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. 
>> Forum owners and members are not responsible for any loss.
>>
>>
>> -
>> - To post to this group, send email 
>> to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a questio

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

That link fails.  What you said below I think gives a range that
includes columns that have numbers I would not want rounded.
I can see how it would work I think to eliminate text columns for
example, but not numeric columns where rounding is wrong.
So still back to the best way to create a used range on each column?

On Mon, Apr 9, 2012 at 9:42 AM, dguillett1  wrote:
>
> read this
> http://support.microsoft.com/kb/194983f
>
> simple change. Keep the special cells just in case or try without
> For Each c In activesheet.usedrange
> but blanks will now be 0 instead of blank
>
>
> Sub roundvalues()
> Dim c As Range
> For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
>
> c.Value = Application.Round(c, 2)
>
> ‘OR vba round which may round down instead of rounding OFF
> ‘ c = Round(c, 2)
>
> Next c
> End Sub
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 11:21 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
>
> All  my values are numbers so can I eliminate the specialcells part?
> To define the ranges can I use
> set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
>  using the real name for each column and then use columnA as the
> range?
> But that would I think give me the entire column and I only need to
> check the used range
> The round I used is giving the correct round up at .005 so it is not
> the VBA round I guess though I am not sure I see the distinction in
> your example other
> than the removal of application.
>
> On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
>>
>> Sub roundvalues()
>> Dim c As Range
>> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
>> c.Value = Application.Round(c, 2)
>>
>> ‘OR vba round which may round down instead of rounding OFF
>> ‘ c = Round(c, 2)
>>
>> Next c
>> End Sub
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>>
>> From: tangledweb
>> Sent: Sunday, April 08, 2012 5:42 PM
>> To: excel-macros@googlegroups.com
>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>
>> I am trying to get more used to using ranges instead of addressing
>> individual cells by indexes.  The loop below is rounding off the entries
>> in
>> all the columns
>> where BarOpen for example is just the column index in the sheet.  Is there
>> a
>> way to do this more efficiently by using the used range portion of each
>> column?
>> In this case all the columns are the same length.
>>
>>
>> '   Round off the raw data values
>>    With Sheets(RawData)
>>        For index = startrawdata To stoprawdata
>>            Cells(index, BarOpen).Value =
>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>            Cells(index, BarHigh).Value =
>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>            Cells(index, BarLow).Value =
>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>            Cells(index, BarClose).Value =
>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>            Cells(index, StopCol).Value =
>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>        Next index
>>    End With
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> 

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Rajan_Verma]

No function exist in this equation.
 
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Mon/2012 10:14
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

But this example did not use Len

selection= Evaluate( selection.address &"/1000"


On Mon, Apr 9, 2012 at 9:37 AM, Rajan_Verma 
wrote:
> You are right..
>
> Well.. Len() Function is necessary to iterate every cell when we are 
> taking it within Evaluate. [I think]
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com 
> [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 10:03
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some 
> range method?
>
> You are correct I have not used it yet.  As I read the description you 
> can use [ ] to enclose things to get rid of the evaluate and quotes.
> Perhaps that would make the syntax easier to understand.  I have not 
> yet figured out exactly what your example equates to.  I think it may 
> be the excel function equivalent of  =if(Len(Range.address
> )>0,Round(Range.address,2))
>
> Len will always be > 0 so that seems not necessary.  Can this be 
> simplified and maybe if written using [ ] easier to understand
>
> On Mon, Apr 9, 2012 at 9:18 AM, Rajan_Verma 
> wrote:
>> Hi
>> It may be possible that you did not use it yet but EVALUATE will 
>> calculate this worksheet function inside   VBA and will assign the 
>> result to each cells in range. It's really useful to avoid loop Try 
>> to use this Rajan
>>
>> -Original Message-
>> From: excel-macros@googlegroups.com
>> [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Domain Admin
>> Sent: Apr/Mon/2012 09:13
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some 
>> range method?
>>
>> Thanks for the help Rajan but your syntax confuses me.  It appears as 
>> if you are trying to create an excel function inside of VBA which 
>> does not make sense to me.
>>
>> On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma 
>> 
>> wrote:
>>> If range have only numeric value  then you can use this to avoid loops :
>>>
>>>
>>>
>>> Not tested: but I think it will work
>>>
>>> Range.value=Evaluate(“=if(Len(“ & Range.address  & “)>0,Round(“ & 
>>> Range.address &”,2),””""")”)
>>>
>>>
>>>
>>> Rajan
>>>
>>>
>>>
>>> From: excel-macros@googlegroups.com
>>> [mailto:excel-macros@googlegroups.com]
>>> On Behalf Of tangledweb
>>> Sent: Apr/Mon/2012 04:12
>>> To: excel-macros@googlegroups.com
>>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>>
>>>
>>>
>>> I am trying to get more used to using ranges instead of addressing 
>>> individual cells by indexes.  The loop below is rounding off the 
>>> entries in all the columns
>>>
>>> where BarOpen for example is just the column index in the sheet.  Is 
>>> there a way to do this more efficiently by using the used range 
>>> portion of each column?
>>>
>>> In this case all the columns are the same length.
>>>
>>>
>>>
>>>
>>>
>>> '   Round off the raw data values
>>>     With Sheets(RawData)
>>>     For index = startrawdata To stoprawdata
>>>     Cells(index, BarOpen).Value = 
>>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>>     Cells(index, BarHigh).Value = 
>>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>>     Cells(index, BarLow).Value = 
>>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>>     Cells(index, BarClose).Value = 
>>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>>     Cells(index, StopCol).Value = 
>>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>>     Next index
>>>     End With
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
>>> Need Advice will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security 
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum 
>>> in signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>> 
>>> -
>>> -
>>>  To post to this group, send email 
>>> to excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>>> Pl

Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[dguillett1]

I haven't followed all of this but it seems to me that there is no reason 
for separate files. To get reports or print you can use filtering 
with/without macros and use only ONE file.  Good design???




Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Somnath Khadilkar

Sent: Monday, April 09, 2012 9:58 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter 
condition


Sir,
  there are many many colm.in the sheets  [ that mighht complex the
issue ] , but as a sample I am able to limit to the TWO fields and one
QTY field. I need all the colm A thru C here in the RESPECTIVE files.
---

issue 2.
  I am able to generate a  datafile [ thru some 'C' code , can you
guide me to 'CONVERT' these into XLS files thru some tool, [ w/o
openining/saving individuay, as these are TOO many, I need to
AUTOMAIL ] THIS is altogether a diff. issue, but any help will be
highly appreciated ]

=Mangal Ho

On 4/9/12, Rajan_Verma  wrote:

So , what the excel file will conatin? Only one Row?


-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 08:11
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Dear Sir,

   The attachment is file with more data in it [ only ONE work SHeet , say
sheet1 ]
  1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL, 
DEL..

]
  2. the col.B contains item-code say item-1, item-2, item-3...[ for
that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
3. Colm C contains just a number showing how many of these exists say 1,2,
600 etc..

now the output after running thr macro should be containining a subfolder
say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [ 
if

quantity exists for the perticular city/item-codeXX ]

=mangal ho

On 4/9/12, Rajan_Verma  wrote:

Can you add more data and explain how you want to bifurcate the data?

Rajan.

-Original Message-
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com]
On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 07:37
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Dear Sir,
   I reqd the output to be upto say 25 * 10 distict FILES [ sir, not
diff WorkSheets ] the names of these files will be combination of the
'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named
subfolder, so that I can routinely keep track. ]

thanks in adv.
PS:  if the file exists --- may be just OVERwrite it...[ if possible
w/o prompting the user]

=mangal ho

On 4/9/12, Somnath Khadilkar  wrote:

Dear Sir,
MY request is to get the data into MULTIFLE FILES depending upon
FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
etc.. [ these will be unique combination] in a subdirectory  named
say XXYY, which will cotain ALL colms for the selected filters on
col. A & B so a unique list of cities say 10, and uniq list of items
say 25 could generate upto 250 files.

Pl help, attachment enclosed.

=mangal ho

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum
in signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.

-
-
 To post to this group, send email to
excel-macros@googlegroups.com



--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum
in signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.

--
--
--
To post to this group, send email to excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for v

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

But this example did not use Len

selection= Evaluate( selection.address &"/1000"


On Mon, Apr 9, 2012 at 9:37 AM, Rajan_Verma  wrote:
> You are right..
>
> Well.. Len() Function is necessary to iterate every cell when we are taking
> it within Evaluate. [I think]
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 10:03
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> You are correct I have not used it yet.  As I read the description you can
> use [ ] to enclose things to get rid of the evaluate and quotes.
> Perhaps that would make the syntax easier to understand.  I have not yet
> figured out exactly what your example equates to.  I think it may be the
> excel function equivalent of  =if(Len(Range.address
> )>0,Round(Range.address,2))
>
> Len will always be > 0 so that seems not necessary.  Can this be simplified
> and maybe if written using [ ] easier to understand
>
> On Mon, Apr 9, 2012 at 9:18 AM, Rajan_Verma 
> wrote:
>> Hi
>> It may be possible that you did not use it yet but EVALUATE will
>> calculate this worksheet function inside   VBA and will assign the
>> result to each cells in range. It's really useful to avoid loop Try to
>> use this Rajan
>>
>> -Original Message-
>> From: excel-macros@googlegroups.com
>> [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Domain Admin
>> Sent: Apr/Mon/2012 09:13
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some
>> range method?
>>
>> Thanks for the help Rajan but your syntax confuses me.  It appears as
>> if you are trying to create an excel function inside of VBA which does
>> not make sense to me.
>>
>> On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma 
>> wrote:
>>> If range have only numeric value  then you can use this to avoid loops :
>>>
>>>
>>>
>>> Not tested: but I think it will work
>>>
>>> Range.value=Evaluate(“=if(Len(“ & Range.address  & “)>0,Round(“ &
>>> Range.address &”,2),””""")”)
>>>
>>>
>>>
>>> Rajan
>>>
>>>
>>>
>>> From: excel-macros@googlegroups.com
>>> [mailto:excel-macros@googlegroups.com]
>>> On Behalf Of tangledweb
>>> Sent: Apr/Mon/2012 04:12
>>> To: excel-macros@googlegroups.com
>>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
>> method?
>>>
>>>
>>>
>>> I am trying to get more used to using ranges instead of addressing
>>> individual cells by indexes.  The loop below is rounding off the
>>> entries in all the columns
>>>
>>> where BarOpen for example is just the column index in the sheet.  Is
>>> there a way to do this more efficiently by using the used range
>>> portion of each column?
>>>
>>> In this case all the columns are the same length.
>>>
>>>
>>>
>>>
>>>
>>> '   Round off the raw data values
>>>     With Sheets(RawData)
>>>     For index = startrawdata To stoprawdata
>>>     Cells(index, BarOpen).Value =
>>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>>     Cells(index, BarHigh).Value =
>>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>>     Cells(index, BarLow).Value =
>>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>>     Cells(index, BarClose).Value =
>>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>>     Cells(index, StopCol).Value =
>>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>>     Next index
>>>     End With
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>>> Need Advice will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>> in signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>> -
>>> -
>>>  To post to this group, send email to
>>> excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>>> Need Advice will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good o

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[dguillett1]

read this
http://support.microsoft.com/kb/194983f

simple change. Keep the special cells just in case or try without
For Each c In activesheet.usedrange
but blanks will now be 0 instead of blank

Sub roundvalues()
Dim c As Range
For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Domain Admin

Sent: Monday, April 09, 2012 11:21 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range 
method?


All  my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
  using the real name for each column and then use columnA as the
range?
But that would I think give me the entire column and I only need to
check the used range
The round I used is giving the correct round up at .005 so it is not
the VBA round I guess though I am not sure I see the distinction in
your example other
than the removal of application.

On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:

Sub roundvalues()
Dim c As Range
For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub


Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: tangledweb
Sent: Sunday, April 08, 2012 5:42 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Could this loop be replaced by some range 
method?


I am trying to get more used to using ranges instead of addressing
individual cells by indexes.  The loop below is rounding off the entries 
in

all the columns
where BarOpen for example is just the column index in the sheet.  Is there 
a

way to do this more efficiently by using the used range portion of each
column?
In this case all the columns are the same length.


'   Round off the raw data values
With Sheets(RawData)
For index = startrawdata To stoprawdata
Cells(index, BarOpen).Value =
WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
Cells(index, BarHigh).Value =
WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
Cells(index, BarLow).Value =
WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
Cells(index, BarClose).Value =
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
Cells(index, StopCol).Value =
WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
Next index
End With
--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
will

not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
will

not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.


2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security 
measure.


4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
s

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Rajan_Verma]

You are right.. 

Well.. Len() Function is necessary to iterate every cell when we are taking
it within Evaluate. [I think]

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Mon/2012 10:03
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

You are correct I have not used it yet.  As I read the description you can
use [ ] to enclose things to get rid of the evaluate and quotes.
Perhaps that would make the syntax easier to understand.  I have not yet
figured out exactly what your example equates to.  I think it may be the
excel function equivalent of  =if(Len(Range.address
)>0,Round(Range.address,2))

Len will always be > 0 so that seems not necessary.  Can this be simplified
and maybe if written using [ ] easier to understand

On Mon, Apr 9, 2012 at 9:18 AM, Rajan_Verma 
wrote:
> Hi
> It may be possible that you did not use it yet but EVALUATE will 
> calculate this worksheet function inside   VBA and will assign the 
> result to each cells in range. It's really useful to avoid loop Try to 
> use this Rajan
>
> -Original Message-
> From: excel-macros@googlegroups.com 
> [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 09:13
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some 
> range method?
>
> Thanks for the help Rajan but your syntax confuses me.  It appears as 
> if you are trying to create an excel function inside of VBA which does 
> not make sense to me.
>
> On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma 
> wrote:
>> If range have only numeric value  then you can use this to avoid loops :
>>
>>
>>
>> Not tested: but I think it will work
>>
>> Range.value=Evaluate(“=if(Len(“ & Range.address  & “)>0,Round(“ & 
>> Range.address &”,2),””""")”)
>>
>>
>>
>> Rajan
>>
>>
>>
>> From: excel-macros@googlegroups.com
>> [mailto:excel-macros@googlegroups.com]
>> On Behalf Of tangledweb
>> Sent: Apr/Mon/2012 04:12
>> To: excel-macros@googlegroups.com
>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>>
>>
>>
>> I am trying to get more used to using ranges instead of addressing 
>> individual cells by indexes.  The loop below is rounding off the 
>> entries in all the columns
>>
>> where BarOpen for example is just the column index in the sheet.  Is 
>> there a way to do this more efficiently by using the used range 
>> portion of each column?
>>
>> In this case all the columns are the same length.
>>
>>
>>
>>
>>
>> '   Round off the raw data values
>>     With Sheets(RawData)
>>     For index = startrawdata To stoprawdata
>>     Cells(index, BarOpen).Value = 
>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>     Cells(index, BarHigh).Value = 
>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>     Cells(index, BarLow).Value = 
>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>     Cells(index, BarClose).Value = 
>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>     Cells(index, StopCol).Value = 
>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>     Next index
>>     End With
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum 
>> in signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> -
>> -
>>  To post to this group, send email to 
>> excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum 
>> in signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

You are correct I have not used it yet.  As I read the description you
can use [ ] to enclose things to get rid of the evaluate and quotes.
Perhaps that would make the syntax easier to understand.  I have not
yet figured out exactly what your example equates to.  I think it may
be
the excel function equivalent of
 =if(Len(Range.address )>0,Round(Range.address,2))

Len will always be > 0 so that seems not necessary.  Can this be
simplified and maybe if written using [ ] easier to understand

On Mon, Apr 9, 2012 at 9:18 AM, Rajan_Verma  wrote:
> Hi
> It may be possible that you did not use it yet but EVALUATE will calculate
> this worksheet function inside   VBA and will assign the result to each
> cells in range. It's really useful to avoid loop
> Try to use this
> Rajan
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 09:13
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> Thanks for the help Rajan but your syntax confuses me.  It appears as if you
> are trying to create an excel function inside of VBA which does not make
> sense to me.
>
> On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma 
> wrote:
>> If range have only numeric value  then you can use this to avoid loops :
>>
>>
>>
>> Not tested: but I think it will work
>>
>> Range.value=Evaluate(“=if(Len(“ & Range.address  & “)>0,Round(“ &
>> Range.address &”,2),””""")”)
>>
>>
>>
>> Rajan
>>
>>
>>
>> From: excel-macros@googlegroups.com
>> [mailto:excel-macros@googlegroups.com]
>> On Behalf Of tangledweb
>> Sent: Apr/Mon/2012 04:12
>> To: excel-macros@googlegroups.com
>> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>>
>>
>>
>> I am trying to get more used to using ranges instead of addressing
>> individual cells by indexes.  The loop below is rounding off the
>> entries in all the columns
>>
>> where BarOpen for example is just the column index in the sheet.  Is
>> there a way to do this more efficiently by using the used range
>> portion of each column?
>>
>> In this case all the columns are the same length.
>>
>>
>>
>>
>>
>> '   Round off the raw data values
>>     With Sheets(RawData)
>>     For index = startrawdata To stoprawdata
>>     Cells(index, BarOpen).Value =
>> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>>     Cells(index, BarHigh).Value =
>> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>>     Cells(index, BarLow).Value =
>> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>>     Cells(index, BarClose).Value =
>> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>>     Cells(index, StopCol).Value =
>> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>>     Next index
>>     End With
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum
>> in signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --
>>  To post to this group, send email to
>> excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum
>> in signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --
>>  To post to this group, send email to
>> excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered

RE: $$Excel-Macros$$ Need chart related vba codes

[Rajan_Verma]

Ok

 

Try this link to download this book

http://www.blitzdownloads.com/download/p-Charts_and_Graphs_for_Microsoft_Off
ice_Excel_2007.rar

 

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mangesh Dayne
Sent: Apr/Mon/2012 08:56
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Need chart related vba codes

 

Hi Rajan,

 

Thanks.

I have checked the link but was unable to download it. 

Kindly help !!!

On Fri, Apr 6, 2012 at 10:35 PM, Rajan_Verma 
wrote:

HI Mangesh

Here is the link for that amazing book..

http://ebookee.org/Charts-and-Graphs-for-Microsoft-Office-Excel-2007_766373.
html

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mangesh Dayne
Sent: Apr/Fri/2012 10:16
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Need chart related vba codes

 

Thanks Rajan,

 

Can you provide me URL for  Excel Chart and Graph by Mr.Excel .


Kind regards,

 

Mangesh 

On Fri, Apr 6, 2012 at 9:00 PM, Rajan_Verma 
wrote:

HI Mangesh

For any type of editing of chart you can find codes from google.. but to
understand charts. You must know object model of Excel Charts, try to learn
Property and method of chartobjects and chart.

You can download the book for Chart..

Excel Chart and Graph by Mr.Excel

 

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mangesh Dayne
Sent: Apr/Fri/2012 08:30
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need chart related vba codes

 

Hi All,

 

Can you send me chart related vba codes like creating, editing chart based
on the data in Excel.

 

 

Kind regards,

 

Mangesh

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 
-

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Rajan_Verma]

EVALUATE  is method in VBA which can evaluate any Function of Excel in VBA
and can return the value in a variable.

To make your code faster you can use this EVALUATE trick in VBA to avoid
extra looping 

Like if you want to Change Case of some Text on selection you can use this
command in vba.

selection=evaluate("=if(len(" & selection.address &")>0,upper(" &
selection.address &")," & selection.address &")")
if you want any Calculation with cells :
selection= Evaluate( selection.address &"/1000"

http://excelpoweruser.blogspot.in/2011/12/evaluate.html

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Mon/2012 09:51
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

All  my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
   using the real name for each column and then use columnA as the range?
But that would I think give me the entire column and I only need to check
the used range The round I used is giving the correct round up at .005 so it
is not the VBA round I guess though I am not sure I see the distinction in
your example other than the removal of application.

On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
> Sub roundvalues()
> Dim c As Range
> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers) 
> c.Value = Application.Round(c, 2)
>
> ‘OR vba round which may round down instead of rounding OFF ‘ c = 
> Round(c, 2)
>
> Next c
> End Sub
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
> From: tangledweb
> Sent: Sunday, April 08, 2012 5:42 PM
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
method?
>
> I am trying to get more used to using ranges instead of addressing 
> individual cells by indexes.  The loop below is rounding off the 
> entries in all the columns where BarOpen for example is just the 
> column index in the sheet.  Is there a way to do this more efficiently 
> by using the used range portion of each column?
> In this case all the columns are the same length.
>
>
> '   Round off the raw data values
>     With Sheets(RawData)
>     For index = startrawdata To stoprawdata
>     Cells(index, BarOpen).Value = 
> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>     Cells(index, BarHigh).Value = 
> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>     Cells(index, BarLow).Value = 
> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>     Cells(index, BarClose).Value = 
> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>     Cells(index, StopCol).Value = 
> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>     Next index
>     End With
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attenti

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

All  my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
   using the real name for each column and then use columnA as the
range?
But that would I think give me the entire column and I only need to
check the used range
The round I used is giving the correct round up at .005 so it is not
the VBA round I guess though I am not sure I see the distinction in
your example other
than the removal of application.

On Mon, Apr 9, 2012 at 7:21 AM, dguillett1  wrote:
> Sub roundvalues()
> Dim c As Range
> For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
> c.Value = Application.Round(c, 2)
>
> ‘OR vba round which may round down instead of rounding OFF
> ‘ c = Round(c, 2)
>
> Next c
> End Sub
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
> From: tangledweb
> Sent: Sunday, April 08, 2012 5:42 PM
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Could this loop be replaced by some range method?
>
> I am trying to get more used to using ranges instead of addressing
> individual cells by indexes.  The loop below is rounding off the entries in
> all the columns
> where BarOpen for example is just the column index in the sheet.  Is there a
> way to do this more efficiently by using the used range portion of each
> column?
> In this case all the columns are the same length.
>
>
> '   Round off the raw data values
>     With Sheets(RawData)
>     For index = startrawdata To stoprawdata
>     Cells(index, BarOpen).Value =
> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>     Cells(index, BarHigh).Value =
> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>     Cells(index, BarLow).Value =
> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>     Cells(index, BarClose).Value =
> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>     Cells(index, StopCol).Value =
> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>     Next index
>     End With
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Rajan_Verma]

Hi 
It may be possible that you did not use it yet but EVALUATE will calculate
this worksheet function inside   VBA and will assign the result to each
cells in range. It's really useful to avoid loop
Try to use this 
Rajan

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Mon/2012 09:13
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?

Thanks for the help Rajan but your syntax confuses me.  It appears as if you
are trying to create an excel function inside of VBA which does not make
sense to me.

On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma 
wrote:
> If range have only numeric value  then you can use this to avoid loops :
>
>
>
> Not tested: but I think it will work
>
> Range.value=Evaluate(“=if(Len(“ & Range.address  & “)>0,Round(“ & 
> Range.address &”,2),””""")”)
>
>
>
> Rajan
>
>
>
> From: excel-macros@googlegroups.com 
> [mailto:excel-macros@googlegroups.com]
> On Behalf Of tangledweb
> Sent: Apr/Mon/2012 04:12
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Could this loop be replaced by some range
method?
>
>
>
> I am trying to get more used to using ranges instead of addressing 
> individual cells by indexes.  The loop below is rounding off the 
> entries in all the columns
>
> where BarOpen for example is just the column index in the sheet.  Is 
> there a way to do this more efficiently by using the used range 
> portion of each column?
>
> In this case all the columns are the same length.
>
>
>
>
>
> '   Round off the raw data values
>     With Sheets(RawData)
>     For index = startrawdata To stoprawdata
>     Cells(index, BarOpen).Value = 
> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>     Cells(index, BarHigh).Value = 
> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>     Cells(index, BarLow).Value = 
> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>     Cells(index, BarClose).Value = 
> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>     Cells(index, StopCol).Value = 
> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>     Next index
>     End With
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, 

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Domain Admin]

Thanks for the help Rajan but your syntax confuses me.  It appears as
if you are trying to create an excel function inside of VBA which
does not make sense to me.

On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma  wrote:
> If range have only numeric value  then you can use this to avoid loops :
>
>
>
> Not tested: but I think it will work
>
> Range.value=Evaluate(“=if(Len(“ & Range.address  & “)>0,Round(“ &
> Range.address &”,2),””)”)
>
>
>
> Rajan
>
>
>
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of tangledweb
> Sent: Apr/Mon/2012 04:12
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Could this loop be replaced by some range method?
>
>
>
> I am trying to get more used to using ranges instead of addressing
> individual cells by indexes.  The loop below is rounding off the entries in
> all the columns
>
> where BarOpen for example is just the column index in the sheet.  Is there a
> way to do this more efficiently by using the used range portion of each
> column?
>
> In this case all the columns are the same length.
>
>
>
>
>
> '   Round off the raw data values
>     With Sheets(RawData)
>     For index = startrawdata To stoprawdata
>     Cells(index, BarOpen).Value =
> WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
>     Cells(index, BarHigh).Value =
> WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
>     Cells(index, BarLow).Value =
> WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
>     Cells(index, BarClose).Value =
> WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
>     Cells(index, StopCol).Value =
> WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
>     Next index
>     End With
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Domain Admin]

All for the education.  Keep it coming.
Once I have things working I will go back and try to make
the program more efficient and elegant.

The statement below does not work if you remove the .value.
Back to the not finding the match function error.
>> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
>> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)


With respect to your statement that the non intersect version below
will not always work.  You said it is because usedrange does not always start
at A1 (it will for this case but still agree bad practice to count on
it that being the case.).
In your case it works because you are using usedrange for
the entire sheet and return wherever that intersects the data
column even if that column of data is not starting at the first row?
But my case if the data does not start at the first row may still work
but not be efficient because of a lot of wasted empty cells at the top?

Set InputRange =
Application.Intersect(Sheets(ContangoSource).UsedRange,
Sheets(ContangoSource).Columns(ConDate))

with this

Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)

=

On Mon, Apr 9, 2012 at 3:38 AM, Asa Rossoff  wrote:
>>     contangoindex = _
>> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
>> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
> RE: passing an array like this, without respect to using Intersect;
>
> Since WorksheetFunction.Match accepts an actual range even though Help
> describes the argument as an array, it may well perform best if you just
> pass the range reference (no .value at the end).  That way it can decide how
> to handle the data best for itself.  The most important issue was to limit
> the size of the range properly.
>
> -Original Message-
> From: Asa Rossoff [mailto:a...@lovetour.info]
> Sent: Monday, April 09, 2012 2:35 AM
> To: 'excel-macros@googlegroups.com'
> Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
> spreadsheet?
>
>> This works.  Is there a reason why it is not better?
> Yes.  It won't always work.  RANGE.Columns is relative to that range (as is
> RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet.  UsedRange
> doesn't always start in A1.
>
>> This is puzzling.  In the case of .rows(1) it can handle the range as
>> the array but in this case you have to explicitly convert to an array.
> Perhaps because in Excel 2007+ there are 16,384 columns, much less than the
> million+ rows,
> Your syntax might well work in prior versions of Excel, where there are just
> 65,536 rows.
>
>> And then further down to this though not as easy to read and understand
>>
>>     contangoindex = _
>> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
>> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
>>
>> So what is happening here?  Is this still just by adding the .value
>> converting the range into an array as some secret VBA thing where you
>> are extracting the values out of the range and they have to go
>> somewhere to it creates and array for them?
> Once you specify the .Value property of a range larger than one cell, you
> are specifying an array.  It doesn't matter if you don't create a variable
> to refer to it.  If you read the Help entry for WorksheetFunction.Match
> you'll see that the data type for all the arguments is Variant.  Your array
> will be stored in memory as an array of Variant (the .Value property is
> Variant) and stored inside another Variant (full name "Variant Array of
> Variant"), and a pointer to it will be passed to WorksheetFunction.Match.
> When Match is done with it's work, the array will be destroyed from memory
> since there are no other pointers to it.  If you had a need to refer to it
> again, it would be a very good idea to create the variable first and not
> have VBA waste energy creating and destroying the whole array multiple
> times.
>
>> You are right though.  Once you find a working bone you can keep
>> gnawing on it until down to minimal shard that still functions.
> It can help in understanding what's what.
>
>> When you use SET to create pointers they can only be done inside a
>> Sub.  If you want to use them in another SUB do you have to do the SET
>> again or if in the same module will the SET be know by other Subs?
> It's not the Set statement that matters for your question.  All variables,
> as well as procedures (be they Sub or Function procedures), and modules,
> have a certain scope within which they can be referred to.  What determines
> the scope, in the case of a variable, is where it is declared, what
> statement was used to declare it, and whether it is passed ByRef to any
> other Sub or Function (although in the last case the other procedure would
> have it's own local name for the variable, even though it points to the same
> memory location).
>
> Variables can be declared at the module level as:

Re: $$Excel-Macros$$ Need chart related vba codes

[Mangesh Dayne]

Hi Rajan,

Thanks.
I have checked the link but was unable to download it.
Kindly help !!!

On Fri, Apr 6, 2012 at 10:35 PM, Rajan_Verma wrote:

> HI Mangesh
>
> Here is the link for that amazing book..
>
>
> http://ebookee.org/Charts-and-Graphs-for-Microsoft-Office-Excel-2007_766373.html
> 
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Mangesh Dayne
> *Sent:* Apr/Fri/2012 10:16
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Need chart related vba codes
>
> ** **
>
> Thanks Rajan,
>
> ** **
>
> Can you provide me URL for  Excel Chart and Graph by Mr.Excel .
>
>
> Kind regards,
>
> ** **
>
> Mangesh 
>
> On Fri, Apr 6, 2012 at 9:00 PM, Rajan_Verma 
> wrote:
>
> HI Mangesh
>
> For any type of editing of chart you can find codes from google.. but to
> understand charts. You must know object model of Excel Charts, try to learn
> Property and method of chartobjects and chart.
>
> You can download the book for Chart..
>
> Excel Chart and Graph by Mr.Excel
>
>  
>
> Rajan.
>
>  
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Mangesh Dayne
> *Sent:* Apr/Fri/2012 08:30
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Need chart related vba codes
>
>  
>
> Hi All,
>
>  
>
> Can you send me chart related vba codes like creating, editing chart based
> on the data in Excel.
>
>  
>
>  
>
> Kind regards,
>
>  
>
> Mangesh
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> ** **
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>

Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[Somnath Khadilkar]

Sir,
   there are many many colm.in the sheets  [ that mighht complex the
issue ] , but as a sample I am able to limit to the TWO fields and one
QTY field. I need all the colm A thru C here in the RESPECTIVE files.
---

issue 2.
   I am able to generate a  datafile [ thru some 'C' code , can you
guide me to 'CONVERT' these into XLS files thru some tool, [ w/o
openining/saving individuay, as these are TOO many, I need to
AUTOMAIL ] THIS is altogether a diff. issue, but any help will be
highly appreciated ]

=Mangal Ho

On 4/9/12, Rajan_Verma  wrote:
> So , what the excel file will conatin? Only one Row?
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Somnath Khadilkar
> Sent: Apr/Mon/2012 08:11
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
> condition
>
> Dear Sir,
>
>The attachment is file with more data in it [ only ONE work SHeet , say
> sheet1 ]
>   1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL, DEL..
> ]
>   2. the col.B contains item-code say item-1, item-2, item-3...[ for
> that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
> 3. Colm C contains just a number showing how many of these exists say 1,2,
> 600 etc..
>
> now the output after running thr macro should be containining a subfolder
> say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [ if
> quantity exists for the perticular city/item-codeXX ]
>
> =mangal ho
>
> On 4/9/12, Rajan_Verma  wrote:
>> Can you add more data and explain how you want to bifurcate the data?
>>
>> Rajan.
>>
>> -Original Message-
>> From: excel-macros@googlegroups.com
>> [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Somnath Khadilkar
>> Sent: Apr/Mon/2012 07:37
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
>> condition
>>
>> Dear Sir,
>>I reqd the output to be upto say 25 * 10 distict FILES [ sir, not
>> diff WorkSheets ] the names of these files will be combination of the
>> 'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named
>> subfolder, so that I can routinely keep track. ]
>>
>> thanks in adv.
>> PS:  if the file exists --- may be just OVERwrite it...[ if possible
>> w/o prompting the user]
>>
>> =mangal ho
>>
>> On 4/9/12, Somnath Khadilkar  wrote:
>>> Dear Sir,
>>> MY request is to get the data into MULTIFLE FILES depending upon
>>> FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
>>> etc.. [ these will be unique combination] in a subdirectory  named
>>> say XXYY, which will cotain ALL colms for the selected filters on
>>> col. A & B so a unique list of cities say 10, and uniq list of items
>>> say 25 could generate upto 250 files.
>>>
>>> Pl help, attachment enclosed.
>>>
>>> =mangal ho
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>>> Need Advice will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5)  Cross-promotion of, or links to, forums competitive to this forum
>>> in signatures are prohibited.
>>>
>>> NOTE  : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>> -
>>> -
>>>  To post to this group, send email to
>>> excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum
>> in signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --
>> --
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Ne

RE: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[Rajan_Verma]

So , what the excel file will conatin? Only one Row?


-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 08:11
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Dear Sir,

   The attachment is file with more data in it [ only ONE work SHeet , say
sheet1 ]
  1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL, DEL..
]
  2. the col.B contains item-code say item-1, item-2, item-3...[ for
that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
3. Colm C contains just a number showing how many of these exists say 1,2,
600 etc..

now the output after running thr macro should be containining a subfolder
say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [ if
quantity exists for the perticular city/item-codeXX ]

=mangal ho

On 4/9/12, Rajan_Verma  wrote:
> Can you add more data and explain how you want to bifurcate the data?
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com 
> [mailto:excel-macros@googlegroups.com]
> On Behalf Of Somnath Khadilkar
> Sent: Apr/Mon/2012 07:37
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter 
> condition
>
> Dear Sir,
>I reqd the output to be upto say 25 * 10 distict FILES [ sir, not 
> diff WorkSheets ] the names of these files will be combination of the 
> 'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named 
> subfolder, so that I can routinely keep track. ]
>
> thanks in adv.
> PS:  if the file exists --- may be just OVERwrite it...[ if possible 
> w/o prompting the user]
>
> =mangal ho
>
> On 4/9/12, Somnath Khadilkar  wrote:
>> Dear Sir,
>> MY request is to get the data into MULTIFLE FILES depending upon 
>> FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2 
>> etc.. [ these will be unique combination] in a subdirectory  named 
>> say XXYY, which will cotain ALL colms for the selected filters on 
>> col. A & B so a unique list of cities say 10, and uniq list of items 
>> say 25 could generate upto 250 files.
>>
>> Pl help, attachment enclosed.
>>
>> =mangal ho
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like 
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum 
>> in signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> -
>> -
>>  To post to this group, send email to 
>> excel-macros@googlegroups.com
>>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
> --
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups

Re: $$Excel-Macros$$ If you put a userform on a sheet is that part of the usedrange?

[Domain Admin]

This problem is solved.

On Mon, Apr 9, 2012 at 5:52 AM, Rajan_Verma  wrote:
> Can you explain More to get desired result?
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 10:35
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ If you put a userform on a sheet is that part
> of the usedrange?
>
> Never mind, answered my own question.
>
> On Sun, Apr 8, 2012 at 3:48 PM, tangledweb  wrote:
>> I would like to have a userform on a data sheet for setting parameters
>> and controls.
>> But if that useform is considered part of the range by the usedrange
>> function then I will have to put it on a separate sheet which would be
>> something of a pain.
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum
>> in signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --
>>  To post to this group, send email to
>> excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> 
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
> Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not 
> get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in 
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[Somnath Khadilkar]

Dear Sir,

   The attachment is file with more data in it [ only ONE work SHeet ,
say sheet1 ]
  1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL, DEL.. ]
  2. the col.B contains item-code say item-1, item-2, item-3...[ for
that matter mango,apple,banana ]   currently upto 25 distinct 'FRUITS'
3. Colm C contains just a number showing how many of these exists say
1,2, 600 etc..

now the output after running thr macro should be containining
a subfolder say 'data' and XL files in it by name say AHM-item-1,
AHM-item-10, [ if quantity exists for the perticular
city/item-codeXX ]

=mangal ho

On 4/9/12, Rajan_Verma  wrote:
> Can you add more data and explain how you want to bifurcate the data?
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Somnath Khadilkar
> Sent: Apr/Mon/2012 07:37
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
> condition
>
> Dear Sir,
>I reqd the output to be upto say 25 * 10 distict FILES [ sir, not diff
> WorkSheets ] the names of these files will be combination of the 'CITY' and
> 'ITEM' code eg del-item001, hyd-item002, [ in a named subfolder, so that I
> can routinely keep track. ]
>
> thanks in adv.
> PS:  if the file exists --- may be just OVERwrite it...[ if possible w/o
> prompting the user]
>
> =mangal ho
>
> On 4/9/12, Somnath Khadilkar  wrote:
>> Dear Sir,
>> MY request is to get the data into MULTIFLE FILES depending upon
>> FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
>> etc.. [ these will be unique combination] in a subdirectory  named say
>> XXYY, which will cotain ALL colms for the selected filters on col. A &
>> B so a unique list of cities say 10, and uniq list of items say 25
>> could generate upto 250 files.
>>
>> Pl help, attachment enclosed.
>>
>> =mangal ho
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum
>> in signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --
>>  To post to this group, send email to
>> excel-macros@googlegroups.com
>>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> 
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaki

Re: $$Excel-Macros$$ need excel dyanmic web query macro

[vijayajith VA]

HI asa,,

Nice links thanks
Is this possible to record macro --webquery..."
http://www.techrepublic.com/article/pull-data-into-microsoft-excel-with-web-queries/6115870
 "

On Sat, Apr 7, 2012 at 2:21 AM, Asa Rossoff  wrote:

> I get some kind of SQL server error from that tracking URL (perhaps
> because I'm not in India), but if the tracking info is in a table on the
> web page, you should be able to use a Web Query to integrate the info right
> into your worksheet.
>
> http://www.mrexcel.com/tip103.shtml
>
>
> https://office.microsoft.com/en-us/excel-help/get-and-analyze-data-from-the-web-in-excel-HA001054848.aspx
> 
>
>
> http://www.techrepublic.com/article/pull-data-into-microsoft-excel-with-web-queries/6115870
> 
>
> http://www.vertex42.com/News/excel-web-query.html
>
> http://www.jkp-ads.com/Articles/WebQuery.asp
>
> ** **
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Mohanraj shunmugam
> *Sent:* Friday, April 06, 2012 6:36 AM
>
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ need excel dyanmic web query macro
>
> ** **
>
> dear sir,
>
>
> i here attach my file its for india post registered letter status
>
> i have only one input data
>
> i am using "hyperlink"
>
> when i press search new webpage opened and show the status
>
> if there is any possibility to show the status table below  the search
> button within same exceland not opening browser page
>
> need our expert help
>
> thanking you
>
> regards,
>
> mohanraj s
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Could this loop be replaced by some range method?

[dguillett1]

Sub roundvalues()
Dim c As Range
For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)

‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)

Next c
End Sub


Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: tangledweb 
Sent: Sunday, April 08, 2012 5:42 PM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Could this loop be replaced by some range method?

I am trying to get more used to using ranges instead of addressing individual 
cells by indexes.  The loop below is rounding off the entries in all the columns
where BarOpen for example is just the column index in the sheet.  Is there a 
way to do this more efficiently by using the used range portion of each column?
In this case all the columns are the same length.


'   Round off the raw data values
With Sheets(RawData)
For index = startrawdata To stoprawdata
Cells(index, BarOpen).Value = WorksheetFunction.Round(.Cells(index, 
BarOpen).Value, 2)
Cells(index, BarHigh).Value = WorksheetFunction.Round(.Cells(index, 
BarHigh).Value, 2)
Cells(index, BarLow).Value = WorksheetFunction.Round(.Cells(index, 
BarLow).Value, 2)
Cells(index, BarClose).Value = 
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
Cells(index, StopCol).Value = WorksheetFunction.Round(.Cells(index, 
StopCol).Value, 2)
Next index
End With
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[Rajan_Verma]

Can you add more data and explain how you want to bifurcate the data?

Rajan. 

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 07:37
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter
condition

Dear Sir,
   I reqd the output to be upto say 25 * 10 distict FILES [ sir, not diff
WorkSheets ] the names of these files will be combination of the 'CITY' and
'ITEM' code eg del-item001, hyd-item002, [ in a named subfolder, so that I
can routinely keep track. ]

thanks in adv.
PS:  if the file exists --- may be just OVERwrite it...[ if possible w/o
prompting the user]

=mangal ho

On 4/9/12, Somnath Khadilkar  wrote:
> Dear Sir,
> MY request is to get the data into MULTIFLE FILES depending upon 
> FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2 
> etc.. [ these will be unique combination] in a subdirectory  named say  
> XXYY, which will cotain ALL colms for the selected filters on col. A & 
> B so a unique list of cities say 10, and uniq list of items say 25 
> could generate upto 250 files.
>
> Pl help, attachment enclosed.
>
> =mangal ho
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com
>

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Editing text in an unlocked cell of a protected sheet

[David]

Thank you for your reply.  File is attached to this post.
 
Specifically, look at the 'goals' and 'comments' near the end of the form.
If you want to unlock/unprotect it, there is no longer a password.
 
 
D

On Sunday, April 8, 2012 10:29:55 AM UTC-5, Maries wrote:

> *Can you share your file...*
>
> On Sun, Apr 8, 2012 at 4:18 AM, David  wrote:
>
>> I'm looking for assistance in fixing a problem a user found.
>>  
>> I have a form created in Excel 2010.  All cells on the form were locked - 
>> then specific cells were unlocked.  This allowed the users to only modify 
>> the 'blank' fields in the form.
>> The worksheet was then protected using Protect Sheet button in the Review 
>> tab.
>> The only option selected under the "Allow all users of this worksheet 
>> to:" is Select Unlocked Cells.(Although I have also tried checking the 
>> Format Cells option too with the same outcome.)
>>  
>> The worksheet is protected and users are only allowed to enter text into 
>> the appropriate cells/fields as I intended.   
>> However, if a user wants to go back and edit something that was already 
>> typed in, they are unable to do so.  
>>
>> Normally, you would see the text in the formula bar to edit, or 
>> double-click and edit.  Neither option is available.
>> There are a couple cells where users may type in several sentances.  If 
>> they try to go back to correct/add/edit all text in that cell deletes as 
>> soon as one character is typed.  Truly not friendly to the users.
>>
>> What option did I miss?  Help!!
>>  
>>  
>> David - 
>> Kansas City
>>
>> -- 
>> FORUM RULES (986+ members already BANNED for violation)
>>  
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
>> will not get quick attention or may not be answered.
>>  
>> 2) Don't post a question in the thread of another member.
>>  
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>  
>> 4) Acknowledge the responses you receive, good or bad.
>>  
>> 5) Cross-promotion of, or links to, forums competitive to this forum in 
>> signatures are prohibited. 
>>  
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum 
>> owners and members are not responsible for any loss.
>>  
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Introductory Review.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[Somnath Khadilkar]

Dear Sir,
   I reqd the output to be upto say 25 * 10 distict FILES [ sir, not
diff WorkSheets ] the names of these files will be combination of the
'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named
subfolder, so that I can routinely keep track. ]

thanks in adv.
PS:  if the file exists --- may be just OVERwrite it...[ if possible
w/o prompting the user]

=mangal ho

On 4/9/12, Somnath Khadilkar  wrote:
> Dear Sir,
> MY request is to get the data into MULTIFLE FILES depending upon
> FILTER Selected.  The file names should be say ahm-item-1, ahm-item-2
> etc.. [ these will be unique combination] in a subdirectory  named say
>  XXYY, which will cotain ALL colms for the selected filters on col. A
> & B
> so a unique list of cities say 10, and uniq list of items say 25 could
> generate upto 250 files.
>
> Pl help, attachment enclosed.
>
> =mangal ho
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


svk-sample.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

RE: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a folder.

[Rajan_Verma]

Ok,

Just remove the Blank line in between RED Lines, error is just because of
all lines are not in continuous ,

 

 

Rajan.

 

From: Excel_Lover [mailto:idforex...@gmail.com] 
Sent: Apr/Mon/2012 07:25
To: rajanverma1...@gmail.com
Subject: Fwd: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.

 


Sorry, Big file is not reaching to group.

-- Forwarded message --
From: Excel_Lover 
Date: Mon, Apr 9, 2012 at 4:52 PM
Subject: Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.
To: excel-macros@googlegroups.com



Version is excel 2007,

Please find the attached screenshot showing the same error. 

 

On Mon, Apr 9, 2012 at 4:41 PM, Rajan_Verma 
wrote:

Which verion of excel are you using.. can you send screenshot.?

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Excel_Lover
Sent: Apr/Mon/2012 07:09


To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.

 

Dear Rajan,

Thanks for your effort on this, 

It shows some compile error, 

Please find below i have red colored the error. 


Sub CreatePDF()

   

Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte

   

intI = 0

intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0
For Active Worksheets")

If intResult = 0 Then

Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

Exit Sub

End If

For Each wksSheet In ThisWorkbook.Worksheets

If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then

If wksSheet.Visible = xlSheetHidden Then

wksSheet.Visible = xlSheetVisible

blnFlag = True

End If

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

intI = intI + 1

If blnFlag = True Then

wksSheet.Visible = xlSheetHidden

blnFlag = False

End If

End If

Next

MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation

   

End Sub



On Mon, Apr 9, 2012 at 4:15 PM, Rajan_Verma 
wrote:

Hi

If you are using Excel -2007 , you can use this , your PDF file will save at
Current Workbook saved Location :

 

Sub CreatePDF()

   

Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte

   

intI = 0

intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0
For Active Worksheets")

If intResult = 0 Then

Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

Exit Sub

End If

For Each wksSheet In ThisWorkbook.Worksheets

If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then

If wksSheet.Visible = xlSheetHidden Then

wksSheet.Visible = xlSheetVisible

blnFlag = True

End If

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

intI = intI + 1

If blnFlag = True Then

wksSheet.Visible = xlSheetHidden

blnFlag = False

End If

End If

Next

MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation

   

End Sub

 

http://excelpoweruser.blogspot.in

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Excel_Lover
Sent: Apr/Mon/2012 05:22
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.

 

Dear All,

 

Hope somebody can help me to create a macro to:

 

print the sheet in pdf,

name the PDF as cell value 'E4'

and save the same into location : C:\Users\FAISAL\Desktop\PRSD

 

Please find the attached sheet for your reference.

 

Thanks in advance for your valuable assistance.

 

 

 

On Sun, Apr 8, 2012 at 11:05 PM, Excel_Lover  wrote:

Dear All,

 

please find the below menti

RE: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a folder.

[Rajan_Verma]

Which verion of excel are you using.. can you send screenshot.?

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Excel_Lover
Sent: Apr/Mon/2012 07:09
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.

 

Dear Rajan,

Thanks for your effort on this, 

It shows some compile error, 

Please find below i have red colored the error. 


Sub CreatePDF()

   

Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte

   

intI = 0

intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0
For Active Worksheets")

If intResult = 0 Then

Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

Exit Sub

End If

For Each wksSheet In ThisWorkbook.Worksheets

If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then

If wksSheet.Visible = xlSheetHidden Then

wksSheet.Visible = xlSheetVisible

blnFlag = True

End If

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

intI = intI + 1

If blnFlag = True Then

wksSheet.Visible = xlSheetHidden

blnFlag = False

End If

End If

Next

MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation

   

End Sub




On Mon, Apr 9, 2012 at 4:15 PM, Rajan_Verma 
wrote:

Hi

If you are using Excel -2007 , you can use this , your PDF file will save at
Current Workbook saved Location :

 

Sub CreatePDF()

   

Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte

   

intI = 0

intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0
For Active Worksheets")

If intResult = 0 Then

Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

Exit Sub

End If

For Each wksSheet In ThisWorkbook.Worksheets

If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then

If wksSheet.Visible = xlSheetHidden Then

wksSheet.Visible = xlSheetVisible

blnFlag = True

End If

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

intI = intI + 1

If blnFlag = True Then

wksSheet.Visible = xlSheetHidden

blnFlag = False

End If

End If

Next

MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation

   

End Sub

 

http://excelpoweruser.blogspot.in

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Excel_Lover
Sent: Apr/Mon/2012 05:22
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.

 

Dear All,

 

Hope somebody can help me to create a macro to:

 

print the sheet in pdf,

name the PDF as cell value 'E4'

and save the same into location : C:\Users\FAISAL\Desktop\PRSD

 

Please find the attached sheet for your reference.

 

Thanks in advance for your valuable assistance.

 

 

 

On Sun, Apr 8, 2012 at 11:05 PM, Excel_Lover  wrote:

Dear All,

 

please find the below mentioned Macro which I recorded to print a sheet in
PDF,

 

Sub test()
   
Application.ActivePrinter = "PDFCreator on Ne01:"
ExecuteExcel4Macro _
"PRINT(2,3,3,12,""PDFCreator on Ne01:"",,TRUE,,FALSE)"
End Sub

Can somebody please modify the macro which can save the created PDF file in
a specified folder.

 

Thanks in advance for your helps.

 

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regardi

Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a folder.

[Excel_Lover]

Dear Rajan,

Thanks for your effort on this,

It shows some compile error,

Please find below i have red colored the error.


Sub CreatePDF()



Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte



intI = 0

intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0
For Active Worksheets")

If intResult = 0 Then

Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

Exit Sub

End If

For Each wksSheet In ThisWorkbook.Worksheets

If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then

If wksSheet.Visible = xlSheetHidden Then

wksSheet.Visible = xlSheetVisible

blnFlag = True

End If

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

intI = intI + 1

If blnFlag = True Then

wksSheet.Visible = xlSheetHidden

blnFlag = False

End If

End If

Next

MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation



End Sub



On Mon, Apr 9, 2012 at 4:15 PM, Rajan_Verma wrote:

> Hi
>
> If you are using Excel -2007 , you can use this , your PDF file will save
> at Current Workbook saved Location :
>
> ** **
>
> Sub CreatePDF()
>
>
>
> Dim wksSheet As Worksheet
>
> Dim blnFlag As Boolean
>
> Dim intI As Integer
>
> Dim intResult As Byte
>
>
>
> intI = 0
>
> intResult = Application.InputBox("Type 1 for Entire Workbook and Type
> 0 For Active Worksheets")
>
> If intResult = 0 Then
>
> Set wksSheet = ActiveSheet
>
> wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
>
> ThisWorkbook.Path & "\" & wksSheet.Name,
> Quality:=xlQualityStandard, _
>
> IncludeDocProperties:=True, IgnorePrintAreas:=False,
> OpenAfterPublish:= _
>
> False
>
> Exit Sub
>
> End If
>
> For Each wksSheet In ThisWorkbook.Worksheets
>
> If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then
>
> If wksSheet.Visible = xlSheetHidden Then
>
> wksSheet.Visible = xlSheetVisible
>
> blnFlag = True
>
> End If
>
> wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
>
> ThisWorkbook.Path & "\" & wksSheet.Name,
> Quality:=xlQualityStandard, _
>
> IncludeDocProperties:=True, IgnorePrintAreas:=False,
> OpenAfterPublish:= _
>
> False
>
> intI = intI + 1
>
> If blnFlag = True Then
>
> wksSheet.Visible = xlSheetHidden
>
> blnFlag = False
>
> End If
>
> End If
>
> Next
>
> MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation*
> ***
>
>
>
> End Sub
>
> ** **
>
> http://excelpoweruser.blogspot.in
>
> Rajan
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Excel_Lover
> *Sent:* Apr/Mon/2012 05:22
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save
> in a folder.
>
> ** **
>
> Dear All,
>
> ** **
>
> Hope somebody can help me to create a macro to:
>
> ** **
>
> print the sheet in pdf,
>
> name the PDF as cell value 'E4'
>
> and save the same into location : C:\Users\FAISAL\Desktop\PRSD
>
> ** **
>
> Please find the attached sheet for your reference.
>
> ** **
>
> Thanks in advance for your valuable assistance.
>
> ** **
>
> ** **
>
> ** **
>
> On Sun, Apr 8, 2012 at 11:05 PM, Excel_Lover  wrote:
> 
>
> Dear All,
>
>  
>
> please find the below mentioned Macro which I recorded to print a sheet in
> PDF,
>
>  
>
> Sub test()
>
> Application.ActivePrinter = "PDFCreator on Ne01:"
> ExecuteExcel4Macro _
> "PRINT(2,3,3,12,""PDFCreator on Ne01:"",,TRUE,,FALSE)"
> End Sub
>
> Can somebody please modify the macro which can save the created PDF file
> in a specified folder.
>
>  
>
> Thanks in advance for your helps.
>
>  
>
>  
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> He

RE: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a folder.

[Rajan_Verma]

Hi

If you are using Excel -2007 , you can use this , your PDF file will save at
Current Workbook saved Location :

 

Sub CreatePDF()

   

Dim wksSheet As Worksheet

Dim blnFlag As Boolean

Dim intI As Integer

Dim intResult As Byte

   

intI = 0

intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0
For Active Worksheets")

If intResult = 0 Then

Set wksSheet = ActiveSheet

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

Exit Sub

End If

For Each wksSheet In ThisWorkbook.Worksheets

If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then

If wksSheet.Visible = xlSheetHidden Then

wksSheet.Visible = xlSheetVisible

blnFlag = True

End If

wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & wksSheet.Name,
Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _

False

intI = intI + 1

If blnFlag = True Then

wksSheet.Visible = xlSheetHidden

blnFlag = False

End If

End If

Next

MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation

   

End Sub

 

http://excelpoweruser.blogspot.in

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Excel_Lover
Sent: Apr/Mon/2012 05:22
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a
folder.

 

Dear All,

 

Hope somebody can help me to create a macro to:

 

print the sheet in pdf,

name the PDF as cell value 'E4'

and save the same into location : C:\Users\FAISAL\Desktop\PRSD

 

Please find the attached sheet for your reference.

 

Thanks in advance for your valuable assistance.

 

 

 

On Sun, Apr 8, 2012 at 11:05 PM, Excel_Lover  wrote:

Dear All,

 

please find the below mentioned Macro which I recorded to print a sheet in
PDF,

 

Sub test()
   
Application.ActivePrinter = "PDFCreator on Ne01:"
ExecuteExcel4Macro _
"PRINT(2,3,3,12,""PDFCreator on Ne01:"",,TRUE,,FALSE)"
End Sub

Can somebody please modify the macro which can save the created PDF file in
a specified folder.

 

Thanks in advance for your helps.

 

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential

RE: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

[Rajan_Verma]

Use this : in Attached Sheet.

Sub DistributeDataOnSheets()

Dim VarFilterData()
Dim objDic As Object
Dim wksSheet As Worksheet
Dim lngLoop As Long
Dim rngRange As Range
Dim wkSSheetNew As Worksheet

Set objDic = CreateObject("Scripting.Dictionary")
Set wksSheet = ThisWorkbook.Worksheets("Sheet1")
VarFilterData = Application.Transpose(Intersect(wksSheet.UsedRange,
wksSheet.UsedRange.Columns(2).Offset(1)))

For lngLoop = LBound(VarFilterData) To UBound(VarFilterData)
If Not objDic.Exists(VarFilterData(lngLoop)) Then objDic.Add
VarFilterData(lngLoop), VarFilterData(lngLoop)
Next lngLoop

Application.ScreenUpdating = False
For lngLoop = 1 To objDic.Count
With wksSheet.UsedRange.Columns(2)
.Replace VarFilterData(lngLoop), ""
Set rngRange = .SpecialCells(xlCellTypeBlanks)
rngRange.Value = VarFilterData(lngLoop)
End With
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(VarFilterData(lngLoop)).Delete
On Error GoTo 0: On Error GoTo -1
Application.DisplayAlerts = True
Set wkSSheetNew = ThisWorkbook.Worksheets.Add
wkSSheetNew.Name = VarFilterData(lngLoop)
wksSheet.Rows(1).Copy wkSSheetNew.Range("A1")
rngRange.EntireRow.Copy wkSSheetNew.Range("A2")
Next lngLoop

Application.ScreenUpdating = True
MsgBox "Done"

End Sub

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Somnath Khadilkar
Sent: Apr/Mon/2012 08:33
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ one to many FILES , depeding upon filter condition

Dear Sir,
MY request is to get the data into MULTIFLE FILES depending upon FILTER
Selected.  The file names should be say ahm-item-1, ahm-item-2 etc.. [ these
will be unique combination] in a subdirectory  named say  XXYY, which will
cotain ALL colms for the selected filters on col. A & B so a unique list of
cities say 10, and uniq list of items say 25 could generate upto 250 files.

Pl help, attachment enclosed.

=mangal ho

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Copy of svk-sample.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

RE: $$Excel-Macros$$ If you put a userform on a sheet is that part of the usedrange?

[Rajan_Verma]

Can you explain More to get desired result?

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Mon/2012 10:35
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ If you put a userform on a sheet is that part
of the usedrange?

Never mind, answered my own question.

On Sun, Apr 8, 2012 at 3:48 PM, tangledweb  wrote:
> I would like to have a userform on a data sheet for setting parameters 
> and controls.
> But if that useform is considered part of the range by the usedrange 
> function then I will have to put it on a separate sheet which would be 
> something of a pain.
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Could this loop be replaced by some range method?

[Rajan_Verma]

If range have only numeric value  then you can use this to avoid loops :

 

Not tested: but I think it will work

Range.value=Evaluate("=if(Len(" & Range.address  & ")>0,Round(" &
Range.address &",2),"")")

 

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of tangledweb
Sent: Apr/Mon/2012 04:12
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Could this loop be replaced by some range method?

 

I am trying to get more used to using ranges instead of addressing
individual cells by indexes.  The loop below is rounding off the entries in
all the columns

where BarOpen for example is just the column index in the sheet.  Is there a
way to do this more efficiently by using the used range portion of each
column?

In this case all the columns are the same length.

 

 

'   Round off the raw data values
With Sheets(RawData)
For index = startrawdata To stoprawdata
Cells(index, BarOpen).Value =
WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
Cells(index, BarHigh).Value =
WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
Cells(index, BarLow).Value =
WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
Cells(index, BarClose).Value =
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
Cells(index, StopCol).Value =
WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
Next index
End With

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Re: Please help on basic Excel

[Soni.Rajender]

Hi
 
The below formulae can be also used for the same:
 
*=IF(J5<0,ABS(J5)," ")*
** 
ABS () - Returns the absolute value of a number, a number without its sign.
** 
Regards
Rajender Soni
 

On Sunday, April 8, 2012 11:09:10 PM UTC+5:30, Aruna jayaram wrote:

> Hi
>
> Attached is a file in which in the loss column ( M Column ) the amount 
> appears with the - ( Minus ) in the beginning. Kindly advise what can be 
> done by which the minus will not appear.
>
> Thanks and Regards
>
> Aruna
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ regarding pivot table problem

[Sanjib Chatterjee]

Dear Mr. Sourbh Salgotra,

I am already done it please check my attachment..  But I want rather
differently which
I mentioned.

Thanking you

Sanjib


On Mon, Apr 9, 2012 at 5:28 PM, Sourabh Salgotra wrote:

> plz check attachement
>
> On Mon, Apr 9, 2012 at 4:48 PM, Sanjib Chatterjee <
> chatterjee.kolk...@gmail.com> wrote:
>
>>
>> Dear Sir,
>> previously i sent you the mail.  But I got the error message that the
>> message I am sending
>> is cross the allocated limit so I am sending you the fresh message.
>>
>> Please see the attachment.  I have created the Pivot table based on the
>> data of sheet1.  Then
>> follow the step for creating pivot table.  But I want in same different
>> format.  In BS column
>> referred to buy and sale of scripts.  If there is B in the column then
>> quantity should be put
>> as buy qty and next buy rate.  If there is S in the column then quantity
>> should be put as
>> sale and next sale rate.  Net qty should be buy - sale quantity.
>>
>> Please help to solve the problem
>>
>> Thanking you in Advance.
>>
>> Sanjib
>>
>>
>> --
>> -
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> mujhay dukh is baat ka nahin kay meri zaat ko
> muntashir karny walay haath tairy thy
> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
> samaitnay walay haath tairy na thy
>
>
>
> 
>
>
> Thanks & Regards
> Sourabh
> Contact Numbers: +91-94630-49202
> Website:http://adhurapyaar.co.cc
>
>
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
-

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ regarding pivot table problem

[Sourabh Salgotra]

plz check attachement

On Mon, Apr 9, 2012 at 4:48 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

>
> Dear Sir,
> previously i sent you the mail.  But I got the error message that the
> message I am sending
> is cross the allocated limit so I am sending you the fresh message.
>
> Please see the attachment.  I have created the Pivot table based on the
> data of sheet1.  Then
> follow the step for creating pivot table.  But I want in same different
> format.  In BS column
> referred to buy and sale of scripts.  If there is B in the column then
> quantity should be put
> as buy qty and next buy rate.  If there is S in the column then quantity
> should be put as
> sale and next sale rate.  Net qty should be buy - sale quantity.
>
> Please help to solve the problem
>
> Thanking you in Advance.
>
> Sanjib
>
>
> --
> -
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
mujhay dukh is baat ka nahin kay meri zaat ko
muntashir karny walay haath tairy thy
mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
samaitnay walay haath tairy na thy





Thanks & Regards
Sourabh
Contact Numbers: +91-94630-49202
Website:http://adhurapyaar.co.cc

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Copy_of_EXAMPLE_VKGOEL.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Macro to Print the sheet in PDF and save in a folder.

[Excel_Lover]

Dear All,

Hope somebody can help me to create a macro to:

print the sheet in pdf,
name the PDF as cell value 'E4'
and save the same into location : C:\Users\FAISAL\Desktop\PRSD

Please find the attached sheet for your reference.

Thanks in advance for your valuable assistance.



On Sun, Apr 8, 2012 at 11:05 PM, Excel_Lover  wrote:

> Dear All,
>
> please find the below mentioned Macro which I recorded to print a sheet in
> PDF,
>
> Sub test()
>
> Application.ActivePrinter = "PDFCreator on Ne01:"
> ExecuteExcel4Macro _
> "PRINT(2,3,3,12,""PDFCreator on Ne01:"",,TRUE,,FALSE)"
> End Sub
> Can somebody please modify the macro which can save the created PDF file
> in a specified folder.
>
> Thanks in advance for your helps.
>
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


test.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ need excel dyanmic web query macro

[NOORAIN ANSARI]

Thanks Asa,

Very useful links,

-- 
Thanks & regards,
Noorain Ansari
 *http://noorainansari.com/*
*http://excelmacroworld.blogspot.com/*

On Sat, Apr 7, 2012 at 2:21 AM, Asa Rossoff  wrote:

> I get some kind of SQL server error from that tracking URL (perhaps
> because I'm not in India), but if the tracking info is in a table on the
> web page, you should be able to use a Web Query to integrate the info right
> into your worksheet.
>
> http://www.mrexcel.com/tip103.shtml
>
>
> https://office.microsoft.com/en-us/excel-help/get-and-analyze-data-from-the-web-in-excel-HA001054848.aspx
> 
>
>
> http://www.techrepublic.com/article/pull-data-into-microsoft-excel-with-web-queries/6115870
> 
>
> http://www.vertex42.com/News/excel-web-query.html
>
> http://www.jkp-ads.com/Articles/WebQuery.asp
>
> ** **
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Mohanraj shunmugam
> *Sent:* Friday, April 06, 2012 6:36 AM
>
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ need excel dyanmic web query macro
>
> ** **
>
> dear sir,
>
>
> i here attach my file its for india post registered letter status
>
> i have only one input data
>
> i am using "hyperlink"
>
> when i press search new webpage opened and show the status
>
> if there is any possibility to show the status table below  the search
> button within same exceland not opening browser page
>
> need our expert help
>
> thanking you
>
> regards,
>
> mohanraj s
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ count formula problem...

[NOORAIN ANSARI]

Dear Muneer,

Please use below formula in B2 and copy down

=SUMPRODUCT(--($A$2:$A$1393=A2))


On Sat, Apr 7, 2012 at 12:32 PM, Mohammed Muneer  wrote:

>  Please find the attachment.
>
> ** **
>
> ** **
>
> Regards,
>
> Muneer,
>
> CC...
>
> ** **
>
> ** **
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 ** *http://noorainansari.com/*
*http://excelmacroworld.blogspot.com/*

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Asa Rossoff]

> contangoindex = _
> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
RE: passing an array like this, without respect to using Intersect;

Since WorksheetFunction.Match accepts an actual range even though Help
describes the argument as an array, it may well perform best if you just
pass the range reference (no .value at the end).  That way it can decide how
to handle the data best for itself.  The most important issue was to limit
the size of the range properly.

-Original Message-
From: Asa Rossoff [mailto:a...@lovetour.info] 
Sent: Monday, April 09, 2012 2:35 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

> This works.  Is there a reason why it is not better?
Yes.  It won't always work.  RANGE.Columns is relative to that range (as is
RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet.  UsedRange
doesn't always start in A1.

> This is puzzling.  In the case of .rows(1) it can handle the range as
> the array but in this case you have to explicitly convert to an array.
Perhaps because in Excel 2007+ there are 16,384 columns, much less than the
million+ rows,
Your syntax might well work in prior versions of Excel, where there are just
65,536 rows.

> And then further down to this though not as easy to read and understand
>
> contangoindex = _
> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
>
> So what is happening here?  Is this still just by adding the .value
> converting the range into an array as some secret VBA thing where you
> are extracting the values out of the range and they have to go
> somewhere to it creates and array for them?
Once you specify the .Value property of a range larger than one cell, you
are specifying an array.  It doesn't matter if you don't create a variable
to refer to it.  If you read the Help entry for WorksheetFunction.Match
you'll see that the data type for all the arguments is Variant.  Your array
will be stored in memory as an array of Variant (the .Value property is
Variant) and stored inside another Variant (full name "Variant Array of
Variant"), and a pointer to it will be passed to WorksheetFunction.Match.
When Match is done with it's work, the array will be destroyed from memory
since there are no other pointers to it.  If you had a need to refer to it
again, it would be a very good idea to create the variable first and not
have VBA waste energy creating and destroying the whole array multiple
times.

> You are right though.  Once you find a working bone you can keep
> gnawing on it until down to minimal shard that still functions.
It can help in understanding what's what.

> When you use SET to create pointers they can only be done inside a
> Sub.  If you want to use them in another SUB do you have to do the SET
> again or if in the same module will the SET be know by other Subs?
It's not the Set statement that matters for your question.  All variables,
as well as procedures (be they Sub or Function procedures), and modules,
have a certain scope within which they can be referred to.  What determines
the scope, in the case of a variable, is where it is declared, what
statement was used to declare it, and whether it is passed ByRef to any
other Sub or Function (although in the last case the other procedure would
have it's own local name for the variable, even though it points to the same
memory location).

Variables can be declared at the module level as:
  Public varname As vartype ' uses global or optionally workbook scope
  Private varname As vartype ' uses module scope
  Dim varname As vartype ' uses module scope

In a procedure they can be declared:
  Dim varname As vartype ' uses local (procedure) scope
  Static varname as vartype ' uses local (procedure) scope and retains value
between calls
  
Then use Set if you are assigning an object to a variable, or use Let or the
usual shorthand of varname=value if you are assigning any other data type.

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Sunday, April 08, 2012 1:36 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

This works.  Is there a reason why it is not better?

I did your array replacement but I changed this

Set InputRange =
Application.Intersect(Sheets(ContangoSource).UsedRange,
Sheets(ContangoSource).Columns(ConDate))

with this

Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)

but it does not work to get rid of the array replacement and go all
the way to this

contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2,
BarDate).Value, Sheets(ContangoSource).UsedRange.Columns(ConDate), 0)

This is puzzling.  In the case of .rows(1) it can handle the r

RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Asa Rossoff]

> This works.  Is there a reason why it is not better?
Yes.  It won't always work.  RANGE.Columns is relative to that range (as is
RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet.  UsedRange
doesn't always start in A1.

> This is puzzling.  In the case of .rows(1) it can handle the range as
> the array but in this case you have to explicitly convert to an array.
Perhaps because in Excel 2007+ there are 16,384 columns, much less than the
million+ rows,
Your syntax might well work in prior versions of Excel, where there are just
65,536 rows.

> And then further down to this though not as easy to read and understand
>
> contangoindex = _
> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _
> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)
>
> So what is happening here?  Is this still just by adding the .value
> converting the range into an array as some secret VBA thing where you
> are extracting the values out of the range and they have to go
> somewhere to it creates and array for them?
Once you specify the .Value property of a range larger than one cell, you
are specifying an array.  It doesn't matter if you don't create a variable
to refer to it.  If you read the Help entry for WorksheetFunction.Match
you'll see that the data type for all the arguments is Variant.  Your array
will be stored in memory as an array of Variant (the .Value property is
Variant) and stored inside another Variant (full name "Variant Array of
Variant"), and a pointer to it will be passed to WorksheetFunction.Match.
When Match is done with it's work, the array will be destroyed from memory
since there are no other pointers to it.  If you had a need to refer to it
again, it would be a very good idea to create the variable first and not
have VBA waste energy creating and destroying the whole array multiple
times.

> You are right though.  Once you find a working bone you can keep
> gnawing on it until down to minimal shard that still functions.
It can help in understanding what's what.

> When you use SET to create pointers they can only be done inside a
> Sub.  If you want to use them in another SUB do you have to do the SET
> again or if in the same module will the SET be know by other Subs?
It's not the Set statement that matters for your question.  All variables,
as well as procedures (be they Sub or Function procedures), and modules,
have a certain scope within which they can be referred to.  What determines
the scope, in the case of a variable, is where it is declared, what
statement was used to declare it, and whether it is passed ByRef to any
other Sub or Function (although in the last case the other procedure would
have it's own local name for the variable, even though it points to the same
memory location).

Variables can be declared at the module level as:
  Public varname As vartype ' uses global or optionally workbook scope
  Private varname As vartype ' uses module scope
  Dim varname As vartype ' uses module scope

In a procedure they can be declared:
  Dim varname As vartype ' uses local (procedure) scope
  Static varname as vartype ' uses local (procedure) scope and retains value
between calls
  
Then use Set if you are assigning an object to a variable, or use Let or the
usual shorthand of varname=value if you are assigning any other data type.

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Sunday, April 08, 2012 1:36 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

This works.  Is there a reason why it is not better?

I did your array replacement but I changed this

Set InputRange =
Application.Intersect(Sheets(ContangoSource).UsedRange,
Sheets(ContangoSource).Columns(ConDate))

with this

Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)

but it does not work to get rid of the array replacement and go all
the way to this

contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2,
BarDate).Value, Sheets(ContangoSource).UsedRange.Columns(ConDate), 0)

This is puzzling.  In the case of .rows(1) it can handle the range as
the array but in this case you have to explicitly convert to an array.

But it can be further whittled down to this

CellsOfInterest = Sheets(ContangoSource).UsedRange.Columns(ConDate).Value
without these lines

'Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate)
'CellsOfInterest = InputRange.Value

And then further down to this though not as easy to read and understand

contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2,
BarDate).Value,
Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0)

So what is happening here?  Is this still just by adding the .value
converting the range into an array as some secret VBA thing where you
are extracting the values out
of the range and they have to go somewhere to it creates and array 

RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?

[Asa Rossoff]

I'm not comparing the approach your code took to find a match with that
Microsoft's worksheetfunction.match code takes, I'm saying that VBA code has
much more overhead and less efficiency than fully compiled code, such as
Excel's own code is.  Once Excel gets to work at whatever you asked it to do
with your VBA statement, it will happen at a healthy clip.  But interpreting
each line of code takes time.  So, one line of code that uses
worksheetfunction.match has 1x overhead amount, plus actual time to do the
work.  A 5-line loop (for example) that does the same work, and loops 1000
times, has 5x1000=5000 times the overhead, plus the actual time to do the
work.  More or less.  Also, MS has the advantage in their own code of being
able to accomplish certain tasks more efficiently than you can given the
limits of the VBA language and object model.

I understand.. your task and goals, and mine, in our discussions, are likely
not exactly the same.  For me, time allowing, I am 99% interested in
education, and 1% in your completing your task :)... In fact, I'm even more
interested in educating the general public through any one person's example
than I am in educating that one person.  I'm making an example of you :)
Tasks do need completion, though, I realize!  You can't necessarily learn
everything in one amd rush anyway.. It's even sensible to start with the
foundation and coding style you already have, and build from there, as you
are.

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 10:55 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?

Not sure I follow that.  Unless it creates an indexed database that it
uses internally or creates hash tables or some other fast indexing
then the match function still has to
test all the entries until it finds a match.  If I write it directly
in machine language that is still the case.  That indexing is overhead
but if only done once and used repeatedly then it will be more
efficient.  Speed is not an issue for what I am doing.  No run is ever
likely to take more than a couple of minutes but just by background I
still like to create the best code I can.  I am just afraid I can not
afford the time to learn to do so as getting this running quickly is
the highest priority.  I will still document it well and write it as
well as I can but I am sure that will mean my style is most likely to
be more procedural than object oriented to its best use. Unfortunate
but reality.

I am sure I will call on you again though as you have been quite
helpful and no good deed ever goes unpunished :)

On Sat, Apr 7, 2012 at 10:22 PM, Asa Rossoff  wrote:
> It was type mismatch
>
> Oh, well.  VBA is interpreted (when compiled it compiles to a bytecode,
not
> executable code) so as a matter of practice it's good to avoid loops when
> there are good alternatives, but if the speed is not an issue in your case
> whatever works.
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Saturday, April 07, 2012 10:14 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
> spreadsheet?
>
> When you did not use the intersection was the error unable to find the
> match function?
>
> In any case
>
>    contangoindex = 1
>    Do While Sheets(RawData).Cells(2, BarDate).Value <>
> Sheets(ContangoSource).Cells(contangoindex, ConDate).Value
>        contangoindex = contangoindex + 1
>    Loop
>
> works, is a lot easier to read code, and probably as efficient as you
> can get so I think I will stop chasing this greased pig.
> Thanks for all the help.  I will still go back and look through the
> debug links and look for the other references you mentioned.
>
> On Sat, Apr 7, 2012 at 10:04 PM, Asa Rossoff  wrote:
>> I tried this little variation:
>> Function matchtest() As Long
>>    Dim contangoindex As Long
>>
>>    Dim CellsOfInterest As Variant
>>    Dim InputRange As Range
>>    Set InputRange = Application.Intersect(Sheet2.UsedRange,
>> Sheet2.Columns(1))
>>    CellsOfInterest = InputRange.Value
>> '   Determine where the index in ContangSource is of the first date that
>> matches the first date in RawData
>>    contangoindex = WorksheetFunction.Match(Sheet1.Cells(2, 1).Value,
>> CellsOfInterest, 0)
>>
>>    matchtest = contangoindex
>> End Function
>>
>> then in immediate:
>> ? matchtest
>>
>> It worked fine.
>> In the first sheet I put the formula =TODAY() in A2
>> In the second sheet I put the formula =TODAY()-10+ROW() in A1 and copied
>> down to A24
>>
>> The result in Immediate:
>> 10
>>
>>
>> The function did not work without Application.Intersect though --- the
> array
>> was just too large and Match balked.
>>
>> In some instances with a full column range argument it did wo