RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Intellisense would have to create the object using the string you specified to know what it would get, and evaluate your code to determine what the string variable's value would be at that point,... it's not quite as intelli- as that. You might consider using worksheet codenames when you are refer

Re: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Things that make you say hmm. That really makes no sense to me. there is something else other than a worksheet that sheets can return? Especially when results is already specified as the name of sheet2? Weird. But I understand what you say. Sheets(Results).UsedRange.Offset(1).ClearContents

RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

woohoo :D I think I will. Signed in to relax on the forums a bit, and now I think I will relax in some other way and then hit the sack. Have fun and take care, Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent

RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Try dim ws as worksheet set ws=sheets(results) ws.UsedRange.Offset(1).ClearContents I get full intellisense that way. Sheets is ambiguous as to what object will be returned (worksheet/chart) so intellisense can't know what properties and methods to suggest to you. However, I would think worksh

Re: $$Excel-Macros$$ How do you find all the properties or methods of an object?

The program compiles and runs with no errors so I assume no syntax errors. But I did the compile test anyway and no errors (or any other indication it actually did anything). Anyway you got me on the right track, thanks once again. I think you can take the rest of the night off. On Thu, Apr 5,

RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

> I have had intellisense work sometimes and others not so I guess I must not have had early binding for those that failed. If there is a syntax error in your code it can disable intellisense too. Debug menu > Compile will verify you have no syntax errors bugging VBA. -Original Message

Re: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Reading the binding stuff harkens back to days of yore when we had to first compile then separately link the programs and the former gives you the syntax problems and the latter the missing library elements, outside objects, etc. I still though do not know why when I enter ActiveCell.Value = shor

RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Intellisense should give you the appropriate object members when you refer to UsedRange and other similar properties that return one specific type of object. Intellisense gives you nothing for Activesheet members because Activesheet can return more than one kind of object (Chart, Worksheet). You c

Re: $$Excel-Macros$$ How do you find all the properties or methods of an object?

My help already set to offline. Now I see what you mean though. Instead of looking up usedrange I looked up range which gave me the list of properties I wanted. It seems absurd though not to be able to type in usedrange and get all the associated properties. Trying the object browser again I can

RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

ActiveSheet is not the name of an object. It's the name of a property of several objects: Application, Window, and Workbook. Application is basically a default object, so unqualified ActiveSheet refers to Application.ActiveSheet. The description for all the Activesheet properties include

Re: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Thanks much as always. I did find the object browser but was unable to get to the next layer down on anything I found. I will try again. I have had intellisense work sometimes and others not so I guess I must not have had early binding for those that failed. I will read up on it. Never would ha

RE: $$Excel-Macros$$ How do you find all the properties or methods of an object?

Hey Howard, They are all listed in Help. Make sure to disable online help* for the help function to operate in a reasonable way and to find all the reference material. Also, make sure you have the table of contents showing. If you open Help from within the VBE Visual Basic Editor, you should ha

$$Excel-Macros$$ How do you find all the properties or methods of an object?

If I want to find all the properties (if that is the correct word) for an object how do I do it? Excel help just gives simple examples. In the ones I wanted intellisense did not help. Right mouse on the word gave an option to list all properties and methods but when clicked nothing happened.

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

Never mind I figured out the syntax. Can be altered to cover all the columns this way. Sheets(Results).Range("A2:D" & ActiveSheet.UsedRange.Rows.Count).ClearContents Thanks for the assist. On Thu, Apr 5, 2012 at 5:14 PM, Domain Admin wrote: > Can you please explain this syntax?  It appears th

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

Can you please explain this syntax? It appears th part after the & extends the range statement somehow but I do not understand how. On Thu, Apr 5, 2012 at 5:08 PM, Maries wrote: > For other columns, > > Sub Macro1() > Sheets("Results").Range("A2:A" & > ActiveSheet.UsedRange.Rows.Count).EntireRow

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

For other columns, Sub Macro1() Sheets("Results").Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).EntireRow.Clear End Sub On Fri, Apr 6, 2012 at 4:03 AM, Domain Admin wrote: > And would offset(0,1) mean skip the first column? > > On Thu, Apr 5, 2012 at 5:01 PM, Domain Admin > wrote: > > That

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

And would offset(0,1) mean skip the first column? On Thu, Apr 5, 2012 at 5:01 PM, Domain Admin wrote: > That works, thanks.  How do you find out all the properties or > functions or whatever of usedrange etc.? > > On Thu, Apr 5, 2012 at 4:47 PM, dguillett1 wrote: >> >> Should do it >> >> sub cle

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

