VBA Question

[Steve]

Hi:

I have BASIC programming skills but have never had the opportunity to
use VBA inside Excel.

Using a UDF, I need to know how to take the contents of a cell and
pass it to a variable inside my user defined function.

I am trying to take the results in one cell and if it matches, then
take the information from two other cells and multiply them together.

I've poured over the VBA documentation and see how to push a value
into a cell, but can't find any examples of retrieving a cell's
content.

Thanks in advance!

--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
35 Years of G.M. Parts Experience
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)

 

[Bob Phillips]

Function myFunction(rng As Range)

If rn.Cells.Count > 1 Then

myFunction = CVErr(xlErrValue)
Else

MsgBox rng.Value
End If
End Function

and use in the worksheet lik so

=myFunction(H10)

 

[Don Guillett]

Functions must be placed in a REGULAR module instead of a sheet module.
=mi(d1) where d1 has a number

Function mi(x)
Application.Volatile
mi = x * (Range("e1") + Range("e2"))
End Function

 

[Steve]

Bob:

Thanks for the super quick reply; however, either I'm completely lost
or you've misunderstood what I originally asked. Let me try again and
if you get a stupid look on your face, I'll know it was me... LOL!

Column B has a numerical value in it.
If the numerical value in A36 (just an arbitrary cell) happens to
match the value in B2, then
multiply the values in C2 and D2 to get a product (ie: total =
value(c2) * value(d2) )

I don't understand how to get the contents of c2 or d2 into a variable
inside my function since they aren't passed as an argument.

Here's the logic in pseudocode:

If Value(B2) = Value(A36)
Then
Product = Value(C2) * Value(D2)
Total = Total + Product
Endif

The value being searched for in B2 could be listed again in another
cell in the same column.


--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
35 Years of G.M. Parts Experience
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)
| Function myFunction(rng As Range)
|
| If rn.Cells.Count > 1 Then
|
| myFunction = CVErr(xlErrValue)
| Else
|
| MsgBox rng.Value
| End If
| End Function
|
| and use in the worksheet lik so
|
| =myFunction(H10)
|
|
| --
| __________________________________
| HTH
|
| Bob
|
| | > Hi:
| >
| > I have BASIC programming skills but have never had the opportunity
to
| > use VBA inside Excel.
| >
| > Using a UDF, I need to know how to take the contents of a cell and
| > pass it to a variable inside my user defined function.
| >
| > I am trying to take the results in one cell and if it matches,
then
| > take the information from two other cells and multiply them
together.
| >
| > I've poured over the VBA documentation and see how to push a value
| > into a cell, but can't find any examples of retrieving a cell's
| > content.
| >
| > Thanks in advance!
| >
| > --
| > Steve Spence
| > Independent AMSOIL Dealer
| > AMSOIL - The "Once A Year" Oil Change
| > 35 Years of G.M. Parts Experience
| > URL: http://synthetic-oil-tech.com/1690163
| > Email: (e-mail address removed)
| >
| >
|
|

 

[Don Guillett]

Function mi(x)
Application.Volatile
If x = Range("d1") Then mi = x * (Range("e1") + Range("e2"))
End Function

 

[Steve]

Don:

Thanks! That looks like what I need. Let me incorporate that into my
code and see what happens. I looked through the documentation for two
hours yesterday trying to find the "magic bullet". I think I looked
at "range" but surely didn't understand the explanation that MS
offered in the VBA help file.

--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
35 Years of G.M. Parts Experience
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)
| Function mi(x)
| Application.Volatile
| If x = Range("d1") Then mi = x * (Range("e1") + Range("e2"))
| End Function
|
| --
| Don Guillett
| Microsoft MVP Excel
| SalesAid Software
| (e-mail address removed)
| | > Bob:
| >
| > Thanks for the super quick reply; however, either I'm completely
lost
| > or you've misunderstood what I originally asked. Let me try again
and
| > if you get a stupid look on your face, I'll know it was me... LOL!
| >
| > Column B has a numerical value in it.
| > If the numerical value in A36 (just an arbitrary cell) happens
to
| > match the value in B2, then
| > multiply the values in C2 and D2 to get a product (ie:
total =
| > value(c2) * value(d2) )
| >
| > I don't understand how to get the contents of c2 or d2 into a
variable
| > inside my function since they aren't passed as an argument.
| >
| > Here's the logic in pseudocode:
| >
| > If Value(B2) = Value(A36)
| > Then
| > Product = Value(C2) * Value(D2)
| > Total = Total + Product
| > Endif
| >
| > The value being searched for in B2 could be listed again in
another
| > cell in the same column.
| >
| >
| > --
| > Steve Spence
| > Independent AMSOIL Dealer
| > AMSOIL - The "Once A Year" Oil Change
| > 35 Years of G.M. Parts Experience
| > URL: http://synthetic-oil-tech.com/1690163
| > Email: (e-mail address removed)
| > | > | Function myFunction(rng As Range)
| > |
| > | If rn.Cells.Count > 1 Then
| > |
| > | myFunction = CVErr(xlErrValue)
| > | Else
| > |
| > | MsgBox rng.Value
| > | End If
| > | End Function
| > |
| > | and use in the worksheet lik so
| > |
| > | =myFunction(H10)
| > |
| > |
| > | --
| > | __________________________________
| > | HTH
| > |
| > | Bob
| > |
| > | | > | > Hi:
| > | >
| > | > I have BASIC programming skills but have never had the
opportunity
| > to
| > | > use VBA inside Excel.
| > | >
| > | > Using a UDF, I need to know how to take the contents of a cell
and
| > | > pass it to a variable inside my user defined function.
| > | >
| > | > I am trying to take the results in one cell and if it matches,
| > then
| > | > take the information from two other cells and multiply them
| > together.
| > | >
| > | > I've poured over the VBA documentation and see how to push a
value
| > | > into a cell, but can't find any examples of retrieving a
cell's
| > | > content.
| > | >
| > | > Thanks in advance!
| > | >
| > | > --
| > | > Steve Spence
| > | > Independent AMSOIL Dealer
| > | > AMSOIL - The "Once A Year" Oil Change
| > | > 35 Years of G.M. Parts Experience
| > | > URL: http://synthetic-oil-tech.com/1690163
| > | > Email: (e-mail address removed)
| > | >
| > | >
| > |
| > |
| >
| >
|

 

[Bob Phillips]

You said ... I need to know how to take the contents of a cell and pass it
to a variable inside my user defined function..., so I gave you the
technique, I expected you to be able to change the MsgBox to your particular
code.

 

[Steve]

| You said ... I need to know how to take the contents of a cell and
pass it
| to a variable inside my user defined function..., so I gave you the
| technique, I expected you to be able to change the MsgBox to your
particular
| code.
|
| --
| __________________________________
| HTH
|
| Bob


Bob:
Thanks again. I just don't know enough about VBA programming to know
my way around. I was a petty good BASIC programmer but haven't used
the skills in many years (since before Visual Basic was invented) and
my BASIC programming skills are DOS based (am I showing my age?).

I needed more than just the technique. I needed to know the syntax to
use since I have virtually no programming time using Visual Basic for
Applications and don't know the reserved words for Excel to make
things happen.

All I really have knowledge of is the BASIC programming languate and a
solid base of logic skills. I know what I want to happen, but like
learning a new language, I haven't got a clue how to say it.

I'm still struggling with the function. I'm going to work on it this
weekend and scream for help if I still don't have it working by Sunday
evening.

Thanks for the great help resources here. This is a great place for
those of us who are still using crutches.

--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
35 Years of G.M. Parts Experience
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)