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.
<tbody>
</tbody>
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.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
1 | 1 | 1 | AC | LJ | 1503 | 0 | LC | Jrnl Description | 100000 | 100000 | Y | F | L | |
2 | 2 | 1 | 10002 | 72400 | UKGB | HDOF | 25000 | |||||||
3 | 2 | 1 | 10002 | 78030 | UKGB | HDOF | 50000 | 11035 | ||||||
4 | 2 | 1 | 10002 | 78030 | USAM | HDOF | 25000 | 14080 | ||||||
5 | 2 | 1 | 10002 | 17100 | 25000 | |||||||||
6 | 2 | 1 | 10002 | 28200 | 25000 | 11035 | ||||||||
7 | 2 | 1 | 10002 | 28200 | 50000 | 14080 | ||||||||
8 | 1 | 1 | AC | LJ | 1504 | 0 | LC | Jrnl Description | 150000 | 150000 | Y | F | L | |
9 | 2 | 1 | 10002 | 72400 | UKGB | HDOF | 50000 | |||||||
10 | 2 | 1 | 10002 | 78030 | USAM | FTOF | 25000 | 11035 | ||||||
11 | 2 | 1 | 10002 | 78030 | EUEU | FTOF | 25000 | 14080 | ||||||
12 | 2 | 1 | 10002 | 17100 | 100000 | |||||||||
13 | 2 | 1 | 10002 | 28200 | 50000 | 11035 | ||||||||
14 | 2 | 1 | 10002 | 28200 | 50000 | 14080 |
<tbody>
</tbody>