Seemingly erratic behavior when auto-filling cells

**excelforum123** · 10-21-2010, 12:40 PM

I couldn't think of a more suitable name for the problem.

In the workbook, there are two sheets. One is the source sheet where certain values are parsed and compiled into a table in the first sheet. The code is supposed to write a single dot ( "." ) to every empty cell between every value in the first sheet (the one with the table for values to be filled in from the other sheet). The problem is that some particular rows remain empty. I can not figure out why.

A simplified excel file with the working example is attached and below is the code:

Please Login or Register  to view this content.

In the excel file, press the button with ">>" text in "A3" to see it work.

The rows that remain empty are 4, 8, 10, 18, 22, 24, 28, 35, 44.

Any help is much appreciated.

**TMS** · 10-21-2010, 01:45 PM

Those appear to be rows that you are not specifically picking up in the code.

One of the headings is "Reliability" and there dowsn't appear to be any meta data for that heading. Could that be the case for the other rows?

Regards

**excelforum123** · 10-21-2010, 03:53 PM

Thank you for looking at it. At the end of the first "If" block that goes through the defined Rows by the variable "dRow", the last condition counts for all the rows not defined:

Please Login or Register  to view this content.

Which is why all the other similar cells are properly written with "." but somehow the aforementioned rows are forgone . The names in the row headers are irrelevant, since parsing method is not dynamic, ie. it doesn't read values from row headers to find in the 2nd sheet and to take the corresponding value from there and back to the first sheet. References are predetermined as can be seen in the code and only works by cell/range references for the first sheet.

Also of note is that the rows that remain blank are immediately next to one with to a row that has values written from the second sheet. It feels like it's so obvious and yet I can not follow the process logic to arrive at the cause.

Also, I'm open to suggestions of a more relevant thread name. I fear this one is prone to go unnoticed/uncared.

**TMS** · 10-21-2010, 05:07 PM

I think your logic may be flawed

Please Login or Register  to view this content.

So, when your destination row (dRow) variable has a value of 4, you put a dot in row 5.

Regards

**TMS** · 10-21-2010, 05:18 PM

Yep, that's definitely it. I've just stepped through a few more iterations and, for the rows not specifically checked, you put the dot in the next row. It is then overwritten when the specific row is processed.

I note that, for the rows specifically checked, you use dRow - 1

Regards

**excelforum123** · 10-22-2010, 03:35 AM

Thank you, I'll check it back when I go back home.

I use dRow - 1 to make it easier to manage the code, ie. I want dRow value to match the row that will actually be modified since I use Range("A1").Offset where the modified cell will always be in the next row from the the dRow value itself.

edit: Oh, dRow - 1 ! I'm such a dumb! I haven't even noticed that I wrote it without the "- 1" in the else condition. This is so embarassing. Thank you for your attention.