+ Reply to Thread
Results 1 to 4 of 4

ActiveSheet Range not working.

Hybrid View

  1. #1
    Registered User
    Join Date
    08-18-2008
    Location
    Brazil
    Posts
    3

    ActiveSheet Range not working.

    Im getting the "Object doesn't support this property or method" Error.
    I have no idea why.

    Here's the code:

    Private Sub Statistics_Click()
        With ActiveSheet
            .Range("D2").Formula = "=COUNT(" & ActiveWindow.Selection.Adress & ")"
            .Range("D3").Formula = "=MIN(" & ActiveWindow.Selection.Adress & ")"
            .Range("D4").Formula = "=MAX(" & ActiveWindow.Selection.Adress & ")"
            .Range("D5").Formula = "=SUM(" & ActiveWindow.Selection.Adress & ")"
            .Range("D6").Formula = "=AVERAGE(" & ActiveWindow.Selection.Adress & ")"
            .Range("D7").Formula = "=STDEV(" & ActiveWindow.Selection.Adress & ")"
        
            
            .Range("C2").Value = "Count:"
            .Range("C3").Value = "Min:"
            .Range("C4").Value = "Max:"
            .Range("C5").Value = "Sum:"
            .Range("C6").Value = "Average:"
            .Range("C7").Value = "Stan Dev:"
            .Range("C2:D7").Select
        End With
        With Selection
            .Borders.Weight = x1Thin
            .Columns.AutoFit
        End With
        Range("A1").Select
    End Sub
    Best.
    Last edited by magni; 09-03-2008 at 04:48 PM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    The word address is spelt wrong. Missing the d

    ActiveWindow.Selection.Adress
    Also you don't need the select lines?

    Lastly you could change this
    .Range("C2").Value = "Count:"
            .Range("C3").Value = "Min:"
            .Range("C4").Value = "Max:"
            .Range("C5").Value = "Sum:"
            .Range("C6").Value = "Average:"
            .Range("C7").Value = "Stan Dev:"
            .Range("C2:D7").Select
    to
            .Range("C2:C7").Value = Application.Transpose _
            (Array("Count:", "Min:", "Max:", "Sum:", "Average:", "Stan Dev:"))
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    .Range("D2").Formula = "=COUNT(" & Selection.Address & ")"

  4. #4
    Registered User
    Join Date
    08-18-2008
    Location
    Brazil
    Posts
    3
    Woah!!
    Thanks guys so much

    I'm just beginning to learn VBA, so I still don't know the most elegant ways to do this..

    Thanks a lot for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If Statement in 'For' Statement - What's wrong?
    By dugong in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-16-2008, 12:22 PM
  2. Data in wrong cells
    By rskinner in forum Excel General
    Replies: 2
    Last Post: 02-20-2008, 01:05 PM
  3. "inconsistent formula" error!
    By grannygamer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-14-2007, 03:33 AM
  4. Bypass the run time error if user enters a wrong value
    By Richard Flame in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2007, 11:26 AM
  5. What's gone wrong with this one?
    By BCB in forum Excel General
    Replies: 10
    Last Post: 10-04-2006, 10:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1