Excel Visual Basic for Applications

[Guest]

I am a new user of VBA (in excel). I must have a huge spreadsheet finished by 3/31 and I was told the only possible way was with VB. I have tried writing some code and keep getting error messages. The spreadsheet has three columns for example
District Amount Scor
7 $10 12
23 $50 22
32 $100 21

There is about 1000 rows with information such as this. I need to be able to go to column 1 (district) and pick up a score in column three with <=200 as one category, the range 201-219 as one category, >=220 as one category, and blank as a category and then pick up and total the amounts that match this criteria in column two. So I will have dollar amounts for the 4 scorebands for each distirct in column 1. (I have 20 different districts)The sheet with the information is named data and I named the ranges with the district numbers "District", with the dollar amounts "Amount", and numerical scores "Score". I was just trying to get one scoreband to work and then I was going to copy it for all the other scorebands (unless there is an easier way). I want one total in cell "02", another in "03", etc. I think one problem is with the Range("District").Value =7. If I change it to just one cell, for instance,"A1" instead of "District", I don't get an error message,but then it adds everything in the amount column and not just <=200. I have worked with this for several days! It seems like it should be simple but.......What I need is similar to a sumif formula such as sumif(A1:A5,"<=200",C1:C5) but I also need to be able to pick up certain districts in the first column! Somebody please help! This is driving me crazy! This is what I tried in VB - and it didn't work..

If (Sheets("DATA").Range("DISTRICT").Value = 7) The
Range("O2").Formula = "=IF(SCORE<=200,SUM(AMOUNT),IF(SCORE=0,0))

End If
End Sub

 

[Greg Wilson]

Instead of VBA, I think you should do this using array
formulae. An array formula is a special type of worksheet
function that operate on a range of cells simultaneously
and returns an array (series) of results. You can
condence these results into a single result which can be
expressed in a single cell using, for example, the Sum
function.

Important: To enter an array formula, you must hold down
both the Ctrl and Shift keys and then press the Enter key
as opposed to just pressing the Enter key. If you have
done this correctly, when you view the formula in the
Formula Bar it should be surrounded in curly brackets. As
I understand your situation, the following examples return
what you need for District 7:

=SUM((District = 7) * Amount * (Score <= 200))
=SUM((District = 7) * Amount * (Score >=201)*(Score <=219))
=SUM((District = 7) * Amount * (Score >=220))
=SUM((District = 7) * Amount * (TRIM(Score) = ""))

Note that in Excel, a true statement has an equivalent
value of 1 while a false statement has an equivalent value
of 0.

Regards,
Greg

-----Original Message-----
I am a new user of VBA (in excel). I must have a huge

spreadsheet finished by 3/31 and I was told the only
possible way was with VB. I have tried writing some code
and keep getting error messages. The spreadsheet has
three columns for example,

District Amount Score
7 $10 122
23 $50 220
32 $100 219

There is about 1000 rows with information such as this. I

need to be able to go to column 1 (district) and pick up a
score in column three with <=200 as one category, the
range 201-219 as one category, >=220 as one category, and
blank as a category and then pick up and total the amounts
that match this criteria in column two. So I will have
dollar amounts for the 4 scorebands for each distirct in
column 1. (I have 20 different districts)The sheet with
the information is named data and I named the ranges with
the district numbers "District", with the dollar
amounts "Amount", and numerical scores "Score". I was just
trying to get one scoreband to work and then I was going
to copy it for all the other scorebands (unless there is
an easier way). I want one total in cell "02", another
in "03", etc. I think one problem is with the Range
("District").Value =7. If I change it to just one cell,
for instance,"A1" instead of "District", I don't get an
error message,but then it adds everything in the amount
column and not just <=200. I have worked with this for
several days! It seems like it should be simple
but.......What I need is similar to a sumif formula such
as sumif(A1:A5,"<=200",C1:C5) but I also need to be able
to pick up certain districts in the first column! Somebody
please help! This is driving me crazy! This is what I
tried in VB - and it didn't work..

 

[Greg Wilson]

Alternatively, try the VBA solution:

