Add Formula SumIf Relative Range D = A*

  • Thread starter Thread starter mkerstei
  • Start date Start date
M

mkerstei

I can't seem to be able to learn how to put macro formulas inside o
spreadsheet formulas! just a last bit of help and this project wil
finally be finished.

Right now I have Column D which is populated with names (i.e. A20004
B33343, etc.) Each name has number data in column F.

I need a macro that:
1. Goes to the last data entry for A, which varies by workbook.
2. Move 3 Rows below that and enters the name "A Article"
3. Moves 3 Columns to the right (Column F) and enters the formula
=Sumif(Column A Data, "=A*", Column F Data)
4. It will then move to the next column on the right and do the same
keeping Column A's data anchored.

This is a bit too complex for my beginner VBA status, any help would b
appreciated. The code I have so far is at the bottom, it should tak
care of objective 1 and 2. Thanks!

Range("d1").End(xlDown).Select
ActiveCell.Offset(rowoffset:=3, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"A Article"
ActiveCell.Offset(rowoffset:=0, Columnoffset:=3).Activate
COLOR="Red"]ActiveCell.FormulaR1C1 = _
"=Sumif(" & Range("D1").End(xlDown) & ","=A*","
Range("F1").End.(xlDown) & ")"[/QUOTE]

Note: The code in red is INCORRECT and needs help
 
Note that these posts hit the public newsgroups, without colour, so it is
better to tell us where the problem is.

Why not just use

ActiveCell.FormulaR1C1 = "=Sumif(D:D,""A*"",F:F)"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Sub eFG()
Dim rngD As Range, rngF As Range
Dim rng As Range
With Worksheets("Sheet1")
Set rngD = .Range(.Range("D1"), _
.Range("D1").End(xlDown))
End With
Set rng = rngD(rngD.Count)(4, 3)
rng.Offset(0,-2).Value = "A Article"
Set rngF = rngD.Offset(0, 2)
rng.Formula = "=Sumif(" & rngD.Address _
& ",""A*""," & _
rngF.Address & ")"
rng.Offset(0, 1).Formula = "=Sumif(" & _
rngD.Address & ",""A*""," & _
rngF.Offset(0, 1).Address & ")"

End Sub
 
Since he puts a label on the same row in D that begins with an A (A Article),
that could give a double count - it does for me if I enter or edit the label
after the formula is entered, but not if the label is entered before the
formula - seems a little flakey. <g>

--
Regards,
Tom Ogilvy




Bob Phillips said:
Note that these posts hit the public newsgroups, without colour, so it is
better to tell us where the problem is.

Why not just use

ActiveCell.FormulaR1C1 = "=Sumif(D:D,""A*"",F:F)"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

mkerstei said:
I can't seem to be able to learn how to put macro formulas inside of
spreadsheet formulas! just a last bit of help and this project will
finally be finished.

Right now I have Column D which is populated with names (i.e. A20004,
B33343, etc.) Each name has number data in column F.

I need a macro that:
1. Goes to the last data entry for A, which varies by workbook.
2. Move 3 Rows below that and enters the name "A Article"
3. Moves 3 Columns to the right (Column F) and enters the formula
=Sumif(Column A Data, "=A*", Column F Data)
4. It will then move to the next column on the right and do the same,
keeping Column A's data anchored.

This is a bit too complex for my beginner VBA status, any help would be
appreciated. The code I have so far is at the bottom, it should take
care of objective 1 and 2. Thanks!

Range("d1").End(xlDown).Select
ActiveCell.Offset(rowoffset:=3, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"A Article"
ActiveCell.Offset(rowoffset:=0, Columnoffset:=3).Activate
COLOR="Red"]ActiveCell.FormulaR1C1 = _
"=Sumif(" & Range("D1").End(xlDown) & ","=A*"," &
Range("F1").End.(xlDown) & ")"
Click to expand...

Note: The code in red is INCORRECT and needs help.
Click to expand...
 
Thanks, with just a tiny bit of editing I was able to get exactly what
needed. Again, thank you for all of your help
 
Back
Top