That works, thanks. How do you find out all the properties or functions or whatever of usedrange etc.? On Thu, Apr 5, 2012 at 4:47 PM, dguillett1 wrote: > > Should do it > > sub clearallbuttoprow() > Sheets("Results").UsedRange.Offset(1).Clear > End Sub > > Don Guillett > Microsoft MVP Excel > S

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

That clears column A but not the other columns. Also can you explain this syntax? I assume the part of the range statement in quotes specifies column 2 from row 2 down? But I am not sure what the & ... is doing. I am guessing it returns the number of rows in the used range and that is needed to m

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

Should do it sub clearallbuttoprow() Sheets("Results").UsedRange.Offset(1).Clear End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Thursday, April 05, 2012 6:29 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ how to clear all

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

Try it now,* Sheets("Results").Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).Clear* On Fri, Apr 6, 2012 at 3:42 AM, Domain Admin wrote: > That clears the header row too. I need to keep that row intact. > > On Thu, Apr 5, 2012 at 4:36 PM, Maries wrote: > > Try it, > > > > Sheets("Results").

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

That clears the header row too. I need to keep that row intact. On Thu, Apr 5, 2012 at 4:36 PM, Maries wrote: > Try it, > > Sheets("Results").Cells.ClearContents > > > > On Fri, Apr 6, 2012 at 3:29 AM, tangledweb wrote: >> >> Results > > > -- > FORUM RULES (986+ members already BANNED for viola

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

*Sheets("Results").Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).Clear* On Fri, Apr 6, 2012 at 3:36 AM, Maries wrote: > Try it, > > Sheets("Results").Cells.ClearContents > > > > On Fri, Apr 6, 2012 at 3:29 AM, tangledweb wrote: > >> Results > > > -- FORUM RULES (986+ members already BANNE

Re: $$Excel-Macros$$ how to clear all but the header row efficiently

Try it, Sheets("Results").Cells.ClearContents On Fri, Apr 6, 2012 at 3:29 AM, tangledweb wrote: > Results -- 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 Probl

$$Excel-Macros$$ how to clear all but the header row efficiently

Before running a program that puts the results onto a sheet I want to clear that sheet from the previous run. Easy to just clear a huge range, but really wanted to do it right and clear the used range. I tried to find a way to use the usedrange value but could not determine how to exclude the h

Re: $$Excel-Macros$$ V LOOKUP PROBLEM

No need Value formula, It can do it, =SUMIF($C$2:$C$113,A2,$D$2:$D$113) On Fri, Apr 6, 2012 at 12:51 AM, Maries wrote: > Hi, > > Try below formula, > > =SUMIF($C$2:$C$113,VALUE(A2),$D$2:$D$113) > > Regards, > > MARIES. > > > > On Thu, Apr 5, 2012 at 11:20 PM, Munir Ahmad wrote: > >> Hi Group,

Re: $$Excel-Macros$$ Call a Function

please see... Sub or Function not defined (Visual Basic) A *Sub* or *Function* must be defined in order to be called. Possible causes of this error include: - Misspelling the procedure name. - Trying to call a procedure from another project without explicitly adding a reference

Re: $$Excel-Macros$$ Call a Function

Please attach example, to better understand Chilexcel 2012/4/5 Matt > Hello, I'm trying to call a public function in one module from another Sub > procedure in another module and it won't call the the function. I get an > error that says.."sub function not defined" But when I include the funct

$$Excel-Macros$$ Call a Function

Hello, I'm trying to call a public function in one module from another Sub procedure in another module and it won't call the the function. I get an error that says.."sub function not defined" But when I include the function within the same module, my code works... I can't seem to find the answ

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Thanks really appreciate it. Sent from my BlackBerry® smartphone from Airtel Ghana -Original Message- From: "Asa Rossoff" Sender: excel-macros@googlegroups.com Date: Thu, 5 Apr 2012 11:22:28 To: Reply-To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Amount of words in Re

Re: $$Excel-Macros$$ Extract desired data

thanks Jaysheel \ Waseem \ MARIES \ Rajan regards, Aamir Shahzad On Wed, Apr 4, 2012 at 1:22 PM, Rajan_Verma wrote: > Hi > > Please find the attached sheet’ > > =IF(ISNUMBER(INT(RIGHT(C2,6))),INT(RIGHT(C2,6)),"") > > > > > > Rajan > > > > *From:* excel-macros@googlegroups.com [mailto: > excel-

RE: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Hi Hilary, You seem to already have the answer :) If you assign the following macro to your listbox, the text in the callout will be updated correctly: Sub ListBox1_Change() Dim wb As Workbook Set wb = ThisWorkbook With wb.Worksheets("Ratio") .Shapes("Rectangular Callout 1

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

OK. I see but I thought desire was wo code BTW, textbox should be set to resize to text Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: hilary lomotey Sent: Thursday, April 05, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Amount of

$$Excel-Macros$$ to remove time

CTRL+1 --->Custom format , choose the one you need. -- 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$$ to remove time

See the attached sheet.. There are three solution and I think yellow one best. Provided by xlstime. Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Apr/Thu/2012 10:10 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macr

Re: $$Excel-Macros$$ new

banned from the forum. if you see any such email, Please report @jainayus...@gmail.com Thanks On Thursday, 5 April 2012 09:13:46 UTC+1, Asa R. wrote: > Is that what that was? It went right over my head! :) > > > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.

RE: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Its' also strange for me also.. y it's not taking all text when using formulas.. but it works fine through VBA.. Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of hilary lomotey Sent: Apr/Thu/2012 09:19 To: excel-macros@googlegroups.com Subject: R

Re: $$Excel-Macros$$ to remove time

Not much info but you can probably change the format. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: vijayajith VA Sent: Thursday, April 05, 2012 10:54 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ to remove time Hi , 12/2/2001 12:00 PM

Re: $$Excel-Macros$$ to remove time

HI Vijay, You can use very simple formula '=int(your date) Int is basically use for Rounds a number down to the nearest integer. On Thu, Apr 5, 2012 at 9:24 PM, vijayajith VA wrote: > Hi , > > 12/2/2001 12:00 PM --- How to remove time ? i need only date .. is > thr any formula ..Th

Re: $$Excel-Macros$$ to remove time

Thanks Noorain ... & Mareshwaran On Thu, Apr 5, 2012 at 9:36 PM, NOORAIN ANSARI wrote: > Dear Vijay, > > Please try it..after selection the required range.. > > Sub remove_Time() > Dim mycell As Range > For Each mycell In Selection > if vba.isdate(mycell) then > mycell.Value = VBA.Left(mycell, VB

Re: $$Excel-Macros$$ to remove time

Dear Vijay, Please try it..after selection the required range.. Sub remove_Time() Dim mycell As Range For Each mycell In Selection if vba.isdate(mycell) then mycell.Value = VBA.Left(mycell, VBA.InStr(1, mycell, " ") - 1) Selection.NumberFormat = "m/d/" endif Next End Sub On Thu, Apr 5, 20

Re: $$Excel-Macros$$ to remove time

*Try it, =TEXT(INT(A1),"DD/MM/")* On Thu, Apr 5, 2012 at 7:54 PM, vijayajith VA wrote: > Hi , > > 12/2/2001 12:00 PM --- How to remove time ? i need only date .. > is thr any formula ..Thanks in advance > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use conci

$$Excel-Macros$$ to remove time

Hi , 12/2/2001 12:00 PM --- How to remove time ? i need only date .. is thr any formula ..Thanks in advance -- 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

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

I have seen the code he used, i think its Sub ListBox1_Change() Sheet2.Shapes("shpMyShape").TextFrame.Characters.Text = Sheet1.Range("A24") End Sub On Thu, Apr 5, 2012 at 3:20 PM, dguillett1 wrote: > How? > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > > *

Re: $$Excel-Macros$$ Need assistance in finding formula to substitute the values to dates.

Thanks a lot Marie ... On Wed, Apr 4, 2012 at 11:56 PM, Haseeb A wrote: > Good One Maries :) > > As you know OFFSET is volatile. A non volatile formula, > > > =IFERROR(INDEX($B$4:$E$4,MATCH(B$9,INDEX($B$5:$E$7,MATCH($A10,$A$5:$A$7,0),0),0)),"") > > ___ > HTH, Haseeb > > -- > FORUM RULES

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

How? Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Rajan_Verma Sent: Thursday, April 05, 2012 8:03 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem Hi please find the attached sheet Rajan

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Rajan, thanks Raj, its working just the way i want it but did you use macros? pls run me thru how you did it. thanks On Thu, Apr 5, 2012 at 3:01 PM, hilary lomotey wrote: > thanks Don > > > > On Thu, Apr 5, 2012 at 2:42 PM, dguillett1 wrote: > >> Use cell instead due to text box limit >> >>

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

thanks Don On Thu, Apr 5, 2012 at 2:42 PM, dguillett1 wrote: > Use cell instead due to text box limit > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > > *From:* hilary lomotey > *Sent:* Thursday, April 05, 2012 6:51 AM > *To:* excel-macros > *Subject:* $

Re: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Use cell instead due to text box limit Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: hilary lomotey Sent: Thursday, April 05, 2012 6:51 AM To: excel-macros Subject: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem Good Afternoon Experts In the

RE: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Hi please find the attached sheet Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of hilary lomotey Sent: Apr/Thu/2012 05:21 To: excel-macros Subject: $$Excel-Macros$$ Amount of words in Rectangular Shape box problem Good Afternoon Experts

RE: $$Excel-Macros$$ How to Delete End (Shift Enter) - Macro Needed

Hi I did not found any line removed in output file.. Can you please explain. Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Patil MG Sent: Apr/Thu/2012 11:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ How to Delete End (Shif

$$Excel-Macros$$ Amount of words in Rectangular Shape box problem

Good Afternoon Experts In the file attached, i am trying to achieve a situation where if i click one of the members in the list box , the interpretation of the of the word would appear in the rectangular shape box. the problem is that , i cant seem to see all the explanation in the rectangular sha

$$Excel-Macros$$ Re: Is this site (http://www.ozgrid.com/Excel/free-training/basic-index.htm) is ok for beginner.

Dear Lakshman, Ozgrid is a very useful website and it is good for beginner. Below mentioned web link will also helpful to learn VBA. http://www.java2s.com/Code/VBA-Excel-Access-Word/CatalogVBA-Excel-Access-Word.htm -- Thanks & regards, Noorain Ansari *http://noorainansari.com/* *http://excel

$$Excel-Macros$$ Is this site (http://www.ozgrid.com/Excel/free-training/basic-index.htm) is ok for beginner.

Dear Experts and Noorian, Is this site (http://www.ozgrid.com/Excel/free-training/basic-index.htm) is ok for beginner. Regards LAKSHMAN -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, F

Re: Fwd: $$Excel-Macros$$ VBA Classes

where , On Thu, Apr 5, 2012 at 3:08 PM, LAKSHMAN PRASAD wrote: > Delhi NCR > > > *From:* Veeru TOC > *To:* excel-macros@googlegroups.com > *Sent:* Thursday, April 5, 2012 12:57 PM > *Subject:* Re: Fwd: $$Excel-Macros$$ VBA Classes > > Venue,,, Plz,,, > > On Thu, Apr 5, 2012 at 12

Re: Fwd: $$Excel-Macros$$ VBA Classes

Any one giving classes @ Borivali Mumbai? Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -Original Message- From: LAKSHMAN PRASAD Sender: excel-macros@googlegroups.com Date: Thu, 5 Apr 2012 02:38:25 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subjec

Re: Fwd: $$Excel-Macros$$ VBA Classes

Delhi NCR   From: Veeru TOC To: excel-macros@googlegroups.com Sent: Thursday, April 5, 2012 12:57 PM Subject: Re: Fwd: $$Excel-Macros$$ VBA Classes Venue,,, Plz,,, On Thu, Apr 5, 2012 at 12:30 PM, LAKSHMAN PRASAD wrote: me tooo > > > >From: Lokesh Loki

Re: $$Excel-Macros$$ format cell

Dear Pawel, You can also use VBA Approach.. select required range and run this macro.. Sub Format_Cell() For Each cell In Selection Selection.NumberFormat = """R""@" Next cell End Sub -- Thanks & regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*

RE: $$Excel-Macros$$ new

Is that what that was? It went right over my head! :) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: Thursday, April 05, 2012 1:09 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ new Dear Gemd, Please read be

Re: Fwd: $$Excel-Macros$$ VBA Classes

Venue,,, Plz,,, On Thu, Apr 5, 2012 at 12:30 PM, LAKSHMAN PRASAD wrote: > me tooo > > *From:* Lokesh Loki > *To:* excel-macros@googlegroups.com > *Sent:* Wednesday, April 4, 2012 7:27 PM > *Subject:* Fwd: $$Excel-Macros$$ VBA Classes > > Hi Experts, > > May i know that is there any Excel &

Re: Fwd: $$Excel-Macros$$ VBA Classes

me tooo From: Lokesh Loki To: excel-macros@googlegroups.com Sent: Wednesday, April 4, 2012 7:27 PM Subject: Fwd: $$Excel-Macros$$ VBA Classes Hi Experts, May i know that is there any Excel & Power point Vba classes conducting in weekend through online on th