Good Morning, and a hearty Monday to all of you.

I have a worksheet with a lot of columns. In one of the columns (lets say A)
I have the name of the piblications, in B I have the dates on which the ads
appear, and in column C I have the cost per insertion. There is a line in
between 2 different publications. The information looks something like this:
Column A Column B Column C
Pub 1 11th nov 500
Pub 1 12th nov 500
Pub 1 17th nov 500
Pub 1 18th nov 500

Pub 2 19th nov 300
Pub 2 24th nov 400

Pub 3 11th dec 500
Pub 3 12th dec 500
Pub 3 14th dec 500

The number of Publications vary in every file. I was wondering if someone
could help with a macro to add the cost per insertion in column C, so that I
get the total cost per publication in the blank row.

Thanks,
Prash

You may want to try a formula the function SUMIF(range,criteria,sum column)

"Pman" wrote:

> Good Morning, and a hearty Monday to all of you.
>
> I have a worksheet with a lot of columns. In one of the columns (lets say A)
> I have the name of the piblications, in B I have the dates on which the ads
> appear, and in column C I have the cost per insertion. There is a line in
> between 2 different publications. The information looks something like this:
> Column A Column B Column C
> Pub 1 11th nov 500
> Pub 1 12th nov 500
> Pub 1 17th nov 500
> Pub 1 18th nov 500
>
> Pub 2 19th nov 300
> Pub 2 24th nov 400
>
> Pub 3 11th dec 500
> Pub 3 12th dec 500
> Pub 3 14th dec 500
>
> The number of Publications vary in every file. I was wondering if someone
> could help with a macro to add the cost per insertion in column C, so that I
> get the total cost per publication in the blank row.
>
> Thanks,
> Prash

Hi JRform,

Thanks for the reply.
I'm kind of very new to VBA coding, so if you could please tell me what the
formula would look like, I'd appreciate it.

Thanks again,

Prash.

"JRForm" wrote:

> You may want to try a formula the function SUMIF(range,criteria,sum column)
>
> "Pman" wrote:
>
> > Good Morning, and a hearty Monday to all of you.
> >
> > I have a worksheet with a lot of columns. In one of the columns (lets say A)
> > I have the name of the piblications, in B I have the dates on which the ads
> > appear, and in column C I have the cost per insertion. There is a line in
> > between 2 different publications. The information looks something like this:
> > Column A Column B Column C
> > Pub 1 11th nov 500
> > Pub 1 12th nov 500
> > Pub 1 17th nov 500
> > Pub 1 18th nov 500
> >
> > Pub 2 19th nov 300
> > Pub 2 24th nov 400
> >
> > Pub 3 11th dec 500
> > Pub 3 12th dec 500
> > Pub 3 14th dec 500
> >
> > The number of Publications vary in every file. I was wondering if someone
> > could help with a macro to add the cost per insertion in column C, so that I
> > get the total cost per publication in the blank row.
> >
> > Thanks,
> > Prash

Pman,

Add this code to a module and you can get your totals
ALT + F11 -open VB editor
add a module and paste the code below.

Option Explicit

Sub Pman()

Dim iLastRow As Long
Dim look4Me As String
Dim first, sec, third As Variant

iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row
'change to the column you want to search

Range("G1").Select 'go to the top of the column to begin searching

Do Until iLastRow = -1
If ActiveCell <> "" Then look4Me = ActiveCell

If ActiveCell = look4Me Then
first = first + ActiveCell.Offset(0, 1)
sec = sec + ActiveCell.Offset(0, 2)
third = third + ActiveCell.Offset(0, 3)
Else
ActiveCell.Offset(0, 1) = first
ActiveCell.Offset(0, 2) = sec
ActiveCell.Offset(0, 3) = third
'reset for next pub
first = 0
sec = 0
third = 0
End If

iLastRow = iLastRow - 1
ActiveCell.Offset(1, 0).Select
Loop

End Sub


"Pman" wrote:

