If cell in range is blank; enter 99999

[shmee150]

Ok so it turned out I wasn't on my last question, but hopefully this is.

In the attached sheet, there is a range with lots of entered data (a small section of the total) where I would like to enter into all of the blank cells the value 99999.

I have been selecting the range as follows:

Please Login or Register  to view this content.

Which is probably not the best, but it works, but need to fill in those gaps?

Many thanks,

Tim

[DonkeyOte]

Tim, dare I ask why you want to do this ?
(this seems to go back a few questions...)

You can achieve this using:

Please Login or Register  to view this content.

[shmee150]

Tim, dare I ask why you want to do this ?
(this seems to go back a few questions...)

You can achieve this using:

Please Login or Register  to view this content.

Yes err good question, I'm not really sure. I know if I get this line working though then the output will be exactly what I am hoping to achieve although though it is a completely awful; from a programming point of view, method to use to solve my problem.

Your code is perfect for the sample spreadsheet, I'm not sure why I can' get it to not throw up an error on mine, am working on it.

Thanks,

Tim

[DonkeyOte]

Does the real life matrix contain Blanks or Nulls ? The latter is likely if the table is populated via Formulae...

If you find what you believe to be a blank cell test with:

=COUNTBLANK(xy) where xy is cell reference of expected blank

If answer is 0 then it's a Null ("") .. if 1 it's a blank and I would not expect the code to bug.

You can handle Nulls easily enough if the values are result of formulae using an approach such as:

Please Login or Register  to view this content.

A Null is a Text value (non-numeric) so can be identified relatively easily if the other results are all numeric.

[shmee150]

They were blanks. But fortunately the entire spreadsheet is now completed and it has been accepted by the analyst for whom it will be used for comparing data drawn from lots of sources but changed into the same format now.

I know I didn't do it necessarily the best way, but this was my first VBA project so I'm pretty pleased although the entire thing is like 500 lines which is not so tidy haha!

Many thanks again for all of the help, it has been truly appreciated.

Tim