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

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

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

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

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

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 n

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

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 ma

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

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

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

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 c

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

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 o

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

> 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

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

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,

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

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 fi

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

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

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

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 Ap

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

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).worksheet

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

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 thi

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

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

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

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

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

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

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 pro

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

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

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

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 o

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

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

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 ra

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

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 me

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

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-

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 Mess

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

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

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

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

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 functio

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

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@

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

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

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

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

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

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

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

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

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 >

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 yo

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

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

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

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 >

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 dis

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

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

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

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

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

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

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

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

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 exi

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

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 an

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

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 an

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

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

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.

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

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 wksSh

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

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 sh

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

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

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

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

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

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

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

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

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 S

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

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

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

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

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

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

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 ran

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

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