This is a discussion on VBA Countif non-blank cells and loop within the Excel Questions forums, part of the Question Forums category; Good day everyone, I would appreciate your help. I have hit a snag in creating a macro. I need something ...
Good day everyone,
I would appreciate your help. I have hit a snag in creating a macro. I need something that will use countifs (or sumproduct) non-blank rows in the last non-blank column if the meet two requirements and place the result in the column to the right of the last column. So in B2 place the results of a countifs formula based on the value of A2. Below is what I have so far. I hope I explained this well enough.
Thank you for your help
Code:Sub Macro3() Dim LastColumn As Integer Dim NextColumn As Integer 'Find last column with text If WorksheetFunction.CountA(Cells) > 0 Then LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column End If 'Copy list into next non-blank column Sheets("Lists").Select Columns("B:B").Select Selection.Copy Sheets("Top 5").Select Columns(LastColumn + 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Countif Loop FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For I = 3 To FinalRow If Cells(I, LastColumn).Value = "*" Then Cells(I, LastColumn + 1).Value = Application.WorksheetFunction.CountIfs(Worksheets("Data").Range("e:e"), Cells(I, LastColumn), Worksheets("Data").Range("a:a"), Cells(2, LastColumn)) End If Next I End Sub