manipulating ranges/arrays with if/then statements?

[losmi8]

A B C D
--------------------
Mike Fax 5 60
John Tel 3 30
Mike Tel 2 30
Mike Fax 1 60
John Fax 1 60

--------------------
hi all, i am faced with the following problem. before i describe the problem, i do not need help with recording a macro or using functions to solve the above problem, i know how to do that.

i need help writing a code from scratch to do the following:

I need a code that will run through the above columns, and IF A=Mike and B=Fax, THEN C*D...so I want to calculate a total for everytime Mike sells a Fax (or a Tel). Column C is quantity, Column D is price.

I am sure this is a simple loop code, but I have spent days trying to figure it out and I can not do it. Recording a macro is useless, because it is too confusing to look at the code and play with it and try to personalize it.


Can someone please help me with this code. I would greatly appreciate it.

Thank you!

[losmi8]

Also, IF A=Mike and B=Tel instead, then I wouldn't want to multiply C and D. I would like the code to skip that row and continue searching for where A=Mike and B=Fax and only then do the =C*D.

And of course, I need to add up all of the results (so all the C*D's).


If anyone needs further explanation of what I need, please say so. I know I am explaining this in a very confusing way. This is what I need any type of a code from scratch so I can try and customize it to what I need..

[DonkeyOte]

Perhaps explain why you need Code exactly ?

=SUMPRODUCT(--(A1:A10="Mike"),--(B1:B10="Fax"),C1:C10,D1:D10)

If VBA is really necessary (for whatever reason) then the above can be executed in memory via Evaluate.

[losmi8]

I have a small company, and I want to automate the data I gather ...so that Excel can just give me the output, which I would then enter into Quickbooks.

I am just trying to learn macro, because I've done some very small things with it and it has made my life easier and my job more fun.

[losmi8]

And yes, I do want a VBA.

I can do this in Excel and its fine. But I want a code because I want to customize it and expand its usefulness. I just need someone to get me started with the code that can do the above. I have a few eBooks and I bought some books on VBA, and none of them can get me to do this. It is getting frustrating.

[losmi8]

So, I'd want a Cell "E1" to = C*D + C*D (for each row that has A=Mike and B=Fax)

And then I'd like to try and manipulate the code to for instance give me the total of all C's in cell E2. Or to print revenue (column D) in E3, etc...

But I can't figure out how to write the loop that will go through the columns A, B and match them up and according to the match perform a simple function.

[DonkeyOte]

So...

Please Login or Register  to view this content.

No need for iteration.

I think I'll leave this to others as I'm struggling to understand the necessity for VBA, calculations conducted natively are nearly always quicker than when conducted in VBA.

[losmi8]

Instead of the MsgBox, I'd want it to store that value in a different cell (E1 for instance).

The only reason I want a VBA, and from scratch, as I can learn from it and understand what it does exactly. A recorded macro is useless to me, hence I don't want to play with it.

I am primarily trying to teach myself VBA here. If I can figure out how to do this, I will be able to do more complex things in the future. And I will have that need..

[losmi8]

Anyone? Please help. I am sitting behind my computer screen trying to figure this out. I need some guidance

[losmi8]

Anyone....Please. I really need someone to get me started. I feel like I'm wasting my time just sitting here unable to get anything moving.

This doesn't sound that complicated that no one knows how to program it? Is it?

[losmi8]

bump.......

[Palmetto]

OK. Guess I'll "bite" on this, although, for the record, I wholeheartedly agree with DonkeyOte; native functions are inherently faster than VBA and should always be viewed as the option of choice.

That being said, there is usually more than one approach in VBA and the below code is as simple as I know to make it though, strictly speaking, it does not process an "array" as your thread title suggests.

This code is also not very flexible or robust as it only looks for "Mike" and "Fax" and there is no error handling. But since you are a beginner there is no sense in being overly technical or complicated.

Please Login or Register  to view this content.

[losmi8]

Palmetto! Thank you!!

You seem to get what I need!

I have another question for you if you dont mind. Well, maybe 2 questions

From your code:

c.Offset(0, 4).Value = c.Offset(0, 2).Value * c.Offset(0, 3).Value


How do I change this code so that the result of goes to a specific cell, let's say....G19?

******Okay, I figured this out. It would be:

Range("G19").Value = c.Offset(0, 2).Value * c.Offset(0, 3).Value


.....But it only prints the last total of C*D, instead of adding up all of them. If I could get that to work, then I think I would be set. Really set!

You're the man Palmetto! No one else didn't even want to tackle this problem.

[losmi8]

Also, your code just prints each result (whereever A=mike, b=fax...it does C*D, and print each C*D in corresponding E cell) ....I need those to add up and just print one SUM of all of them in any cell. Lets use G19 as the Total.

[Palmetto]

Please Login or Register  to view this content.

[losmi8]

WOW! You are good. Thank you! This is exactly what I was looking for!! Now I can play with it and hopefully customize it a bit.

Can you tell me why you didn't set Column B (where fax/tel stuff is) as a Range too? Instead you used the 1row thing?

I'm just trying to understand this completely.

If you do not have time to explain, that is find. You have helped me a ton and I appreciate your help! You've given me something to do on a Friday night haha.

Thanks again!!

[losmi8]

One more question, I promise IT IS THE LAST ONE


Lets say I wanted to Divide the total sum in range ("G19") by however many Faxes were sold.


So lets say, your formula adds up the following into G19:

Mike Fax 5 50
Mike Fax 3 50
Mike Fax 1 50

so G19=450

how can i make G20= G19/3, so that it equals 150.

So I would want the total to be divided by however many things went into calculating the total? Does that make sense?

Or if it is simpler, to just print the total of matches (where A=mike, B=fax) in G20. Then I can use excel functions to get two cells to divide.

[Palmetto]

Can you tell me why you didn't set Column B (where fax/tel stuff is) as a Range too? Instead you used the 1row thing?

"1row" is Lrow - the "L" is lowercase.

lrow is the name of a variable dimensioned as "Long" (See Excel VBA Help for explanations).

Column-B is referenced by offsetting from column-A range of cells.

Please Login or Register  to view this content.

[losmi8]

I'm reading up on it right now.

Is there a way for your code to count how many occurrences happen in the IF statement? (as i described above)

[Palmetto]

how can i make G20= G19/3, so that it equals 150.

So I would want the total to be divided by however many things went into calculating the total? Does that make sense?

Please Login or Register  to view this content.

[losmi8]

ok, so here is my customized code..

Sub mikefax()

Dim c As Range, lrow As Long, w As Range

lrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For Each c In Sheet1.Range("A1:A10" & lrow)
If c.Value = "Mike" And c.Offset(0, 1).Value = "Fax" Then
Range("w").Value = Range("w") + c.Offset(0, 3).Value
End If
Next c

Range("G21").Formula = "=Range("w") / SUMPRODUCT(--(A1:A10=""Mike""),--(B1:B10=""Fax""))"

Application.ScreenUpdating = True

End Sub

----

what i am trying to do is to store a value in Range("w") that will be divided at the end by SUMPRODUCT(--(A1:A10=""Mike""),--(B1:B10=""Fax""))"

i dont want to store it to an actual cell in excel, because i have no use for that number there...i just need it to do this calculation.

and how did you learn VBA? if you have any tutorials/books that you found very helpful, please let me know! i'd love to know how to write it like you do. it would be very cool. the possibilities are endless to a person that really knows VBA. im jealous

[DonkeyOte]

losmi8,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


Please edit your prior post(s) in accordance with the above.

[DonkeyOte]

I know Palmetto is illustrating the basics but the points re: succinct code and calculations remains... in post 7 you have the code to generate your G19 value, similar logic can be used to generate G20.

Combined:

Please Login or Register  to view this content.

The range references can of course be made to be dynamic using similar logic to that already outlined and if you don't want to write back the above to cells simply store as variables, ie

Please Login or Register  to view this content.

of course the dblAvg could debug if the Average returns #DIV/0! so you may choose to store the Average as a Variant type rather than a Double or alternatively calculate the Count as a variable and calc. avg (sum/count) only where count > 0
(ie the COUNT equates to the first SUMPRODUCT without the C:D arrays)

[losmi8]

do you know how to store the variable to be used later? as i highlighted above in red.

i want to save a variable (instead of putting in a cell to be visible) so i can used it later to divide it by another number..

[losmi8]

Sub mikefax()

Dim c As Range, lrow As Long, w As Range

lrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For Each c In Sheet1.Range("A1:A10" & lrow)
If c.Value = "Mike" And c.Offset(0, 1).Value = "Fax" Then
Range("w").Value = Range("w") + c.Offset(0, 3).Value
End If
Next c

Range("G21").Formula = "=Range("w") / SUMPRODUCT(--(A1:A10=""Mike""),--(B1:B10=""Fax""))"

Application.ScreenUpdating = True

End Sub

....

anyone know why this isn't working?

i wanted to store a value in "w" (range) and to use it later to divide it by the SUMPRODUCT formula. its giving me an error?

[DonkeyOte]

losmi8, my prior point regards code tags pertains to one of the most basic rules of the forum (to which you agreed when you registered).

As requested previously please edit your prior posts and add tags.

[losmi8]

sorry about that, i figured how to properly post the code now!

[DonkeyOte]

Please use CODE tags rather than QUOTE tags.

Manually, CODE Tags are constructed in the same way as QUOTE tags - ie replace QUOTE with CODE.

If doing via icons use # rather than the quote icon to wrap selected text within tags.