KX13ZN
New Member
- Joined
- Sep 12, 2019
- Messages
- 34
- Office Version
- 365
- 2019
- 2010
- Platform
- Windows
- Mobile
- Web
I have had a look around for a way to check that the data stored in a range is only numbers or is blank. However nothing matches what I need...at least in VBA, which is what I need.
The range can be anywhere between A1 to D65000. The data will always end up in the first 4 columns.
Theoretically, in column A there should only be whole numbers between 1 and 999,999,999 (there whole range isn't present but values can go that high) and blank cells.
However if something has gone wrong in a previous process then the cells may contain dates or letters or both as well as numbers.
I've tried using data validation within excel but that doesn't check existing data if applied after the fact and because the data moves around on the sheet setting it before hand or setting cell formatting before isn't practical.
And IsNumber or IsNumeric require the cells to be formatted to work properly...as far as I can tell.
So unless excel has auto formatted a cell to be something specific, then all the cells are formatted as general.
So I need to way to check that just the numbers 0,1,2,3,4,5,6,7,8,9 are present and that its not a date.
I then need to check if all cells in Column B contain letters, but these are names so they contain spaces and accented letters, just not numbers, period or commas
I also need to check that Column C only contains capital Letters, so A-Z only, or be blank
Has to be case sensitive.
And then lastly Column D should only contain dates or be blank
If any errors are found then it just needs to output a msgbox and then run some other code because it means a previous process has gone wrong and things need to start again.
I've never worked directly with data checking so I'm completely new to this.
Help would be great but I also want to learn how to do this in the future as i'm sure i'll need to check otherthing later on.
The range can be anywhere between A1 to D65000. The data will always end up in the first 4 columns.
Theoretically, in column A there should only be whole numbers between 1 and 999,999,999 (there whole range isn't present but values can go that high) and blank cells.
However if something has gone wrong in a previous process then the cells may contain dates or letters or both as well as numbers.
I've tried using data validation within excel but that doesn't check existing data if applied after the fact and because the data moves around on the sheet setting it before hand or setting cell formatting before isn't practical.
And IsNumber or IsNumeric require the cells to be formatted to work properly...as far as I can tell.
So unless excel has auto formatted a cell to be something specific, then all the cells are formatted as general.
So I need to way to check that just the numbers 0,1,2,3,4,5,6,7,8,9 are present and that its not a date.
I then need to check if all cells in Column B contain letters, but these are names so they contain spaces and accented letters, just not numbers, period or commas
I also need to check that Column C only contains capital Letters, so A-Z only, or be blank
Has to be case sensitive.
And then lastly Column D should only contain dates or be blank
1 | name one | AAAA | 22/01/2020 |
22 | name two | BBBB | 21/01/2020 |
333 | name threé | CCCC | 20/01/2020 |
4444 | name four | DDDD | 19/01/2020 |
55555 | name fivé | EEEE | 18/01/2020 |
666666 | name six | FFFF | 17/01/2020 |
7777777 | name seven | GGGG | 16/01/2020 |
88888888 | name eight | HHHH | 15/01/2020 |
999999999 | name nine | IIIII | 14/01/2020 |
If any errors are found then it just needs to output a msgbox and then run some other code because it means a previous process has gone wrong and things need to start again.
I've never worked directly with data checking so I'm completely new to this.
Help would be great but I also want to learn how to do this in the future as i'm sure i'll need to check otherthing later on.