vba offset program

[ctallen23 via OfficeKB.com]

Hi. I have another question. I am trying to post this: OFFSET($A$4,MATCH
(C21,$A$5:$A$17,0),MATCH(D21,$B$4:$M$4,0)) into a vba program but can't get
it to work. I understand that the $$ are not recognized in vba and also, how
would I go about putting this equation into a sub procedure? I tried calling
it price = offset(....... Also, this is a set range that will not move. How
can the program be structured to where $a$4 will always be the range or
array's starting point? I have stock symbols in a row with dates down the
left column. Stock prices fill the range.

thansk.

chris

 

[Gary Keramidas]

not sure what you want. this enters the formula in A1

Range("A1").Formula = "=
OFFSET($A$4,MATCH(C21,$A$5:$A$17,0),MATCH(D21,$B$4:$M$4,0))"

 

[Guest]

I haven't tested this code, but it should work. Because you hard coded
location with $ they will never change. So you can put these locations
directly into the function.

=getstockrange(c21,D21)

Function getstockrange(findparm1 As range, _
findparm2 As range) As Range

Set c1 = Range("A5:A17"). _
Find(findparm1, LookIn:=xlValues)
Set c2 = Range("B4:M4"). _
Find(findparm2, LookIn:=xlValues)

Set getstockrange = Range(Range("A4"), _
Cells(c1.Row, c2.Column))

End Function

----------------------------------------------------------------------------
another way is the following

=getstockrange($A$4,c21,$A$5:$A$17,D21,$B$4:$M$4)

Function getstockrange(parm1 As range, _
parm2 As range, _
parm3 As range, _
parm4 As range, _
parm5 As range) As Range

Set c1 = parm3.Find(parm2, LookIn:=xlValues)
Set c2 = parm5.Find(parm4, LookIn:=xlValues)

Set getstockrange = Range(parm1, _
Cells(c1.Row, c2.Column))

End Function

 

[ctallen23 via OfficeKB.com]

I haven't tested this code, but it should work. Because you hard coded
location with $ they will never change. So you can put these locations
directly into the function.

=getstockrange(c21,D21)

Function getstockrange(findparm1 As range, _
findparm2 As range) As Range

Set c1 = Range("A5:A17"). _
Find(findparm1, LookIn:=xlValues)
Set c2 = Range("B4:M4"). _
Find(findparm2, LookIn:=xlValues)

Set getstockrange = Range(Range("A4"), _
Cells(c1.Row, c2.Column))

End Function

----------------------------------------------------------------------------
another way is the following

=getstockrange($A$4,c21,$A$5:$A$17,D21,$B$4:$M$4)

Function getstockrange(parm1 As range, _
parm2 As range, _
parm3 As range, _
parm4 As range, _
parm5 As range) As Range

Set c1 = parm3.Find(parm2, LookIn:=xlValues)
Set c2 = parm5.Find(parm4, LookIn:=xlValues)

Set getstockrange = Range(parm1, _
Cells(c1.Row, c2.Column))

End Function

Hi. I have another question. I am trying to post this: OFFSET($A$4,MATCH
(C21,$A$5:$A$17,0),MATCH(D21,$B$4:$M$4,0)) into a vba program but can't get

[quoted text clipped - 8 lines]

chris

I never knew about the hard coding aspect of VBA. Just barely scratching the
surface with this. I will try this shortly to see what I get. Thank you.

chris

 

[ctallen23 via OfficeKB.com]

Is there a way of making this a Sub procedure to where who ever is using it
can simply click the macro and have two questions asked: 1) date, 2) stock
symbol and then the procedure will output via Msgbox the price? thanks again.



chris