CountA Macro not working :(-VBForums
Results 1 to 6 of 6

Thread: CountA Macro not working :(

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

    CountA Macro not working :(

    I am trying to turn this counta query into a macro does anyone have an idea of how to do it:
    Code:
    =COUNTA(TABF10!F:F)-2
    and also this sum into a macro
    Code:
    =SUMPRODUCT(KTPT81T!G:G)
    this is what i had done so far for the counta macro, am I on the right path:

    Code:
    Sub CountA()
    Dim x As Long
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    If ws.Name = "TABF10" Then
    x = ws.Range("G" & Rows.Count).End(xlUp).Row
    If x > 100 Then x = 100
    Sheets("Index").Range("Z2") = Application.WorksheetFunction.CountA(Range("G5" & "G50"))
    x = 0
    End If
    Next ws
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,996

    Re: CountA Macro not working :(

    are you trying to put the formula in the cell? this will make it dynamic and it will update if the value in the range it refers to changes, or to insert the result of the formula into the cell, this would be static, the result will not change when any values in range G5:G50 change

    for the former, you should specify it is a formula like
    vb Code:
    1. Sheets("Index").Range("Z2").formula = "=counta(g5:g50)"
    check result is correct
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

    Re: CountA Macro not working :(

    I need it to be dynamic and I want the result to be displayed in sheet 1 column y2, I have modified this code
    Code:
    Sub COUNTA()
    With ThisWorkbook.Sheets("TABF10").Range("F:F")
        With Range(.Cells(1, 1), .Cells(.Rows.COUNT, 0).End(xlUp))
            Sheets("Index").Range("Z2").FormulaR1C1 = "=COUNTA(" & .Address(, , xlR1C1) & ")"
            End With
            End With
    End Sub
    but the result is coming back as 40 instead of the right figure of 43

  4. #4
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,962

    Re: CountA Macro not working :(

    Hello,

    This question really relates to developing for an Office Application, as a result, I am going to move it to the Office Development Forum.

    Gary

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,996

    Re: CountA Macro not working :(

    but the result is coming back as 40 instead of the right figure of 43
    i can not see that

    With Range(.Cells(1, 1), .Cells(.Rows.COUNT, 0).End(xlUp))
    this should error, the range is on the activesheet, but the cells are on tabf10 sheet, also i do not believe 0 would be a valid value
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

    Re: CountA Macro not working :(

    it is not erroring at all which is weird. I probably made a mistake somewhere or the whole code is messed up, let me double check and see if i should change it completely,


    Thanks

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.