> Hi JRform,
>
> Thanks for the reply.
> I'm kind of very new to VBA coding, so if you could please tell me what the
> formula would look like, I'd appreciate it.
>
> Thanks again,
>
> Prash.
>
> "JRForm" wrote:
>
> > You may want to try a formula the function SUMIF(range,criteria,sum column)
> >
> > "Pman" wrote:
> >
> > > Good Morning, and a hearty Monday to all of you.
> > >
> > > I have a worksheet with a lot of columns. In one of the columns (lets say A)
> > > I have the name of the piblications, in B I have the dates on which the ads
> > > appear, and in column C I have the cost per insertion. There is a line in
> > > between 2 different publications. The information looks something like this:
> > > Column A Column B Column C
> > > Pub 1 11th nov 500
> > > Pub 1 12th nov 500
> > > Pub 1 17th nov 500
> > > Pub 1 18th nov 500
> > >
> > > Pub 2 19th nov 300
> > > Pub 2 24th nov 400
> > >
> > > Pub 3 11th dec 500
> > > Pub 3 12th dec 500
> > > Pub 3 14th dec 500
> > >
> > > The number of Publications vary in every file. I was wondering if someone
> > > could help with a macro to add the cost per insertion in column C, so that I
> > > get the total cost per publication in the blank row.
> > >
> > > Thanks,
> > > Prash

Hi JRform,

I get a "type mismatch" error message when it tries to add the columns. I
have a couple of columns to add, and the sample data looks like the one
attached in the image below:

http://tinypic.com/view.php?pic=24vtfa1&s=2

The columns I'm trying to add up are: J, L, N, P, R, T, V, X, Z, AB, AD, AF,
AG and AH.

I really really appreciate your help on this.

Thanks,

Prash

"JRForm" wrote:

> Pman,
>
> Add this code to a module and you can get your totals
> ALT + F11 -open VB editor
> add a module and paste the code below.
>
> Option Explicit
>
> Sub Pman()
>
> Dim iLastRow As Long
> Dim look4Me As String
> Dim first, sec, third As Variant
>
> iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row
> 'change to the column you want to search
>
> Range("G1").Select 'go to the top of the column to begin searching
>
> Do Until iLastRow = -1
> If ActiveCell <> "" Then look4Me = ActiveCell
>
> If ActiveCell = look4Me Then
> first = first + ActiveCell.Offset(0, 1)
> sec = sec + ActiveCell.Offset(0, 2)
> third = third + ActiveCell.Offset(0, 3)
> Else
> ActiveCell.Offset(0, 1) = first
> ActiveCell.Offset(0, 2) = sec
> ActiveCell.Offset(0, 3) = third
> 'reset for next pub
> first = 0
> sec = 0
> third = 0
> End If
>
> iLastRow = iLastRow - 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> End Sub
>

Pman,

This is different than your original question!

Is your comparison data still in column A?

"Pman" wrote:

> Hi JRform,
>
> I get a "type mismatch" error message when it tries to add the columns. I
> have a couple of columns to add, and the sample data looks like the one
> attached in the image below:
>
> http://tinypic.com/view.php?pic=24vtfa1&s=2
>
> The columns I'm trying to add up are: J, L, N, P, R, T, V, X, Z, AB, AD, AF,
> AG and AH.
>
> I really really appreciate your help on this.
>
> Thanks,
>
> Prash
>
> "JRForm" wrote:
>
> > Pman,
> >
> > Add this code to a module and you can get your totals
> > ALT + F11 -open VB editor
> > add a module and paste the code below.
> >
> > Option Explicit
> >
> > Sub Pman()
> >
> > Dim iLastRow As Long
> > Dim look4Me As String
> > Dim first, sec, third As Variant
> >
> > iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row
> > 'change to the column you want to search
> >
> > Range("G1").Select 'go to the top of the column to begin searching
> >
> > Do Until iLastRow = -1
> > If ActiveCell <> "" Then look4Me = ActiveCell
> >
> > If ActiveCell = look4Me Then
> > first = first + ActiveCell.Offset(0, 1)
> > sec = sec + ActiveCell.Offset(0, 2)
> > third = third + ActiveCell.Offset(0, 3)
> > Else
> > ActiveCell.Offset(0, 1) = first
> > ActiveCell.Offset(0, 2) = sec
> > ActiveCell.Offset(0, 3) = third
> > 'reset for next pub
> > first = 0
> > sec = 0
> > third = 0
> > End If
> >
> > iLastRow = iLastRow - 1
> > ActiveCell.Offset(1, 0).Select
> > Loop
> >
> > End Sub
> >

