Checking data integrity in excel spreadsheet using vba

[yoshe]

Hello,
I may not know exactly what words to search for, so I apologize if this question has already been addressed in other threads.

I want to run some preliminary checks on data that is sent to me in excel form, just checking columns for appropriate variable length and character/numeric format, any wierd symbols or spaces that shouldn't be in a field, etc. Possibly zip code validity. I simply want to highlight any offending cells, or output their rows to another spreadsheet.

The data arrives on a monthly basis and should be in a consistent format/layout, so I am thinking the quickest way to do this would be to set up a vba macro to check the columns, where the only thing I have to do is update the worksheet name.

Can anyone point me to a thread or other resource or sample code that would show a starting point for how to program this?
Thanks very much for any help.

 

[SkipVought]

Hi,

First establish exactly what validation procedures you want to impose on what columns. It may be plausable to construct a table of procedures and columns, so that, for instance if column A gets a DTE (date) validation and a LNT (length) validation and column B gets a LNT validation, you table would look like this...
[tt]
COL PROC
1 DTE
1 LGT
2 LGT
[/tt]
Then you can go about writing a function for each validation.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Presumably you want to do something like:
loop through each of the cells in each worksheet
compare the data content with the allowed type for data in that location (column number for example)
change the cell background colour if there is a mismatch

If so, you'll need to know which cells to loop through. Use worksheets(sheetname).usedrange.rows.count to find the number of rows and worksheets(sheetname).usedrange.columns.count to find the number of columns.

To determine the type of the data in a given cell, you might want to look at Typename and Vartype in the help files. There are also a bunch of "is" functions - e.g. isnumeric, isdate etc.

To address the cell (either to read or to write) at row 3, column 5 on the active worksheet, use the expression cells(3,5). Example:

To read the value in cell E3 into the variable fred:
fred=cells(3,5)

To change the background colour of cell E3 to yellow:

cells(3,5).interior.colorindex=6


I hope that helps.

Tony