convert formula to VBA

  • Thread starter Thread starter rroach
  • Start date Start date
R

rroach

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:h300,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
 
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)
 
Assuming where you want the formulas is B6, B7 and B8...

Range("B6").Formula = "=max(h1:h300)"
Range("B7").Formula =
"=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))"
Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))"

HTH,
 
you can also copy a formula

range("A1").formula = range("A2").formula

About the other's people ideas, I prefer to use the
..FormulaLocalR1C1
or
..FormulaLocal

because what you see in the sheet is written in your local language,
but vba formula is in english
With FormulaLocal it's the lcal name (auto converted)

the R1C1 just mean you are using the R1C1 naming of cells, but you do
not seem to use it, so it's better to forget this R1C1 (and do not
change your options in the menu, keep the way you like )

VBA accept R1C1 or not R1C1 (just tell him which one you write)
 
thanks all.

next question is if I use:
Range("B6").Formula = "=max(h1:h300)"
Range("B7").Formula =
"=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))"
Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))"

for my 3 formulae, how do i set a variable to equal each of those
values for writing to a text file?

TIA,

Rob
 
who, take care of one thing: a string can not contains any "

or you must double the double-quotes

"abc""toto""def"
means
abc"toto"def

but
"=(CELL("address"

if supposed to give "=(CELL(" string followed by stuffs
for my 3 formulae, how do i set a variable to equal each of those
values for writing to a text file?
Click to expand...


I'm not sure to well understand what's your wish. Can you give an
example ?
 
In VBA,

Dim strB6 As String, strB7 As String, strB8 As String
strB6 = ActiveSheet.Range("B6").Value
strB7 = ActiveSheet.Range("B7").Value
strB8 = ActiveSheet.Range("B8").Value

HTH,
 
Back
Top