Converting to R1C1 in FormulaArray

  • Thread starter Thread starter Ken McLennan
  • Start date Start date
K

Ken McLennan

G'day there One & All, it's me again...

This time around I'm having a problem converting one of John
Walkenshaw's Array Formulae to R1C1 addressing as the Help file tells me
I'm supposed to.

It's not that difficult, but I can't figure out how to put the
references in. Here's what I just tried. I didn't expect it to work, but
the combinations of quotes (",',`) and ampersands I've already used
didn't work either. I'm sure someone out there knows how it's done.

All assistance will be gratefully accepted.

Selection.FormulaArray = "=index(data,small(if(match(data,data,0)
=row(indirect(R2C8:R[rows(data)]C8)),match(data,data,0),''),row(indirect
(R2C8:R[rows(data)]C8))))"


Thanks in advance,
Ken McLennan
Qld, Australia
 
G'day there One & All, it's me again...
This time around I'm having a problem converting one of John
Walkenshaw's Array Formulae to R1C1 addressing as the Help file tells me
I'm supposed to.
Click to expand...

Please disregard this request. I didn't even get the question
right this time around =).

For some reason I can't get the formula into the range object,
after several changes, but I'm still working on it =)

See ya,
Ken McLennan
Qld, Australia
 
Try John Walkenbach's arrayformula in A1 reference style.

You might be pleasantly surprised.





Ken said:
G'day there One & All, it's me again...

This time around I'm having a problem converting one of John
Walkenshaw's Array Formulae to R1C1 addressing as the Help file tells me
I'm supposed to.

It's not that difficult, but I can't figure out how to put the
references in. Here's what I just tried. I didn't expect it to work, but
the combinations of quotes (",',`) and ampersands I've already used
didn't work either. I'm sure someone out there knows how it's done.

All assistance will be gratefully accepted.

Selection.FormulaArray = "=index(data,small(if(match(data,data,0)
=row(indirect(R2C8:R[rows(data)]C8)),match(data,data,0),''),row(indirect
(R2C8:R[rows(data)]C8))))"

Thanks in advance,
Ken McLennan
Qld, Australia
Click to expand...
 
G'day there Dave,
Try John Walkenbach's arrayformula in A1 reference style.
Click to expand...

Doh!!! I got his name wrong!!!

Sorry about that John!

You might be pleasantly surprised.
Click to expand...

OK then. I'll follow your advice and work with A1 to see what
happens. I hope it won't result in a loud explosion though. I've got a
headache =)

See ya
Ken McLennan
Qld Australia
 
Back
Top