Calculation within VB

[jcvoth]

Greetings all!

I would like to find a way to solve simple equations through VB and print the result in something like a msgbox.

Here is an example:

******** ******************** ************************************************************************>

Microsoft Excel - Book4 ___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D
1	Name	Sales	*	*
2	Bob	4	*	*
3	Joe	3	*	*
4	Jim	2	*	*
5	Jane	5	*	*
6	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What I would like is to click a command button, which will trigger a new sub that will sum the range B1:B5 and print the result [14] in a msgbox.

I know how to push an equation into a particular cell, but I would like to run the whole calculation through VB.

Thank you all for your help!
-Jarrod

[erik.van.geit]

jcvoth,
is this what you are searching for ?

MsgBox Application.WorksheetFunction.Sum(Range("B1:B5"))

(why did you include B1 in your question? there is no number)

regards,
Erik

[jcvoth]

Hi Erik,

Yes, this is exactly what I was looking for.

(The B1 was a typo on my part. Should have been B2:B5.)

The sum is an overly simplistic example of what I would like to do. Eventually, I want to push most of my time-intensive calculations into VB such as vlookups, etc.

Thanks for your help!
-Jarrod

[HalfAce]

Or to add a little text to the message....
Use Erik's idea like so:

MsgBox "Your total is " & Application.WorksheetFunction.Sum(Range("B1:B5"))

[erik.van.geit]

you're welcome !
Erik

[jcvoth]

Okay, so I am getting there, but the vlookup function is failing.

Here is my code thus far:

Private Sub CommandButton1_Click()
lastrow = Range("A65536").End(xlUp).Row
For i = 1 To lastrow
Range("b" & i) = Application.WorksheetFunction.VLookup(Range("a" & i), Range("h2:i27"), 2, False)
i = i + 1
Next i
End Sub

Essentially I am looking up every value in column A and finding its corresponding value in the lookup series H2:I27. I am then printing the results in the B column.

The error I receive is Unable to get the vlookup property of the worksheet class.

Help!
-Jarrod

[erik.van.geit]

did you have a good reason to put "i = i + 1" in it ?
that's just what "next" does, so you don't need to add 1 each time, Excel does it for you !

"unable to get ..." : I'm not 100% sure of my English here but probably Excel doesn't find the value Range("a" & i) in Range("h2:i27")

go through your code with the functionkey F8 to find the reason of your bug

it's time to zzzzzzzzzzzzzz for me (03:15 AM here)

bye,
Erik

[jcvoth]

You were right about the i+1... I noticed that shortly after posting. And I was able to fix the vlookup code itself after some continued testing. Thanks for your help!

The final sub was:

Private Sub CommandButton1_Click()
lastrow = Range("A65536").End(xlUp).Row
Set lookuprange = Worksheets("Sheet1").Range("h2:i27")
For i = 2 To lastrow
Range("b" & i) = Application.WorksheetFunction.VLookup(Range("A" & i), lookuprange, 2, False)
Next i
End Sub


-Jarrod