Option Base 1
Sub XYZ()
Dim A As Range, B As Range, C As Range
Dim Band1 As Single, Band2 As Single
Dim Band3 As Single, Band4 As Single
Dim i As Integer
Dim Dist As Integer, DistArr As Variant

'Enter all District numbers in this array:-
DistArr = Array(7, 23, 32, 50, 75)

Cells(1, 14) = "Criteria"
Cells(2, 14) = "<=200"
Cells(3, 14) = ">=201 and <=219"
Cells(4, 14) = ">=220"
Cells(5, 14) = "Blank"

For i = 1 To UBound(DistArr)
Dist = DistArr(i)
Cells(1, 14 + i) = "District " & Dist
For Each A In Range("District")
If A.Value = Dist Then
Set B = A.Offset(, 1)
Set C = A.Offset(, 2)
If Trim(C) <> "" And C <= 200 Then
Band1 = Band1 + B.Value
ElseIf C >= 201 And C <= 219 Then
Band2 = Band2 + B.Value
ElseIf C >= 220 Then
Band3 = Band3 + B.Value
ElseIf Trim(C) = "" Then
Band4 = Band4 + B.Value
End If
Cells(2, 14 + i) = Band1
Cells(3, 14 + i) = Band2
Cells(4, 14 + i) = Band3
Cells(5, 14 + i) = Band4
End If
Next A
Band1 = 0: Band2 = 0: Band3 = 0: Band4 = 0
Next i

End Sub

The two solutions were in accordance with how I understood
your problem. My interpretation may be incorrect. Make a
copy of your data before testing them !!!

Regards,
Greg

 

[Guest]

Thank you so much! I am going to go to work and try it now! I am going to try the VB first since that is what my supervisor told me to use.

 

[Greg Wilson]

I forgot to add a couple of lines that will speed it up.
Advised is that you add the line
Application.ScreenUpdating = False immediately above the
line Cells(1, 14) = "Criteria". Also, add the line
Application.ScreenUpdating = True at the end of the code
just before the End Sub statement.

If you are repeatedly calling the macro then these lines
can be ommitted:
Cells(1, 14) = "Criteria"
Cells(2, 14) = "<=200"
Cells(3, 14) = ">=201 and <=219"
Cells(4, 14) = ">=220"
Cells(5, 14) = "Blank"

You can also delete the line:
Cells(1, 14 + i) = "Dist " & Dist

The above lines add row and column headings respectively
and don't need to be restated each time.

Test the code carefully on duplicate data. I am NOT a
professional programmer and I wrote the code specifically
for your situation as I understood it. It has not been
rigorously tested. That's your job.

Regards,
Greg

-----Original Message-----
Thank you so much! I am going to go to work and try it

now! I am going to try the VB first since that is what my
supervisor told me to use.

 

[Guest]

It works!! You are a genius! Thank you so much!
I checked it and the totals were all correct.

 

[Guest]

As I said I am new at VBA. Could you explain what some of the code means? The Range A, Range B, Range C - is that the columns? And what does the Dim Band 1 as single, Band 2 as single, Dim Band 3 as single - etc do? What does the UBand at the end do? I hate to ask but I tried to look it up in an excel manual I have and couldn't find it. Is it to much trouble to explain? If I can't figure it out I'll never be able to do it myself.

Thanks!

 

[Tom Ogilvy]

in column D put in a formula like this in D2

=if(C2="","Blank",if(C2<200, "Cat A",if(C2<=219,"Cat B","Cat C")))

then drag fill down the column

In D1 put in the heading CAT

now select you data and do Data=>Pivot Table report

in the last dialog, select the layout button

for the row field, drag the button District

for the Column field, drag the button CAT

in the data area drag the button Amount.

if it doesn't show Sum of Amount, double click on it and change it to sum

click OK

make the location a new sheet.

That should do it for all districts.

--
Regards,
Tom Ogilvy

I am a new user of VBA (in excel). I must have a huge spreadsheet

finished by 3/31 and I was told the only possible way was with VB. I have
tried writing some code and keep getting error messages. The spreadsheet
has three columns for example,

