PDA

View Full Version : Referencing Cells



epanagio
07-20-2006, 07:38 AM
How do I reference cells in a function? I insert the function in cell C11 and the function takes the contents of the cell B11 and does something with it.

Thanks,

Evan

lucas
07-20-2006, 07:59 AM
Hi Evan,
Not exactly sure what your question is but you can use native functions like sum, etc like this:
=B11*2
If you put this in cell C11 and you have a 5 in B11 then C11 will show 10
If your talking about user defined functions thats a little different.

epanagio
07-20-2006, 08:04 AM
Oops,
I am trying to create a custom VB function that will take the contents of the cell directly to the left and operate on its contents.

lucas
07-20-2006, 08:38 AM
Can we see your function?

lucas
07-20-2006, 09:25 AM
A function is code in a module that is called from a cell.
You need to add your function to a new module and then call it from a cell.
In this example the call to the function is made from cell C2(can be anywhere)
The call makes reference to cell A2. This is where you put your temp to be converted.
The call in cell C2 does 2 things. It finds the number in cell A2 and then runs the function from
the module on it to come up with a result.
The call for the function that is in cell C2 looks like this:
=CELSIUS(A2)
The code for the Module is:

Public Function Celsius(Degrees) As Double
Celsius = (Degrees - 32) * (5 / 9)
End Function

epanagio
07-20-2006, 09:33 AM
Function mileage()
If <cell reference> = "PHS" Then mileage = 0.4
REM there will be other calculations below

End Function

lucas
07-20-2006, 10:29 AM
This will do that for you I think:
=IF(A1="PHS",0.4,"")

Cyberdude
07-20-2006, 11:42 AM
I'm still not totally clear on what you are doing with the VBA function, but remember that you cannot use a VBA function in a spreadsheet formula IF that function modifies anything on the spreadsheet. All that a VBA function is allowed to do is perform some calculations and return a value to the formula that references it. If Excel thinks that you are doing some kind of change, then you get #VALUE! as the returned value. I know, I know, but that's the way it works. Another macro can reference the same function and it won't care if the function changes something on the worksheet.

epanagio
07-20-2006, 11:57 AM
In cell B3 I enter the word PHS and in cell A3 the word Morse, the names of two of our schools. Then, in cell C3 I am trying to have a function that will calculate the mileage reinbursment for those two schools. So, the distance between those two schools is 1.2 miles. Multiply that by 0.456 $/mile and you get the reinbesment.

In order to calculate the distance I need to detect the name from the other two cells. Now, I got two drop-downs with the names.

Thanks,

Evan

Cyberdude
07-20-2006, 12:26 PM
OK, sounds like you need a VBA macro (function) to do this.
Roughly how many different school names will there be? I'm wonderiing how many pairs are involved.
Do you already know the distance between each possible pair of schools, or is this something you want to compute using other available data?

Do you actually want to compute the cost in dollars to travel the distance between each possible pair of schools? Does the cost rate depend on which pair of schools you choose?

Sounds like you're going to need to set up a table of distances, a table of school pairs, and a table of cost rates based on school pairs.

epanagio
07-20-2006, 12:39 PM
Hey Cyberdude,

Thanks for the help. I know this can be done with a bunch of simple if...then statements. My difficulty is referencing the cells. Having Excel running, I will press Alt+F11 and then I will write a VBA function. However, I would like to know how do reference my cell directly to the left of where the result will go.

A1=PHS
B1=Morse
C1= ??????

I would like to reference the cells to the left of C1.

Thanks,

Evan

malik641
07-20-2006, 03:57 PM
Hi Evan, and welcome

I would use a formula for this situation. It's faster than VBA functions and it doesn't sound like it would be too complicated for what you are trying to do.

I would do what Sid (cyberdude) said and make a table of different combinations of schools and distances. I would also create dynamic named ranges for these 3 columns (and I would put it on another sheet and hide it so nobody can accidentally change something). Then you could just use a very simple IF & VLOOKUP formula (or an EASY sumproduct formula) to find the combination that was chosen in the dropdowns.

And if you ever needed to add combinations (i.e. schools) it would take less time to place them in cells and let the formula handle it rather than putting more code and typing more.

That's just how I would do it....if you still want a function, you could do that too :)

malik641
07-20-2006, 04:37 PM
Here's a small example of what I think you are trying to do. Notice the dynamic named ranges.

HTH :thumb

epanagio
07-21-2006, 10:14 AM
Hey Guys,

Great forum! I will check the example.

Thanks for your help!

Evan