Macro to fill column with SUMPRODUCT formula
Hi Bob,
Thanks, this is a big help! But I made a wrong assumption on my formula so
what is needed for column D is
SUMPRODUCT(($A$2:$A$[lastrow]=A2)*($C$2:$C$[lastrow]=""Exist"")). The
[lastrow] is the last row in the sheet and differs from sheet to sheet I run
the macro on. I also need the sorting to be by Column A & B now (I might not
have been too clear about this in my first post but... I want to sort the
entire sheet/selection based on Column A & B, and not just sorting the values
within those two columns).
I also noticed the autofill of the formulas stops whenever cell(s) in column
A or B is blank. For a row, it's possible for Column A and/or B to be blank
while Column E onwards still contain data. Is there a way for the autofill to
fill in the formulas until the actual last row ? (You may assume row E as
always containing data).
Appreciate your help and sorry for causing the rework on the code.
TIA
Beth
"Bob Phillips" wrote:
Dim LastRow As Long
With ActiveSheet
.Columns(1).Sort key1:=.Range("A1"), header:=xlGuess
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("C:D").Insert
.Range("C2").Resize(LastRow - 1).Formula =
"=IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,""E xist"",""Duplicate"")"
.Range("D2").Resize(LastRow - 1).Formula =
"=SUMPRODUCT(($A$2:$A$2=A2)*($C$2:$C$2=""Exist"")) "
End With
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Beth" wrote in message
...
Hi,
I'm new to macros. How do I create a macro that sorts by Column A, inserts
two new columns after column B (i.e. they will become column C & D) and
fill
column C & Column D with these formulas down to the last row in the
current
sheet ?
Formula assumes that column headers are in first row.
Column C:
=IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,"Exi st","Duplicate")
Column D:
=SUMPRODUCT(($A$2:$A$2=A2)*($C$2:$C$2="Exist"))
This will really save me time since I have to do this task on many sheets.
TIA
Beth
|