District Amount Score
7 $10 122
23 $50 220
32 $100 219

There is about 1000 rows with information such as this. I need to be able

to go to column 1 (district) and pick up a score in column three with <=200
as one category, the range 201-219 as one category, >=220 as one category,
and blank as a category and then pick up and total the amounts that match
this criteria in column two. So I will have dollar amounts for the 4
scorebands for each distirct in column 1. (I have 20 different districts)The
sheet with the information is named data and I named the ranges with the
district numbers "District", with the dollar amounts "Amount", and numerical
scores "Score". I was just trying to get one scoreband to work and then I
was going to copy it for all the other scorebands (unless there is an easier
way). I want one total in cell "02", another in "03", etc. I think one
problem is with the Range("District").Value =7. If I change it to just one
cell, for instance,"A1" instead of "District", I don't get an error
message,but then it adds everything in the amount column and not just <=200.
I have worked with this for several days! It seems like it should be simple
but.......What I need is similar to a sumif formula such as
sumif(A1:A5,"<=200",C1:C5) but I also need to be able to pick up certain
districts in the first column! Somebody please help! This is driving me
crazy! This is what I tried in VB - and it didn't work..

 

[Greg Wilson]

The statements: "Dim A As Range, B As Range, C As Range" are variabl
declarations. In this case I am declaring these variables as being o
the "Range" data type which simply means they are worksheet cell(s).
used the the variable names Band1, Band2 etc. (probably not goo
choices) to store the sums for each criteria "scoreband" as you calle
them. Their data type was declared as "Single" which refers to
non-integer type. I'll leave it to you to research data types. Chi
Pearson has a discussion on declaring data types at:
http://www.cpearson.com/excel/variables.htm

