Basic Question

[JAUrrutia9]

....I hope. I just started doing my own VBA Programming and I'm stuck.
I've created a formula that is just a series of nested ifs (12). I'd
like to be able to dragged the cell containing the formula and have the

REF change as I drag it, but right now the formula is not variable and
REF's only one set of cells when I try to drag it. here's my
formula...

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]

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
...
--------------------

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!

 

[JAUrrutia9]

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

 

[JAUrrutia9]

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?

 

[Excelenator]

It sounds like you need another variable in the function declaration to
hold the place of the "Input Cell" (ie B4)

Function CalcDate(pVal As Range, Sel as Range)
If pVal.Value = "1" Then
CalcDate = Sel.Offset(8, 13).Value
ElseIf pVal = "8" Then
CalcDate = Sel.Offset(8, 13).Value
End If
....