ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Do I need a sumif or sum of a vlookup formula? (https://www.excelbanter.com/excel-worksheet-functions/28733-re-do-i-need-sumif-sum-vlookup-formula.html)

PeterB

Do I need a sumif or sum of a vlookup formula?
 

Sub FindGrandTotals()

'Sorry I have not replied any earlier, - I haven't been on ExcelForum
for a while.
'Next, please don't be offended if I am explaining too much, but you
said you are new to VBA,
'so I will explain each line as best as I can. BTW, I've not been using
VBA for too long
'either, so I hope the explanation is up to standard. Come back if it's
not.
Apologies for not sending a Private message, but this exceeds the 1000
character limit that seems to be in place on PMs.

'OK, Here we are declaring variables that we will be using in the
macro. I don't think that this
'is absolutely necessary (I may be wrong), However, it is a 'clean' and
'structured' way of working.
Dim CurrentSheet As Integer 'We will use the Currentsheet
variable to keep track of which
'worksheet we are working in at any
particular point in time.
Dim FirstSheet As Integer 'We will use the FirstSheet
variable to hold the index number
'of the first of your worksheets.
Dim LastSheet As Integer 'There you go, proof that I'm
fairly new to vba - originally, I
'declared LastSheet as a String. (I
don't know why I did that,
'or why it worked when I was trying
it out.)
'Anyway, this variable will hold
the index number for the last
'of your worksheets.
Dim GrandTotal 'We will use this variable to hold
the result that you are after.
'Dim LastSheet As String 'I have 'commented' this line out
so you could see the error I
'said about above. You can delete
this declaration, if you want
'to.

GrandTotal = 0 'Here we are making sure that the
GrandTotal variable is set to
'zero, so we don't add up garbage
that might be sitting in the
'memory location that the variable
uses. Again, good practice,
'even if not always necessary.

'This next statement adds a new worksheet to you workbook, so we can
work in the new worksheet,
'without disturbing or corrupting your existing worksheets.
Set NewSheet = Worksheets.Add

'The next three statements name the new worksheet as "Totalise". This
is so we know how to refer to
'the worksheet when we want to do anything in it.
With NewSheet 'The "With" statement lets us
specify an object or user-defined
'type once for a series of
statements. "With" statements make
'procedures run faster and help us
avoid repetitive typing.
'Actually, here it is probably
overkill because we only have one
'following statement, but if you
use it regularly, it becomes
'habit and will likely benefit you
in the long run.
.Name = "Totalise" 'The fact that we used the "With"
statement means that we can
'define the new worksheet's .Name
property as "Totalise", using
'this cut-down statement, rather
than using the statement -
' NewSheet.Name = "Totalise"
'(I'm also not sure if the full
statement would be work,
'anyway.)
End With 'A "With" statement has to have a
matching "End" statement.

'The next statement assigns the index number of your "AR1" sheet to the
FirstSheet variable. I must
'admit that it has just struck me that I have assumed that the index
numbers of your worksheets
'actually increment from "AR1" through to "VO1". You may need to verify
this. I get the impression
'from the VB Help file that the index number depends on the ordering of
your worksheets' tabs across
'the bottom of the worksheet window. So I would expect that you would
need to have them ordered "AR1"
'through to "VO1" with no interceding worksheets that you do not want
taken account of in the
'GrandTotal result. I would also expect that "AR1" does NOT need to be
the left-most worksheet.
FirstSheet = Worksheets("AR1").Index

'The next statement assigns the index number of your "VO1" sheet to the
LastSheet variable. This way,
'we can check when we have got to the last sheet in your series.
LastSheet = Worksheets("VO1").Index

'If you change the order of your worksheets or their names you will ned
to adjust the code in this
'macro. Either way, you would have to change the two statements above
to refer to your new first and
'last sheets.

'The next statement sets the value of the CurrentSheet variable to the
index number of your first
'sheet.
CurrentSheet = FirstSheet


'The following For-Next loop works through each of your worksheets,
according to their index numbers.
'It starts with the index number for your first sheet and ends with the
index number of your last
'sheet.
For CurrentSheet = FirstSheet To LastSheet


'The next statement finds the row that has "Grand Total" in column A of
the worksheet whose index
'number is currently held in the CurrentSheet variable. We do this,
using the "Find Method".
'The "Find Method" finds specific information in a range (in this case
"a:a" - which really means
'column a), and returns a Range object that represents the first cell
where that information is
'found.
'The statement assigns the returned Range to the variable c, so that we
can then use it later on.
Set c = Worksheets(CurrentSheet).Range("a:a").Find("Grand Total",
LookIn:=xlValues)


'The next statement performa a running summation of the contents of the
column B cells next to the
'column A cells containing "Grand Total". What that means is that each
time the loop repeats, the
'GrandTotal variable is updated by adding the total from the worksheet
that is now being looked at.
'The total on each sheet is located at the row that we get from the
variable c and the column is 2,
'because 1 is equivalent to A, 2 is equivalent to B, etc.
GrandTotal = GrandTotal + Worksheets(CurrentSheet).Cells(c.Row, 2)

'The next statement makes the loop repeat until we have worked our way
through to your last sheet.
Next

'The next statement just puts a 'comment' in Cell A1 of the "Totalise"
worksheet. You can change
'this to put it in any cell you wish on any worksheet you wish. Of
course you may not want to use
'this statement at all. Also, if you don't use the "Totalise"
worksheet, you can remove the
'statements that add and name it.
Worksheets("Totalise").Cells(1, 1) = "Summed Grand Totals"

'The next statement places the sum that you want in Cell B1 of the
"Totalise" worksheet. Again, you
'can change this to put it in any cell you wish on any worksheet you
wish, or just delete this
'statement entirely.
Worksheets("Totalise").Cells(1, 2) = GrandTotal

End Sub


--
PeterB
------------------------------------------------------------------------
PeterB's Profile: http://www.excelforum.com/member.php...o&userid=20288
View this thread: http://www.excelforum.com/showthread...hreadid=360384



All times are GMT +1. The time now is 11:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com