Trying to COUNTIF with OFFSET 1 row to Last Row

[Shiseiji]

I've spent 4+ hours trying to figure this out without success.
Simple Record Macro works:

ActiveCell.FormulaR1C1 ="=""Cards issued this week ["" & COUNTIF(R[1]C:R[15648],""Yes"") & ""]"" "

But I can't get figure out how to revise the code into a dynamic range. I've tried several versions, for simplicity I've only included one non-working example of what I've tried.

    Dim LastRow                As Long
    Dim LastCol                 As Integer
    Dim thiswksht              As Worksheet
    Dim thiswb                  As Workbook
    '
    '
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
     '
     'code to get me to this point
     ' now insert a column and select row 1 cell in the new column.
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.Offset(0, 1).Select
    '
    'count all the "yes'" from row two to the last row
    ' the next line works but I need to count to the last row
    'ActiveCell.FormulaR1C1 ="=""Cards issued this week ["" & COUNTIF(R[1]C:R[15648],""Yes"") & ""]"" "
    'ActiveCell.FormulaR1C1 = "= ""["" & COUNTIF(ActiveCell.Offset(1, 0), LastRow),""Yes"") & ""] Cards Issued This Week"" "

As always
TIA

And may everyone in the US enjoy their 4th!

Ron

 

[JoeMo]

This should work if the activecell is in the same column as your "yes" values. Using Activecell is risky as the activecell may not be where you intended it when you run the macro.

ActiveCell.FormulaR1C1 = "=""Cards issued this week [ ""&COUNTIF(R[1]C:R[" & LastRow & "]C,""Yes"") & ""]"""

 

[Shiseiji]

JoeMo, thank you. After reviewing, again, the notations for absolute locations, I think can read what you wrote. Never would have thought of it, and am still thinking about how I could have phrased the question in a search. I would never have thought to make LastRow absolute as I thought, obviously, that

 LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

had already done that, or put a column reference AFTER LastRow.

I did double check what had become the ActiveCell after inserting the column. I've gone back and forth between moving as I insert columns or inserting the number of columns needed and then choosing them by column number. I "think" either way I have to move into the inserted column, be it by offset or RC number. I'm happy to take any advice!

Thanks much for the code and the lesson. I'll confirm it worked tomorrow.

Ron

 

[Shiseiji]

I couldn't get the code MoJoe provided to work, but realized I could use a planned named range to not have to specify in the CountIf the start and end cells. I use OffSet to not include the formula in my named range. May not be the optimal , I'm always looking for help

Here is my whole module for others reference/suggestions for improvement. This is one of ten columns I need to insert then run various date functions/create date ranges against the dates in another column:

Sub m_Count_ThisWeek()
'   module name is Count_ThisWeek
    
'
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    '
    Worksheets("AD").Activate
    '
    Dim LastRow                 As Long
    Dim LastCol                 As Integer
    Dim oneName                 As Name
    Dim thiswksht                As Worksheet
    Dim thiswb                  As Workbook
    '
    Set thiswksht = ActiveSheet
    '
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    '
    For Each oneName In ThisWorkbook.Names
        On Error Resume Next
        If oneName.RefersToRange.Parent.Name = "c_AD_ThisWeek" Then oneName.Delete
        On Error GoTo 0
    Next oneName
    '
    Columns(4).Select
    Cells(1, ActiveCell.Column).Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.Cells(LastRow)).Select
    Selection.Name = "c_AD_ThisWeek"
    '
    Cells(1, ActiveCell.Column).Select
    ActiveCell.FormulaR1C1 = _
        "=""Cards Activated This Week ["" & (COUNTIF(c_AD_ThisWeek,""Yes"")) &""]"""
    '
    ActiveCell.Calculate
    '
    Range("c_AD_ThisWeek").Select
    With Selection
        .NumberFormat = "General"
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 1
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Font.Bold = False
    End With
    Cells(1, ActiveCell.Column).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Font.Bold = True
    End With
    '
    Cells(1, ActiveCell.Column).Select
    ActiveCell.EntireColumn.Columns.AutoFit
    '
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
'
End Sub