Hi JRform,

Sorry that I messed up with the 1st question, was trying to explain in as
simple a way as possible.

Yes the comparison daat is still in the 1st column, i.e. the names of the
publications. The software that I use does not give the option of totals when
I also use the date, however it does give a total by publication by month if
I exclude the date (which I cannot afford to). Hence thought that a macro
would be the best bet (but I suck at it, and hence am here for
help......help!).

I'm sorry if I wasted your time with the 1st question again.

Regards,

Prash.


"JRForm" wrote:

> Pman,
>
> This is different than your original question!
>
> Is your comparison data still in column A?
>
> "Pman" wrote:
>
> > Hi JRform,
> >
> > I get a "type mismatch" error message when it tries to add the columns. I
> > have a couple of columns to add, and the sample data looks like the one
> > attached in the image below:
> >
> > http://tinypic.com/view.php?pic=24vtfa1&s=2
> >
> > The columns I'm trying to add up are: J, L, N, P, R, T, V, X, Z, AB, AD, AF,
> > AG and AH.
> >
> > I really really appreciate your help on this.
> >
> > Thanks,
> >
> > Prash
> >
> > "JRForm" wrote:
> >
> > > Pman,
> > >
> > > Add this code to a module and you can get your totals
> > > ALT + F11 -open VB editor
> > > add a module and paste the code below.
> > >
> > > Option Explicit
> > >
> > > Sub Pman()
> > >
> > > Dim iLastRow As Long
> > > Dim look4Me As String
> > > Dim first, sec, third As Variant
> > >
> > > iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row
> > > 'change to the column you want to search
> > >
> > > Range("G1").Select 'go to the top of the column to begin searching
> > >
> > > Do Until iLastRow = -1
> > > If ActiveCell <> "" Then look4Me = ActiveCell
> > >
> > > If ActiveCell = look4Me Then
> > > first = first + ActiveCell.Offset(0, 1)
> > > sec = sec + ActiveCell.Offset(0, 2)
> > > third = third + ActiveCell.Offset(0, 3)
> > > Else
> > > ActiveCell.Offset(0, 1) = first
> > > ActiveCell.Offset(0, 2) = sec
> > > ActiveCell.Offset(0, 3) = third
> > > 'reset for next pub
> > > first = 0
> > > sec = 0
> > > third = 0
> > > End If
> > >
> > > iLastRow = iLastRow - 1
> > > ActiveCell.Offset(1, 0).Select
> > > Loop
> > >
> > > End Sub
> > >

Pman,

Delete the code I already posted and paste in the code here in this post.
This will total the columns you posted.
Sub Pman()

Dim iLastRow As Long
Dim look4Me As String
Dim vAddingUp(14) As Variant
Dim k As Integer
iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
'change to the column you want to search

Range("A1").Select 'go to the top of the column to begin searching

Do Until iLastRow = -1
If ActiveCell <> "" Then look4Me = ActiveCell

If ActiveCell = look4Me Then
vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9)
vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11)
vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13)
vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15)
vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17)
vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19)
vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21)
vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23)
vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25)
vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27)
vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29)
vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31)
vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32)
vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33)
Else

