VBA to Validate Excel Data and Create .CSV File

[mok74]

Hi,

I am new to this forum and very new to VBA. I would be really great full if anyone could help on this please.

I have a journal data file that I want to validate using VBA and once the validations have been checked, to create a .csv file from the data file to post as a journal into an application.

The data has been shown below in the table as I didn't know how to attach my file as couldn't find the attach button.

VBA Required:
1. What I am after is if Column A is 1 then do a check that for the same row which is 1 that Column J - Column I = 0. So what I am trying to do here is if A1=1 and I1-J1=0 then give me a message stating 'all is good', otherwise give me a message stating 'all is bad', but when I1-J1 does not =0, I want to write the cell addresses of all bad rows into another spreadsheet in the same file. I want this to work for all columns and rows which have column A as 1.

2. VBA to check that if Column A is 1 then Column M can only be either T or F, if column A is 1 then Column N can only be L or G, if column A is 1 then column G can only be LC

3. VBA to check that if column A is 2, then check to see that data in columns H and I is to 0 decimal places, if not to convert all data in columns H and I to 0 decimal places where column A is 2

4. VBA to alert the user when column A is 2 and column D is 17100 and a popup message stating 'Please provide further information'

5. Once all the validations have been met, a popup message stating 'All data has been validated - create .CSV file?' Yes/No button if user clicks yes it gives the user a screen where they can save the file as .csv format, if user clicks no, then nothing happens and user returns to the file.

I would really be greatful if the VBA can be anotated, so I can learn how things are actually happening.

Any assistance will be greatly appreciated.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]AC[/TD]
[TD]LJ[/TD]
[TD]1503[/TD]
[TD]0[/TD]
[TD]LC[/TD]
[TD]Jrnl Description[/TD]
[TD]100000[/TD]
[TD]100000[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]F[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]72400[/TD]
[TD]UKGB[/TD]
[TD]HDOF[/TD]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]78030[/TD]
[TD]UKGB[/TD]
[TD]HDOF[/TD]
[TD][/TD]
[TD]50000[/TD]
[TD][/TD]
[TD]11035[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]78030[/TD]
[TD]USAM[/TD]
[TD]HDOF[/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]14080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]17100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]28200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD]11035[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]28200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50000[/TD]
[TD]14080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]AC[/TD]
[TD]LJ[/TD]
[TD]1504[/TD]
[TD]0[/TD]
[TD]LC[/TD]
[TD]Jrnl Description[/TD]
[TD]150000[/TD]
[TD]150000[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]F[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]72400[/TD]
[TD]UKGB[/TD]
[TD]HDOF[/TD]
[TD][/TD]
[TD][/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]78030[/TD]
[TD]USAM[/TD]
[TD]FTOF[/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]11035[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]78030[/TD]
[TD]EUEU[/TD]
[TD]FTOF[/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]14080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]17100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]28200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50000[/TD]
[TD]11035[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10002[/TD]
[TD]28200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50000[/TD]
[TD]14080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[brucef2112]

98% of this can be done just using conditional formating on the respective cells or creating 'Rules' columns containing the rules.

Number 1. Done with conditional formatting and using Auto Filter.
With Auto Filter you can pick the errors you want to see. Do stuff and then filter for more stuff, while all on the same sheet. No need to flip back and forth.
For Example the test data shows "D" rule is violated and shows "Need more info" From the column filter you could select it from the dropdown box and the rows would be filtered to those which need more info.

Number 2. Done with conditional formating.
Number 3. QUESTION: Do you mean there could be decimal values and you want them stripped once col:A equals 2. Or do you mean this column should be either text or whole number.
Number 4. Done with conditional formating.

Number 5. Creating a user interface specific for this is more trouble than needed. Excels File Save As *.CSV works great. At some point you have to trust the user knows or has been told what they need to do to save the data as.

Go to the link for a sample of all this. Its not polished for actual use. it is just demonstrating your rules using conditional formating and the benefit of using Auto Filter to limit the scope of corrections that need to be made or pinpoint data to be studied.
Click Here to get Sample Spreadsheet.
You will see, as data is changed which violates the rules the fill colors change to bring attention to them. Just for testing there are added columns to the right with similar messaging about broken rules. Break some rules and then try the Auto Filter feature on these columns at the end. You'll see that not having to switch between sheets (like you requested in #1) to analyze data is pretty nice.

Auto Filter and Conditional Formating are your friends
Writing validation formula, Mehhh. But very useful.

 

[mok74]

Hi brucef2112,

Firstly please accept my apologies on not replying back to you earlier. I really appreciate you taking the time out to look at my issues and I really like the fact that I can do all this without using VBA. Have tested your conditional formatting and rules on cells and they work really well and is very user-friendly. Thank you so much. My issue for #3 is if column A = 2 then the data in column H and I is rounded to 0 decimal places.

once again really appreciate your help.