Hello,

I am new to macro's and I would be grateful for any advice:

I have a macro that looks at about 100 spreadsheets and performs
various calculations, below is an example:

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11)),"""")"

as you will see there is a constant reference to the last row (R6000) -
but I have added this manually as I know this is the last row of the
biggest spreadsheet - but there must be a way of calculating which the
last row is...but I am a bit dim and cant work out how to programme
it...

...I wonder if anyone else could help me with the code?

Thanks in advance.
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=507462

Jaime

If the data in R is contiguous, I use

Dim lLastRow as Long
lLastRow=Range("R65536").End(xlup).Row

You can then use the number in lLastRow in you range references

(XL97 on)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"jaimetimbrell"
wrote in message
news:jaimetimbrell.22kl8m_1138830003.5617@excelfor um-nospam.com...

Hello,

I am new to macro's and I would be grateful for any advice:

I have a macro that looks at about 100 spreadsheets and performs
various calculations, below is an example:

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11)),"""")"

as you will see there is a constant reference to the last row (R6000) -
but I have added this manually as I know this is the last row of the
biggest spreadsheet - but there must be a way of calculating which the
last row is...but I am a bit dim and cant work out how to programme
it...

..I wonder if anyone else could help me with the code?

Thanks in advance.
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile:
http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=507462

Dim rng As Range
Dim lMaxRow As Long

Set rng = ActiveSheet.UsedRange
lMaxRow = rng.Row + rng.Rows().Count - 1

use the actual sheet rather than activesheet. lMAxrow is the number o
the last used row

regard

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

Tony,

Thank you also for your very kind reply.

I have the same question for you as for Nick - how do I then use th
variable in the equation to substitute for R6000? (sorry if it is
dumb question!)

Regards,
Jaime

--
jaimetimbrel
-----------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...fo&userid=2616
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

lots of dirrefernt ways but suggest instead of R5C19:R6000C19 us
R5C19.resize(lmaxrow-4)

regard

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50746

sorry Tony,

I have tried the following (and variations) and I am not getting it to
work...

In the context of the original equation I have written...

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19.resize(lmaxrow-4)=ROWS(R5C1:RC[-24]).R5C11resize(lmaxrow-4))...etc

I wonder if you might be good enough to just point me in the right
direction...?

Thanks,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=507462

I use

dim lastRow as integer
Dim lastCol as integer


lastRow =
Application.WorksheetFunction.CountA(ActiveSheet.R ange("a:a")) '
counts all values in column A
lastCol =
Application.WorksheetFunction.CountA(ActiveSheet.R ange("1:1")) ' counts
all values in row 1

Won't work if there are gaps in your data, but I don't think any of the
other methods would either.



jaimetimbrell Wrote:
Hello,

I am new to macro's and I would be grateful for any advice:

I have a macro that looks at about 100 spreadsheets and performs
various calculations, below is an example:

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11)),"""")"

as you will see there is a constant reference to the last row (R6000) -
but I have added this manually as I know this is the last row of the
biggest spreadsheet - but there must be a way of calculating which the
last row is...but I am a bit dim and cant work out how to programme
it...

...I wonder if anyone else could help me with the code?

Thanks in advance.
Jaime.


--
chubster264
------------------------------------------------------------------------
chubster264's Profile: http://www.excelforum.com/member.php...o&userid=30164
View this thread: http://www.excelforum.com/showthread...hreadid=507462