here's one way - go to tools/options/general/select R1C1. Then, for each of
these formulas, you may see something like
=MAX(RC[3]:r[299]c[3]) for example, depending on where the =MAX(H1:H300) was
entered. In any case, drag across the formula in the formula bar, then in
the VBA you can use:
Range("Whatever").FormulaR1C1 = "=MAX(RC[3]:r[299]c[3])"
Get the idea?

Oh -- revisit tools/options/general, and change R1C1 back! (deselect it)

"rroach" wrote in
message ...

I need some help figuring out how to convert formulas that I have
working in an excel spreadsheet into VBA language in Excel. I have the
following three formulae that do just what I want. I go through all
kinds of hoops to open excel, write this formula in a cell, copy it
then write it to a new spreadsheet. would be much faster if i could
capture the value of all three formulae in VBA. Suggestions
appreciated:

=max(h1:h300)

=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h 300,0)-1,0)))

=ROW(OFFSET(A1,COUNTA(A:A)-1,0))

What do I need to do to get each of those working. I think if I can
figure these out then the generic rules can be used to apply to other
examples.

Thanks in advance,

Rob


--
rroach
------------------------------------------------------------------------
rroach's Profile:
http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=386564