JAUrrutia9
Mon Aug 07 13:12:57 CDT 2006
When I replace Range("B4"). with Selection. it give me the value
offset from the present selected cell. What Is the command that will
tell it to give me the value offset from the inputed cell?
JAUrrutia9@gmail.com wrote:
> Thanks for the advice Excelenator.
> I've spent the last hour or two trying to make this work, but it
> doesn't do me much good because it offsets from the pVal. The pVal is
> not the selected cells, it's not were the output is displayed.
>
> I need to find a way to have the formula CalcDate( pVal ) in cell B4,
> and drag it into from B4:F9 and have the offset change in each new cell
> so that a different value is drawn when I drag the formula over.
>
> Incase this doesn't make sense, I don't know how well I'm describing
> it, let me illustrate.
>
> when B4=Calcdate(pVal), the offset tell us to move 8 rows down and 13
> colomns over and to return the value it finds there (which is 26).
> Now when I drag the formula in b4 into B5, I want the offset to move
> 8rows down and 13 columns over from B5 now to get the value (which is
> 7).
> How Can I write this into my formula so that I don't have to create a
> new formula for every cell?
>
> This is what I'm leaning towards right not, but it's still not
> write--closer, but not right.
>
> Function CalcDate(pVal As Range)
> If pVal.Value = "1" Then
> CalcDate = Range("B4").Offset(8, 13).Value
> ElseIf pVal = "8" Then
> CalcDate = Range("B4").Offset(8, 13).Value
> End If
>
> End Function
>
>
> Excelenator wrote:
> > Instead of taking in a parameter as a string, "pVal as String" use "PVal
> > as range".
> >
> >
> > Code:
> > --------------------
> > Function CalcDate(pVal As Range) As Long
> > If pVal.value = "1" Then
> > CalcDate = pVal.offset(rows from pval, cols from pval).value
> > ...
> > --------------------
> >
> >
> >
> > JAUrrutia9@gmail.com Wrote:
> > >
> > >
> > > Function CalcDate(pVal As String) As Long
> > >
> > >
> > > If pVal = "1" Then
> > > CalcDate = Range("I4").Value
> > >
> > >
> > > ElseIf pVal = "2" Then
> > > CalcDate = Range("I12").Value
> > >
> > >
> > > ElseIf pVal = "3" Then
> > > CalcDate = Range("I20").Value
> > >
> > >
> > > ElseIf pVal = "4" Then
> > > CalcDate = Range("I28").Value
> > >
> > >
> > > ElseIf pVal = "5" Then
> > > CalcDate = Range("I36").Value
> > >
> > >
> > > ElseIf pVal = "6" Then
> > > CalcDate = Range("I44").Value
> > >
> > >
> > > ElseIf pVal = "7" Then
> > > CalcDate = Range("O4:S4").Value
> > >
> > >
> > > ElseIf pVal = "8" Then
> > > CalcDate = Range("O12:S12").Value
> > >
> > >
> > > ElseIf pVal = "9" Then
> > > CalcDate = Range("O20").Value
> > >
> > >
> > > ElseIf pVal = "10" Then
> > > CalcDate = Range("O28").Value
> > >
> > >
> > > ElseIf pVal = "11" Then
> > > CalcDate = Range("O36").Value
> > >
> > >
> > > ElseIf pVal = "12" Then
> > > CalcDate = Range("O44").Value
> > >
> > >
> > > Else
> > > CalcValue = 0
> > > End If
> > >
> > >
> > > End Function
> > >
> > >
> > > .....Please help!
> >
> >
> > --
> > Excelenator
> >
> >
> > ------------------------------------------------------------------------
> > Excelenator's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36768
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=568979