Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    213
    Thanks
    30
    Thanked 4 Times in 4 Posts

    How to find attributes of ".cell" I can use?

    Specifically:

    A cell has the formula "=1+2+3". I want to copy this to another cell. If I just use ".cell(1,3) = .cell(1,2)" I get the value "6". I want to copy the formula. How do I do that?

    [Edit - I used .Formula and that worked]

    Generally, where do I go to find a list of all the attributes I can use (like ".cell(x,y).filldown" etc) ?

    Thanks for any info

    Alan
    Last edited by alan sh; 2014-01-29 at 03:26.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    If you don't put explicitly use a property the ".Value" property is implicitly assigned.

    If you have the VBA option to "AutoList" members, when you enter an object (and variables will also work if they are DIMmed, so requiring "variable declaration" is also recommended) when you enter a period a list of the properties and methods are shown.

    In helping to find the correct one, using the macro recorder is often useful for seeing what properties are used.

    Also stepping through the code and looking at the various properties of the variables in the "Locals window" at different times in the program can also be informativeYou can see the difference between the formula, formular1c1, value, text, value2, etc properties (sometimes they are the same value in each, other times they are different). [One good example to see the differences is to put a formula in cell referencing other cells that returns a date and format the cell to display a date. The properties I listed previously will be different for that example and which one you want depends on what you want to do]

    Here is a link to MS Excel 2007 object reference: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx You can drill down the various objects in the model and see the properties and methods with some description and examples.

    Searching in the help can also provide answers and examples.

    Steve

  4. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    213
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Steve, thanks. I have Autolist enabled, but it doesn't seem to do anything when I put a dot after .cell(x,y) - does it work for you?
    Also, thanks for the other suggestions.

    Alan

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    I don't believe .cell(x,y) is valid, it should be .cellS(x,y)

    But even that will not display the autolist, since cells is not a unique object, it can be something other than a worksheet range object, and excel needs to know what it is before an appropriate autolist will be displayed. You can use the .Range("Yx")-type naming or create a range variable [eg: dim rng as range] and then rng followed by a period will have the autolist. You can use teh range object in the code by setting the variable [eg: set rng = .cells(x,y)]

    One tip: if you use all lowercase to enter the properties and methods, after the line is compiled it should capitalize the appropriate letters. If there are no caps added, the property or method is probably not valid [If you dim variables with upper and lower cases in the name, the tip also works for ensuring the variable name you type is not misspelled or non-existent]

    Steve

    Steve

  6. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    213
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Thanks again Steve - yes it was a typo - should be .cells()

    I use the auto-capitalisation a lot - also with any public variables I use - helps me know that I have got the right name.

    Cheers

    Alan
    Last edited by alan sh; 2014-01-29 at 10:12.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    Another tip with variable names is to use some type of naming convention (eg http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx) to indicate what the variable type is. That and the name can make it much easier to debug and follow the code (especially years later when you may forget the logic you used when you originally wrote it)

    Steve
    Last edited by sdckapr; 2014-01-29 at 10:15. Reason: Fixed link

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,838
    Thanks
    186
    Thanked 708 Times in 646 Posts
    Quote Originally Posted by sdckapr View Post
    Another tip with variable names is to use some type of naming convention (eg http://msdn.microsoft.com/en-us/libr...v=vs.60).aspx) to indicate what the variable type is. That and the name can make it much easier to debug and follow the code (especially years later when you may forget the logic you used when you originally wrote it)
    Steve
    Years! Did he say YEARS!

    For me at least it's usually DAYS! YMMV
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    I have had the issues days later with some code, but if can recall the issues in days after, imagine the exponentially increased issues when it is years later.

    And I have revisisted code that I wrote for someone years later (it was been to revamp or modify it due to changes the make in the source workbooks that the code no longer is applicable for).

    I don't know if it is apparent, but I am not a programmer by training: I am a chemist and I have learned most through playing and answering questions on this board (and its earlier incarnation). The scary part is that if it has been far enough in the past, I cringe at some of the code I had written in my younger less-experienced days...

    Take care,
    Steve

  10. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    213
    Thanks
    30
    Thanked 4 Times in 4 Posts
    I have a rule that I have at least 1 line of comments for every 3 lines of code. Plus I do use meaningful (to me) names.

    Alan

    PS - Content not found on that link.

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    Content not found on that link
    Sorry. The end parenthesis was placed as part of the link instead of outside the line. It has been corrected in the original post.

    Thanks for pointing it out.

    Steve

  12. The Following User Says Thank You to sdckapr For This Useful Post:

    alan sh (2014-02-04)

  13. #11
    New Lounger
    Join Date
    Aug 2013
    Posts
    12
    Thanks
    11
    Thanked 1 Time in 1 Post
    Would placing the cursor at the next line and pressing Ctrl-J help?

    Lexi
    Last edited by Lexi; 2014-02-01 at 20:33.

  14. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    I believe ctrl-J on a blank line just lists the properties/methods available for a blank line. It won't display the properties/methods for a range object unless you have a range object and period. The "autolist" option essentially automatically does what ctrl-j does. If autolist can't create a list, ctrl-j will not either.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •