Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheetfunction (VB for Excel XP)

    Is there something wrong with this command?

    Selection.Formula = Application.WorksheetFunction.CountA(Range(cel), Sheets("Pascale''s Doc.").Range("H:H"))

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheetfunction (VB for Excel XP)

    Yes irt appears to have a rogue " sign in Pascales Doc. Try renaming it
    Jerry

  3. #3
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheetfunction (VB for Excel XP)

    How should I rename it?

    What sign should be removed?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheetfunction (VB for Excel XP)

    My Top Tip for any renaming is to change the name of your sheet to PascalsDoc without spaces so:

    Selection.Formula = Application.WorksheetFunction.CountA(Range(cel), Sheets("Pascale''s Doc.").Range("H:H"))

    becomes

    Selection.Formula = Application.WorksheetFunction.CountA(Range(cel), Sheets("PascalesDoc").Range("H:H"))
    Jerry

  5. #5
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheetfunction (VB for Excel XP)

    Hi,

    It keeps giving me
    Method 'Range' of object '_Global' failed

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheetfunction (VB for Excel XP)

    Can you provide a copy of the spreadsheet for me to look at, blanking out any sensitive data, I would like to see the whole code if possible.
    Jerry

  7. #7
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheetfunction (VB for Excel XP)

    Here is a portion of the document. There is a lot of code, but only the last sub is the one that isn't working.

    Thank you Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheetfunction (VB for Excel XP)

    Jezza is offline at the moment. The problem is in

    Selection.Formula = Application.WorksheetFunction.CountA(Range(cel), Sheets("Pascale''s Doc.").Range("H:H"))

    cel is already a Range variable, so Range(cel) isn't valid. If you change it to

    Selection.Formula = Application.WorksheetFunction.CountA(cel, Sheets("Pascale''s Doc.").Range("H:H"))

    it'll work, although it probably won't do what you want.. My guess is that you want to do this:

    Selection.Value = Application.WorksheetFunction.CountIf(Sheets("Pasc alesDoc.").Range("H:H"), cel)

    By the way, there is no need to select cells to set their formula or value. Instead of

    cel.Offset(0, 1).Select
    Selection.Value= Application.WorksheetFunction.CountIf(Sheets("Pasc alesDoc.").Range("H:H"), cel)

    you can use

    cel.Offset(0, 1).Value= Application.WorksheetFunction.CountIf(Sheets("Pasc alesDoc.").Range("H:H"), cel)

  9. #9
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheetfunction (VB for Excel XP)

    Hi Hans,

    I've noticed a mistake in what I was doing, I actually need to calculate the sum of the the previous column....the only modification I've done is:

    'Calculate ALL POI's
    Dim cel As Range, POI As String, Subscribers As Range

    Rows("1:1").AutoFilter
    Columns("H:H").Select
    Selection.Replace what:="#N/A", Replacement:="aaa", lookat:=xlPart, _
    searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
    ReplaceFormat:=False
    Selection.AutoFilter field:=8, Criteria1:="aaa"
    Do
    Set rng = Cells.Find(what:="aaa", After:=ActiveCell, _
    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    If Not rng Is Nothing Then
    rng.EntireRow.Delete
    Else
    Exit Do
    End If
    Loop
    Set rng = Sheets("Results by POI").Range("A4:A60")
    Sheets("PascalesDoc.").Range("A1:H1").Select
    For Each cel In rng
    Selection.AutoFilter field:=8, Criteria1:=cel.Value
    Sheets("Results by POI").cel.Offset(0, 1).Value = Application.WorksheetFunction.Subtotal(9, Sheets("PascalesDoc.").Range("G:G"))
    On Error Resume Next
    Next

    Sheets("PascalesDoc.").Rows("1:1").AutoFilter

    Sheets("Steps").Select
    Range("L21:L22").FormulaR1C1 = "DONE"

    Application.ScreenUpdating = True

    End Sub

    This is the last portion of the last sub...can you tell me how can I do it so it wouldn't give me an error while still looping through the range to select each cell in the filter? Also, if the cel.value isn't found in the filter, I would like the cel.offset(0,1).value be equal to 0...Is that possible?

    thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheetfunction (VB for Excel XP)

    I can't be sure, since the Steps sheet is not present in the workbook you attached, but I think you want a SumIf calculation:

    For Each cel In rng
    cel.Offset(0, 1).Value = Application.WorksheetFunction.SumIf _
    (Sheets("PascalesDoc.").Range("H:H"), cel, Sheets("PascalesDoc.").Range("G:G"))
    Next

  11. #11
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheetfunction (VB for Excel XP)

    That is excellent Hans

    Thank you very much

Posting Permissions

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