Function Question

I have two cells in a custom fuction.

A9 ' For example
The cell the function is in.

A9 is passed to the function as a arguement.

What I need to do is find the ROW offset of A9 and the cell the
function is in.

Can anyone help with this? I'm going to have to do the same thing with
some Column offsets as well, but I think if I can get over the ROW
offset problem, I think I can get around the Column offset as it should
be similar.

TIA
J. Hall

This should do it

Function RowOffset(rng1 As Range)
RowOffset = Application.Caller.Row - rng1.Row
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Egon" <jhalljr@gmail.com> wrote in message
news:1132678547.750785.52000@g47g2000cwa.googlegroups.com...
> I have two cells in a custom fuction.
>
> A9 ' For example
> The cell the function is in.
>
> A9 is passed to the function as a arguement.
>
> What I need to do is find the ROW offset of A9 and the cell the
> function is in.
>
> Can anyone help with this? I'm going to have to do the same thing with
> some Column offsets as well, but I think if I can get over the ROW
> offset problem, I think I can get around the Column offset as it should
> be similar.
>
> TIA
> J. Hall
>

Below you will see the entire fuction, I have the beginning and ending
pieces working, however the middle is not doing very well. Thanks for
the RowOffset Piece, that worked great Bob, but my problem is that its
not acting as I would expect. The Comments will tell you what I'm
expecting it to do. Maybe someone can lend a hand and tell me how to
fix it and what I've done wrong.

TIA

J.

Function DailyCost(TrackDate)

Celladdress = Application.ThisCell.Address
ProperRow = Application.Caller.Row - TrackDate.Row
'Works To here

'Pull the Rate for Fuel from the Cell H7 on the same sheet as the
Function
FuelRateMonthly = ActiveSheet.Range("H7")

'Set the location of the information where the values will be pulled
from
TrackingInfo = Worksheets("Project Summary").Range("G9")

'Calculate the fuel usage for the day based on the amount used
*rate*conversion
FuelRate = WorksheetFunction.Sum(FuelRateMonthly * FuelUsage * 264.15)

'Set StartDate equal to the cell G9 on the 'Project Summary' Sheet
using a Relative Reference
StartDate = TrackingInfo.Range.Offset(ProperRow, 0).Value

'Set EndDate equal to the cell H9 on the 'Project Summary' Sheet using
a Relative Reference
EndDate = TrackingInfo.Range.Offset(ProperRow, 1).Value

'Set TrackingRate equal to the cell I9 on the 'Project Summary' Sheet
using a Relative Reference
TrackingRate = TrackingInfo.Range.Offset(ProperRow, 2).Value

'Set DailyRate to the FuelRate + TrackingRate
DailyRate = WorksheetFunction.Sum(FuelRate, TrackingRate)

'Set Mobe equal to the cell J9 on the 'Project Summary' Sheet using a
Relative Reference
Mobe = TrackingInfo.Range.Offset(ProperRow, 3).Value

'Set MobeDate equal to the cell K9 on the 'Project Summary' Sheet using
a Relative Reference
ModeDate = TrackingInfo.Range.Offset(ProperRow, 4).Value

'Set DemobeDate equal to the cell M9 on the 'Project Summary' Sheet
using a Relative Reference
DeMobeDate = TrackingInfo.Range.Offset(ProperRow, 6).Value

'Set DeMobe equal to the cell L9 on the 'Project Summary' Sheet using a
Relative Reference
DeMobe = TrackingInfo.Range.Offset(ProperRow, 5).Value

'This Section Works
EndDateNull = IIf(EndDate = "", DailyRate, 0)
TrackEnd = IIf(TrackDate <= EndDate, DailyRate, EndDateNull)
TrackToday = IIf(TrackDate >= StartDate, TrackEnd, 0)
StartTracking = IIf(Now >= TrackDate, TrackToday, 0)
DailyCost = StartTracking

I wouldn't hardcode the FuelRateMonthly or TrackingInfo details, you should
have UDF arguments for these, and pass them ehwn calling.

FuelUsage is never defined or set in the code.

I don't kknow what you are trying to do here
Mobe = TrackingInfo.Range.Offset(ProperRow, 3).Value
as TrackingInfo is just a value so you can't offset it. Perhaps if you pass
TrackingInfo as a range argument, you could then use
Mobe = TrackingInfo.Offset(ProperRow, 3).Value

etc.



RP
(remove nothere from the email address if mailing direct)