I won't go into a long-winded discussion about arrays here. I'll leav
it to you to research this. You can retrieve values assigned to a
array (or list) by referring to their index numbers. One importan
thing to know about arrays is that, for some stupid reason, the inde
number for the first element is normally 0 instead of 1. Therefore
the last element has an index number equal to the number of elements i
the array minus 1. You can change this by declaring at module leve
(top of the module) the statement Option Base 1 as I did in this case.
Therefore, in my code, the index numbers correspond as one woul
reasonably assume: 1 for the first elemen, 2 for the second etc.
stored the District numbers in an array with the statement DistArr
Array(7, 23, 32, 50, 75). Therefore, the statement DistArr(i
retrieves 7 when i = 1, 23 when i = 2 etc.

UBound is a function that returns the index number of the last elemen
in an array. It requires the variable name of the array as a
argument. In this case UBound(DistArr) returns the index number of th
last element in the array. I didn't know for sure how many Distric
numbers there were (you were going to add them to the array). Usin
UBound is a way to avoid hard-coding a number. Note that it has
counterpart LBound which returns the index number of the first element
These functions are particulary useful when using dynamic arrays fo
which the number of elements change during code execution.

I used two nested loops to do the mechanics. I won't go into th
nitty-gritty. I'll demo the principles with these code examples. Th
second loop will call a message box for each cell in Range("XYZ").
You'll have to create a named range with this name. Give it, say, 1
cells. Note: Although I restate it for each code example below, yo
can only state "Option Base 1" once for each module at the top of th
module. Run the following code:

Option Base 1
Sub Test1()
Dim i As Integer, Dist As Integer
Dim DistArr As Variant
DistArr = Array(7, 23, 32, 50, 75)
For i = 1 To UBound(DistArr)
Dist = DistArr(i)
MsgBox Dist
Next
End Sub

Sub Test2()
Dim A As Range
For Each A In Range("XYZ")
A.Select
MsgBox A.Address
Next
End Sub

Note the mechanics of the above two loops. Both loops run the cod
contained withing the For and Next statements for the specified numbe
of times. The first systematically retrieves a District number fro
the array and the second systematically references each cell within th
named range. If you want to take each District number in sequence an
systematically check each cell within the named range to see if i
contains this number then you can nest them as follows. For th
experiment, insert the District numbers randomly in the named rang
"XYZ".

Option Base 1
Sub Test3()
Dim i As Integer, Dist As Integer
Dim DistArr As Variant, A As Range
DistArr = Array(7, 23, 32, 50, 75)
For i = 1 To UBound(DistArr)
Dist = DistArr(i)
For Each A In Range("XYZ")
A.Select
If A.Value = Dist Then MsgBox A.Address & " contains " & Dist
Next
Next
End Sub

The code uses the Cells method to reference cells. The Cells method i
very simple. It takes two arguments - the row number and the colum
number. For example, the statement Cells(3, 5) refers to Cell E3. Th
statement Cells(3, 5 + i) refers to Cell G3 when i = 2. This method i
tailor made for using loops to reference cells. Test the followin
code:

Option Base 1
Sub Test4()
Dim i As Integer, DistArr As Variant
Dim Dist As Integer
DistArr = Array(7, 23, 32, 50, 75)
For i = 1 To UBound(DistArr)
Dist = DistArr(i)
Cells(1, i + 5) = "Dist " & Dist
Next
End Sub

Now, what if you want to take each element in an array and check each
cell within a range to see if it equals this number. And, if so,
obtain the value of the adjacent cell and add it to a total. Once we
have done this for the first element, copy the total to a cell and then
do the same for the next element and so on (except copy the total to a
different cell of course). We will have to rezero the total variable
before proceeding to the second element to avoid creating a cumulative
total. For the experiment, enter some random numbers into the column
immediately to the right of Range "XYZ".

Option Base 1
Sub Test5()
Dim i As Integer, DistArr As Variant
Dim A As Range, B As Range
Dim Tot As Single, Dist As Integer
DistArr = Array(7, 23, 32, 50, 75)
Tot = 0
For i = 1 To UBound(DistArr)
Dist = DistArr(i)
For Each A In Range("XYZ")
If A.Value = Dist Then
Set B = A.Offset(, 1) 'Set this variable to the adjacent
cell
Tot = Tot + B.Value
Cells(2, i + 5) = Tot
End If
Next
Tot = 0 'Rezero Tot variable before proceeding to next element
Next
End Sub

Hope all goes well and I hope I didn't screw up this demo !!!


Regards,
Greg

 

[Dave Peterson]

Dim A as Range

declares an object variable named A that will be used to hold a range (and all
its properties--.value, .numberformat, etc).

Dim Band1 as Single
declares a simple variable (it'll hold a value) as Single (a number that can be
really small (negative) to really big (positive).

Look at VBA's help for: "Dim Statement" and you'll see a section for "type".
You'll see Single listed there.

My personal preference: I'd use something that was more descriptive for A, B,
and C.

Dim ADistrictCell as Range
would make it easier for me when I come back to the code in a few days
(hours/minutes??).

And I'd use
dim Band1 as double
It allows bigger and smaller numbers. But more importantly, from what I've
read, modern pc's do some housekeeping to convert to singles to doubles. (but
I've been confused before).

And it isn't uBand(), it's Ubound().

Ubound() returns the upper bouund of an array

If I had 12 things:

dim things(1 to 12) as string
dim iCtr as long

for ictr = lbound(things) to ubound(things)
'''
next ictr

It would be equivalent to:
for ictr = 1 to 12

But if I had to redimension my array (say: Dim Things(33 to 44) as string).
Then I'd have to fix all those loops.

But by using lbound() and ubound(), I can let VBA help me with the details.

In Greg's code, he used a bunch of lines that worked in conjunction with each
other:

Option Base 1
DistArr = Array(7, 23, 32, 50, 75)
For i = 1 To UBound(DistArr)

The "option base 1" says to start counting arrays (that aren't specified) at 1,
then 2, ...

so DistArr() has 5 elements. (1 through 5)

Greg could have left off the "option base 1" and use:
for ictr = lbound(distarr) to ubound(distarr)
and it would have been equivalent of:
for ictr = 0 to 4

(if he kept the "option base 1", then
for ictr = lbound(distarr) to ubound(distarr)
and it would have been equivalent of:
for ictr = 1 to 5

===
the good thing is if you need to add more codes to distarr, you don't have to
remember to go to each loop and fix them.