stratthaslam
Board Regular
- Joined
- Feb 4, 2009
- Messages
- 63
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
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