Trying to autofill to end and getting run time error

[ugg]

I am trying to put a formula in to cell H9 and then copy that formula down to the last record (I don't want the formula to be in H1:H8) but with my code I am getting a run time error 1004 Method 'Range' of object '_global' failed.

Please Login or Register  to view this content.

can anyone tell me where I am going wrong?

[jindon]

Isn't the sheet protected?

[ugg]

its not protected because I can run other macros on this sheet.

My spreadsheet is set out as follows.

I have miscellaneous information in A1:H7

My data headings are in row 8

My data then goes from Row 9 downwards (not always to the same row)

I would like my formula to be written from H9 downwards based on how many rows have data (eg data goes to Row 200, formula is written H9:H200, the next day data goes to 430, formula is written H9:H430)

My formula is

Please Login or Register  to view this content.

[jindon]

OK
Then how are you getting "Last" ?

Please Login or Register  to view this content.

[ugg]

to be honest, I am really new to this and don't know what I am doing. I have been trying to get it to do it based on other posts that I have found but nothing is working for me.

I feel really silly because I think I am being really clever when I change the cell references to suit my needs and then it doesnt work.

The closest I have come is having it fill the cells H9:H1 but I dont want it to go up, I want it to go down. everything else gives me an error.

There is no emoticon with tears......

[mudraker]

It helps if when describing your problem you also specify which line of code is causing the error instead of us trying to gause where the error is occuring

I agree with ugg that it is possible becuse your variable of last has not been setwith a row number

[ugg]

Do you know how I can achieve the final result?

I would like my formula to be written from H9 downwards based on how many rows have data (eg data goes to Row 200, formula is written H9:H200, the next day data goes to 430, formula is written H9:H430)

[jindon]

Since you are looking at Col.B in your formula,
I guess...

Please Login or Register  to view this content.

[ugg]

Oh Jindon,

Thankyou, thankyou, thankyou.

You have no idea how long I have been trying to do this. Thanks