PDA

View Full Version : Macro to Check For a Cell Value and Perform Null & Data Type Check for multiple Cols



chakkrav
04-09-2017, 12:53 PM
Hi,

I have a sheet which has :
1) Category "Null Check" which will have values either Y or N in Row 1 from Column B Onwards...
2) Category "Data types" which will have values like varchar2(6) etc... in Row2 from Column B Onwards...
3) Row 3 is Attribute Names.
4) Row 4 i.e. from B4 onwards there is Data.

The requirement is to :

1) Check if B1 has value N & then perform Null check for the B column data range & if a cell has Null value, highlight the cell & throw out an Error Message box saying null values exist.
2) Check for B2 value varchar2 specification (In the above example it is 6) & then perform data type validation check for B Column whether it has Varchar length 6 & if No, highlight the cell not meeting the
Varchar criteria & throw out an Error Message box saying Data type mismatch exist.

Since I'm not an VBA expert, The Pseudo Code I think should be:

'1 use current region to get the table with data
'2 go through the cells by row and then by column – To use FOR loop
'3 use the first column with Y and N marks to check the cells in the same column if they are subject of your checks
'4 store the cell address in some collection or array
'5 moving from cell to cell check against your second row if varchars are not exceeded/the length issue
'and store them in a separate collection/array
'6 mark the cells that need correction (with 2 different colors for example) – also in the loop
'7 give some meaningful information even quantitate info to the user with msgbox

This is obviously my pseudo code – not VBA. Turning that into code is where I need someone's expertize help & assistance to sort out the issue.
I would greatly appreciate helping hand :)

Attached is the sample sheet for your reference.