Do until macro with variable

This is following on from a previous post.

I have the following formula to copy a range of cells where the row depends on the value of another cell (in this case CN2,3,4...) which itself is determined by a match function.

I am as far as this...

Please Login or Register  to view this content.

and need to keep doing this going down column CN until the cell is empty

In the case of the attached spreadsheet for CN2,CN3,CN4, stopping when empty CN5 is reached. It can always start on CN2

both the CN? values in the code should change after each iteration...
i.e. 2nd iteration should be

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi Marco,

thanks for the response.... the reason i need to write a macro rather than drag down code is because this will have to be done every day for a large amount of data where the match up of times will usually not be in such a regular 30 minute step.

How is your sampling data imported?
i.e in your original workbook the column containing the values 7166, 7196, and 7226

Do you append to this list daily, or delete the data each time?

it comes from a text file and another text type file.

it changes every day.

it is neccessary to run a match function to identify which row contains the matching time value, i.e. from which row to copy the cells of data.

the row on sheet 1 which corresponds to the first value varies as does the time interval, sometimes irregularly

it is a completely new workbook each time for each days data, hence the need for a macro

I don't understand why you are autofilling, but you don't need all of that code, this should do it for you

Please Login or Register  to view this content.

Hi Bob, I'm getting a runtime error 424 when i run this macro on workbook 1 (attached above). It occurs on this line

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

Any ideas?

Got me bud. I created it against your workbook and it worked fine here.

thats perculiar... any clue what a runtime error 424 refers to?
I use excel 2007 could there be any changes related to this

I altered last row to 4 to force past this error and it immediately gave me another runtime error 424 on the next line

" .Range("CN2").AutoFill .Range("CN2").Resize(LastRow - 1)"

am i meant to be adding anything into this code to apply it to my spreadsheet?

I ran it against 2007 too.

Error 424 is Object error, it is a generic error that could be one of many things.