Excel Help Requested

[crabbyman]

So I'm at work helping a co-worker with a state report that is due soon.

The person to do the report previously left us with little to no instruction. So after looking at her old Excel sheets, I decided to automate some of the calculations. Now that we moved to a new month worksheet, it is not calculating correctly. The information is pull from a data set query from Access.

I have a set of data in column H that is a code number of either "1" or "2". I have a set of data in I that is a number of trips which is any number from 1 to ??.

I used in the previous sheet =sumif(h2:h150, "=1", i2:i150) and it calculated the number of trips per code number. For some reason this months sheet gives me the answer of 10 for either formula "=1" and "=2".

I have tried formating cells, manually reentering the numbers, and changing the formula to ">1".


Am I missing something? Something I should try?

 

[crabbyman]

*Bump*

 

[letdown427]

Well, if it still isn't working you could do it in VBA?

Right click on the little Icon to the left of File Edit etc and go to View Code.

In the module for the worksheet, add in the code below, then run the TripMacro from in Excel.

Do with the results what you will, I just dumped them in the next two cells.

 

[crabbyman]

Thank you for your help. I'm good with math hence my like of Excel formulas.


I'll still have to learn VBA...but it is more of a programming language...and I stink at details involving code/words.

Is this a basic VBA code I can work from?

EDIT: Also..what could would I want to add/edit to this to total the Column J instead of Column I using the same statement of "if I = 1/2, then add..."?

 

[crabbyman]

I tried just replacing every instance of "I" or "i" with "J/j", respectively. Is this a correct change to do the same thing with the J row totals?

...also, for the J row it is in money. I noticed with the above changes only it either rounds or drops off the cents to make it an even dollar. So the figures are off in the cents area.

$912.00
$7,750.00
$8,662.00

That's what the VBA gave me.

$913.75
$7,748.50
$8,662.25

That's the calculation done by hand. It could be miscalculated also.

 

[letdown427]

Sorry I missed your replies, forgot I posted in here.

Errrm, yeah, you did right changing the column titles lol, as for the rounding/using money, you could instead define the variables used to store the totals as Currency instead of Integers (integer eing whole number as I'm sure you know, hence it dropping the cents...)

So yeah, instead of it being Dim i1Count As Integer, it would be Dim DollarCount As Currency (The important bit is the 'Currency' type, I jsut changed the variable name to dollar count so it made a bit more sense)

Feel free to PM me if you want some more help with it, I'll probably forget to check back here otherwise

 

[crabbyman]

Thank you very much! You just made 2 people do the happy dance!


Do you know of a good introductory book/video to help me learn about VBA for Excel? I hear its all the rave with more complex functions...so it is probably best I know it.

 

[letdown427]

Unfortunately no, I've so far learnt VB6 by just, erm, doing it, and trying to make things, then using the internet to find solutions to problems. There's huge amounts of code out there you can use, and you just begin to get a knack of what things mean and how they work, and just sort of end up understanding it.

I found it's fairly simple to follow as it resembles broken English almost, and it's quite a helpful development environment. Just take a look through the code and see what it's doing, it should hopefully be fairly simple to follow.

I'm sure someone else on here will be able to point you in the right direction regards books etc. If not, Google some VB6 tutorials, (VBA is the same thing, just aimed solely at Excel etc [I think]) they'll give you an idea as to what means what.

Another good way to learn, record a macro, and then view the code for it. You'll quickly see what user tasks you can emulate etc, even if that is a clumsy way of doing it.

Finally, the most useful thing when writing macros etc,

Application.ScreenUpdating = False

That'll stop the screen flickering as it draws all the things you're telling it to do, and thus make the macro run hugely faster.

As I said, PM me if you want any other help, I'm no pro, and helping with your problems means I may learn stuff too (i case you're wondering why the hell I'd want to help with your problems! )

:beer:

 

[PowerEngineer]

While I gather that you've solved this problem using letdown's VB macro, I'm still puzzling over why your formula isn't working.

My guess is that the information pulled in by the query might be coming into columns H and I as text rather than numbers. It doesn't seem to matter for the matching range (column H), but the summing range needs to be numbers (Excel will not automatically change them from text to values for you; just like a regular SUM()). Try an =ISTEXT(i2) to see...

 

[crabbyman]

I tried reformation the whole column of cells as numbers. I also tried manually reentering data into the cells and nothing happened that way either.

Don't know if that would be similar to your idea or not.