Fill data series or resize range not working on large blank ranges

[Gareth Allan]

Morning

I have an inherited macro, that occasionally does not behave as intended, when trying to fill a data series between two column values - sometimes when there is a large number of blank cells/rows between the two points, it doesn't fill correctly.

So it has a user entered data fields on the first 4 columns - the fourth column is a marker used to indicate certain triggers IRL and used as targets in this macro

so in this example, it has an 's' marker that the macro looks for to determine the start and end of the data series fill - note that there may be several different markers throughout the data series, each 'pair' it calculates a new series for, and fills between. it also uses the first and last cells by default.

So I have stripped some other stuff out of this to redact it slightly, but the example shows a failure state, when running the 'get values' macro button:
1) it is able to fill a data series in column L between the 's' tags at row 2 and 21
2) it fails to enter a data series between the next set, between row 21 and row 18116

I can manually create a FILL / SERIES / COLUMN / LINEAR / TREND between these samples

You can also step through the 'get_overwrite_Original' macro, and see that it is calculating the correct step value (I think)

So, the macro works when there is a small number of cells to range over, but fails if this is too large - I have been unable to find the information about limiting the resize range command it seems to use - any ideas?

Example excel, with sample data and macro attached - click clear calculated field, then get values to see in action

Gareth

[sintek]

Hi Gareth

Welcome to the Forum...

Try this ...Output col L

Please Login or Register  to view this content.

[Gareth Allan]

Afternoon

Unfortunately the code did not work for me, in my situation

However, a bit more digging found a different way to do it:

Please Login or Register  to view this content.

in this example, my last line is LR in initial macro,or could be entered as a manual value.

the above macro used a CTRL + Down arrow type 'search' to jump between the values i want to fill between. I therefore had to change the format of my 'fill' column after a paste, and while its not the most elegant solution, I used a record macros, text to columns code to do this:

Please Login or Register  to view this content.

always fun how there are so many different ways to do things - anyways thanks for the help

Gareth

[sintek]

Unfortunately the code did not work for me, in my situation

Strange...Worked perfectly with sample upload...Gave exact results...Am guessing your actual file is different...

[Gareth Allan]

well the example I was using was heavily redacted...

it had more triggers than just the 's', and needed the first and last values highlighted as well.

as usual when I deal with programmers, it makes perfect sense in my head when I explain it the first time, but I always need to add stuff i missed out

Away to dig out my other partially broken files now

Gareth