ActiveCell.Offset(0, 9) = vAddingUp(1)
ActiveCell.Offset(0, 11) = vAddingUp(2)
ActiveCell.Offset(0, 13) = vAddingUp(3)
ActiveCell.Offset(0, 15) = vAddingUp(4)
ActiveCell.Offset(0, 17) = vAddingUp(5)
ActiveCell.Offset(0, 19) = vAddingUp(6)
ActiveCell.Offset(0, 21) = vAddingUp(7)
ActiveCell.Offset(0, 23) = vAddingUp(8)
ActiveCell.Offset(0, 25) = vAddingUp(9)
ActiveCell.Offset(0, 27) = vAddingUp(10)
ActiveCell.Offset(0, 29) = vAddingUp(11)
ActiveCell.Offset(0, 31) = vAddingUp(12)
ActiveCell.Offset(0, 32) = vAddingUp(13)
ActiveCell.Offset(0, 33) = vAddingUp(14)
'reset for next pub

For k = 1 To 14
vAddingUp(k) = 0
Next k

End If
iLastRow = iLastRow - 1
ActiveCell.Offset(1, 0).Select
Loop

End Sub


"Pman" wrote:

> Hi JRform,
>
> Sorry that I messed up with the 1st question, was trying to explain in as
> simple a way as possible.
>
> Yes the comparison daat is still in the 1st column, i.e. the names of the
> publications. The software that I use does not give the option of totals when
> I also use the date, however it does give a total by publication by month if
> I exclude the date (which I cannot afford to). Hence thought that a macro
> would be the best bet (but I suck at it, and hence am here for
> help......help!).
>
> I'm sorry if I wasted your time with the 1st question again.
>
> Regards,
>
> Prash.
>
>
> "JRForm" wrote:
>
> > Pman,
> >
> > This is different than your original question!
> >
> > Is your comparison data still in column A?
> >
> > "Pman" wrote:
> >
> > > Hi JRform,
> > >
> > > I get a "type mismatch" error message when it tries to add the columns. I
> > > have a couple of columns to add, and the sample data looks like the one
> > > attached in the image below:
> > >
> > > http://tinypic.com/view.php?pic=24vtfa1&s=2
> > >
> > > The columns I'm trying to add up are: J, L, N, P, R, T, V, X, Z, AB, AD, AF,
> > > AG and AH.
> > >
> > > I really really appreciate your help on this.
> > >
> > > Thanks,
> > >
> > > Prash
> > >
> > > "JRForm" wrote:
> > >
> > > > Pman,
> > > >
> > > > Add this code to a module and you can get your totals
> > > > ALT + F11 -open VB editor
> > > > add a module and paste the code below.
> > > >
> > > > Option Explicit
> > > >
> > > > Sub Pman()
> > > >
> > > > Dim iLastRow As Long
> > > > Dim look4Me As String
> > > > Dim first, sec, third As Variant
> > > >
> > > > iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row
> > > > 'change to the column you want to search
> > > >
> > > > Range("G1").Select 'go to the top of the column to begin searching
> > > >
> > > > Do Until iLastRow = -1
> > > > If ActiveCell <> "" Then look4Me = ActiveCell
> > > >
> > > > If ActiveCell = look4Me Then
> > > > first = first + ActiveCell.Offset(0, 1)
> > > > sec = sec + ActiveCell.Offset(0, 2)
> > > > third = third + ActiveCell.Offset(0, 3)
> > > > Else
> > > > ActiveCell.Offset(0, 1) = first
> > > > ActiveCell.Offset(0, 2) = sec
> > > > ActiveCell.Offset(0, 3) = third
> > > > 'reset for next pub
> > > > first = 0
> > > > sec = 0
> > > > third = 0
> > > > End If
> > > >
> > > > iLastRow = iLastRow - 1
> > > > ActiveCell.Offset(1, 0).Select
> > > > Loop
> > > >
> > > > End Sub
> > > >

Thank you JrForm,

That worked like a charm You are a life saver

Thanks again,

Prash

"JRForm" wrote:

> Pman,
>
> Delete the code I already posted and paste in the code here in this post.
> This will total the columns you posted.
> Sub Pman()
>
> Dim iLastRow As Long
> Dim look4Me As String
> Dim vAddingUp(14) As Variant
> Dim k As Integer
> iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
> 'change to the column you want to search
>
> Range("A1").Select 'go to the top of the column to begin searching
>
> Do Until iLastRow = -1
> If ActiveCell <> "" Then look4Me = ActiveCell
>
> If ActiveCell = look4Me Then
> vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9)
> vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11)
> vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13)
> vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15)
> vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17)
> vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19)
> vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21)
> vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23)
> vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25)
> vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27)
> vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29)
> vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31)
> vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32)
> vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33)
> Else
>
> ActiveCell.Offset(0, 9) = vAddingUp(1)
> ActiveCell.Offset(0, 11) = vAddingUp(2)
> ActiveCell.Offset(0, 13) = vAddingUp(3)
> ActiveCell.Offset(0, 15) = vAddingUp(4)
> ActiveCell.Offset(0, 17) = vAddingUp(5)
> ActiveCell.Offset(0, 19) = vAddingUp(6)
> ActiveCell.Offset(0, 21) = vAddingUp(7)
> ActiveCell.Offset(0, 23) = vAddingUp(8)
> ActiveCell.Offset(0, 25) = vAddingUp(9)
> ActiveCell.Offset(0, 27) = vAddingUp(10)
> ActiveCell.Offset(0, 29) = vAddingUp(11)
> ActiveCell.Offset(0, 31) = vAddingUp(12)
> ActiveCell.Offset(0, 32) = vAddingUp(13)
> ActiveCell.Offset(0, 33) = vAddingUp(14)
> 'reset for next pub
>
> For k = 1 To 14
> vAddingUp(k) = 0
> Next k
>
> End If
> iLastRow = iLastRow - 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> End Sub
>

you welcome.

"Pman" wrote:

> Thank you JrForm,
>
> That worked like a charm You are a life saver
>
> Thanks again,
>
> Prash
>
> "JRForm" wrote:
>
> > Pman,
> >
> > Delete the code I already posted and paste in the code here in this post.
> > This will total the columns you posted.
> > Sub Pman()
> >
> > Dim iLastRow As Long
> > Dim look4Me As String
> > Dim vAddingUp(14) As Variant
> > Dim k As Integer
> > iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
> > 'change to the column you want to search
> >
> > Range("A1").Select 'go to the top of the column to begin searching
> >
> > Do Until iLastRow = -1
> > If ActiveCell <> "" Then look4Me = ActiveCell
> >
> > If ActiveCell = look4Me Then
> > vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9)
> > vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11)
> > vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13)
> > vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15)
> > vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17)
> > vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19)
> > vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21)
> > vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23)
> > vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25)
> > vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27)
> > vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29)
> > vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31)
> > vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32)
> > vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33)
> > Else
> >
> > ActiveCell.Offset(0, 9) = vAddingUp(1)
> > ActiveCell.Offset(0, 11) = vAddingUp(2)
> > ActiveCell.Offset(0, 13) = vAddingUp(3)
> > ActiveCell.Offset(0, 15) = vAddingUp(4)
> > ActiveCell.Offset(0, 17) = vAddingUp(5)
> > ActiveCell.Offset(0, 19) = vAddingUp(6)
> > ActiveCell.Offset(0, 21) = vAddingUp(7)
> > ActiveCell.Offset(0, 23) = vAddingUp(8)
> > ActiveCell.Offset(0, 25) = vAddingUp(9)
> > ActiveCell.Offset(0, 27) = vAddingUp(10)
> > ActiveCell.Offset(0, 29) = vAddingUp(11)
> > ActiveCell.Offset(0, 31) = vAddingUp(12)
> > ActiveCell.Offset(0, 32) = vAddingUp(13)
> > ActiveCell.Offset(0, 33) = vAddingUp(14)
> > 'reset for next pub
> >
> > For k = 1 To 14
> > vAddingUp(k) = 0
> > Next k
> >
> > End If
> > iLastRow = iLastRow - 1
> > ActiveCell.Offset(1, 0).Select
> > Loop
> >
> > End Sub
> >
>