"Egon" <jhalljr@gmail.com> wrote in message
news:1132682190.103792.239460@g43g2000cwa.googlegroups.com...
> Below you will see the entire fuction, I have the beginning and ending
> pieces working, however the middle is not doing very well. Thanks for
> the RowOffset Piece, that worked great Bob, but my problem is that its
> not acting as I would expect. The Comments will tell you what I'm
> expecting it to do. Maybe someone can lend a hand and tell me how to
> fix it and what I've done wrong.
>
> TIA
>
> J.
>
> Function DailyCost(TrackDate)
>
> Celladdress = Application.ThisCell.Address
> ProperRow = Application.Caller.Row - TrackDate.Row
> 'Works To here
>
> 'Pull the Rate for Fuel from the Cell H7 on the same sheet as the
> Function
> FuelRateMonthly = ActiveSheet.Range("H7")
>
> 'Set the location of the information where the values will be pulled
> from
> TrackingInfo = Worksheets("Project Summary").Range("G9")
>
> 'Calculate the fuel usage for the day based on the amount used
> *rate*conversion
> FuelRate = WorksheetFunction.Sum(FuelRateMonthly * FuelUsage * 264.15)
>
> 'Set StartDate equal to the cell G9 on the 'Project Summary' Sheet
> using a Relative Reference
> StartDate = TrackingInfo.Range.Offset(ProperRow, 0).Value
>
> 'Set EndDate equal to the cell H9 on the 'Project Summary' Sheet using
> a Relative Reference
> EndDate = TrackingInfo.Range.Offset(ProperRow, 1).Value
>
> 'Set TrackingRate equal to the cell I9 on the 'Project Summary' Sheet
> using a Relative Reference
> TrackingRate = TrackingInfo.Range.Offset(ProperRow, 2).Value
>
> 'Set DailyRate to the FuelRate + TrackingRate
> DailyRate = WorksheetFunction.Sum(FuelRate, TrackingRate)
>
> 'Set Mobe equal to the cell J9 on the 'Project Summary' Sheet using a
> Relative Reference
> Mobe = TrackingInfo.Range.Offset(ProperRow, 3).Value
>
> 'Set MobeDate equal to the cell K9 on the 'Project Summary' Sheet using
> a Relative Reference
> ModeDate = TrackingInfo.Range.Offset(ProperRow, 4).Value
>
> 'Set DemobeDate equal to the cell M9 on the 'Project Summary' Sheet
> using a Relative Reference
> DeMobeDate = TrackingInfo.Range.Offset(ProperRow, 6).Value
>
> 'Set DeMobe equal to the cell L9 on the 'Project Summary' Sheet using a
> Relative Reference
> DeMobe = TrackingInfo.Range.Offset(ProperRow, 5).Value
>
> 'This Section Works
> EndDateNull = IIf(EndDate = "", DailyRate, 0)
> TrackEnd = IIf(TrackDate <= EndDate, DailyRate, EndDateNull)
> TrackToday = IIf(TrackDate >= StartDate, TrackEnd, 0)
> StartTracking = IIf(Now >= TrackDate, TrackToday, 0)
> DailyCost = StartTracking
>

how do I pass the TrackingInfo as a range arguement?

Sorry, I'm kinda new at this..

J.

Function DailyCost(TrackDate, TrackingInfo As Range)


=DailyCost(A6, A7)

etc.

because the two arguments are of differing data types, they evaluate
differently.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Egon" <jhalljr@gmail.com> wrote in message
news:1132687584.775904.218170@g14g2000cwa.googlegroups.com...
> how do I pass the TrackingInfo as a range arguement?
>
> Sorry, I'm kinda new at this..
>
> J.
>

It's hard to tell what you are talking about. I'm guessing you want to know
the number of rows between the cell you are entering the formula into and
the cell A9. Try entering this:

=Row() - Row(A9)

which is the same as
= Row() - 9



"Egon" <jhalljr@gmail.com> wrote in message
news:1132678547.750785.52000@g47g2000cwa.googlegroups.com...
> I have two cells in a custom fuction.
>
> A9 ' For example
> The cell the function is in.
>
> A9 is passed to the function as a arguement.
>
> What I need to do is find the ROW offset of A9 and the cell the
> function is in.
>
> Can anyone help with this? I'm going to have to do the same thing with
> some Column offsets as well, but I think if I can get over the ROW
> offset problem, I think I can get around the Column offset as it should
> be similar.
>
> TIA
> J. Hall
>