Add Formula SumIf Relative Range D = A*
 

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) & ")"[/color]

Note: The code in red is INCORRECT and needs help

--
mkerste
-----------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=54957

Add Formula SumIf Relative Range D = A*
 

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" wrote in
message ...

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) & ")"

Note: The code in red is INCORRECT and needs help.


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile:[/color]
http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549575

Add Formula SumIf Relative Range D = A*
 

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

--
Regards,
Tom Ogilvy


"mkerstei" wrote:


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) & ")"

Note: The code in red is INCORRECT and needs help.


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549575

[/color]

Add Formula SumIf Relative Range D = A*
 

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" wrote:

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" wrote in
message ...

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) & ")"

Note: The code in red is INCORRECT and needs help.


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile:
http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549575



[/color]

Add Formula SumIf Relative Range D = A*
 

Thanks, with just a tiny bit of editing I was able to get exactly what
needed. Again, thank you for all of your help

--
mkerste
-----------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=54957