Refering to a cell reference based on a variable

[bd528]

Hi,

I have defined Numrows as an integer.

I want to use the following code :-

Please Login or Register  to view this content.

But rather than M410, I want the code to copy down to "numrows"

How can I code this?

Thanks in advance

[JeanRage]

Hi,

You can try

Selection.AutoFill Destination:=Range("M6:M"&Numrows)

HTH

[bd528]

Hi,

You can try


HTH

When I tried that I got "method 'range' of object '_global' failed"

Any other ideas?

Thanks

[DonkeyOte]

It's not clear what you're "filling" - formula/value etc... it's generally quicker to apply to a contiguous range using the below approach as opposed to AutoFill.

Please Login or Register  to view this content.

If we assume numrows is 10 then M6:M10 will assume the formula of the first cell in the selection.

If numrows is meant to represent the size of the vector rather than the literal end row number (implied by naming convention - ie number of rows) then you would use Resize

Please Login or Register  to view this content.

this would apply the formula to M6:M15 (10x1)

If you're still having problems post a sample.

(we assume the sheet is unprotected etc...)

[bd528]

I have attached an example.

I want to copy the forumula down to the last row (based on the number of entries in column A)

I have used a seperate piece of code (not in the example) to count the number of entries in column A, which creates the integer "numrows". But if you know a better solution to counting them, please let me know.

Thanks.

[DonkeyOte]

Sorry, just looked at this...

Please Login or Register  to view this content.

[bd528]

Thanks for that - its nearly perfect.

Please see my ammended attachment. I'd like L5 to remain blank if possible, after the macro is complete. I realise I could just use another piece of code to remove the current contents on the cell, but I assume you will know how to adjust your example.

Thanks again.

[DonkeyOte]

Change the reference from 5 to 6

[bd528]

If I do that, I get #VALUE! in M6

[DonkeyOte]

I should perhaps have been more explicit

Please Login or Register  to view this content.

[bd528]

I should perhaps have been more explicit

Please Login or Register  to view this content.

No, not at all - I should have seen my mistake! Thank you thats great.

As for my original question, (just so I know for future reference, and understand the syntax) in the code below, how can I refer to an integer variable row number, rather than 410?

Please Login or Register  to view this content.

[DonkeyOte]

You would use something along the lines of:

Please Login or Register  to view this content.

Note: numrows should be declared as Long rather than Integer
Long is advised when working with Row values given Row numbers can exceed Integer boundaries thereby generating overflow errors

It is also assumed that numrows holds the last row number of interest rather than the count of rows to be included - if it is the latter then use a Resize based approach.

[bd528]

Thanks again. Exactly the information I needed.