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 said:
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 said:
Pman,
This is different than your original question!
Is your comparison data still in column A?
Pman said:
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
:
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