Countif Macro

[aeddipa]

Ok so I'm trying a round about way to get an autofill to work. I have a dataset on Sheet1 which has a formula in Column L with the word "New" or "Old".

I want cell M2 to countif the cells are "New".

Now, I want to incorporate this into my macro for autofill.

I want it to be somehting like this...

LR = Sheet1.Cells("M2").Value + 1
Selection.AutoFill Destination:=Range("A2:A" & LR)

Would something like this or similar work?

 

[MrKowz]

What is your purpose for this? What do you want the outcome to be?

As it currently reads, if there are, say, 5 "New" items, it will only autofill from A2:A6. Also, You will need to have the .Autofill line read as :

Range("A2").AutoFill Destination:=Range("A2:A" & LR)

 

[aeddipa]

It was a round about way to obtain an autofill to last row due to on a previous thread I was copying data from another sheet and pasting it into a new tab. Even though I had done a copy only visible cells for some reason excel believed there was data in some of the blank cells so when I did my autofill, it would go further than I wanted resulting in errors.

My roundabout solution was to get a countif statement that counted on the first sheet the number of "new" rows and then add 1 on to that number and use that as my LR in the autofill code. Here is the solution in case you are interested. Thank you for the response though!

Sub Unauth_Ded()
 
Dim LR As Long
 
    Selection.AutoFilter Field:=12, Criteria1:="NEW"
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("New Unauth").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],'CA Codes'!R1C1:R32C2,2,FALSE)"
    Range("A2").Select
   [B] LR = WorksheetFunction.CountIf(Sheets("New Detail").Range("L2: L10000"), "New") + 1[/B]
[B]    Selection.AutoFill Destination:=Range("A2:A" & LR)[/B]
    Columns("A:A").EntireColumn.AutoFit
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Analyst"
    Range("A1").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
    Sheets("New Detail").Select
    ActiveSheet.ShowAllData
End Sub

 

[MrKowz]

I see...

Here is some cleaned up code that should run faster. Also, notice the slight change to your LR code. By putting in the Rows.Count, it will look for "New" all the way to the end of the spreadsheet, on the offchance that your data grows beyond row 10,000.

Sub Unauth_Ded()
Dim LR As Long
Application.ScreenUpdating = False
    With Selection
        .AutoFilter Field:=12, Criteria1:="NEW"
        .SpecialCells(xlCellTypeVisible).Copy
    End With
    Sheets("New Unauth").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Application.CutCopyMode = False
    Cells.EntireColumn.AutoFit
    Columns("A:A").Insert Shift:=xlToRight
    Range("A2").FormulaR1C1 = "=VLOOKUP(RC[1],'CA Codes'!R1C1:R32C2,2,FALSE)"
    LR = WorksheetFunction.CountIf(Sheets("New Detail").Range("L2:L" & Rows.Count), "New") + 1
    Range("A2").AutoFill Destination:=Range("A2:A" & LR)
    Columns("A:A").EntireColumn.AutoFit
    With Range("A1")
        .Value = "Analyst"
        With .Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
    Sheets("New Detail").Select
    ActiveSheet.ShowAllData
Application.ScreenUpdating = True
End Sub

Edit: Noticed one small thing I didn't correct.

 

[aeddipa]

Appreciate the clean-up. Never had a formalized course on VBA. Just picked it up on my own so certain things I'm still learning.

Thank you!