Looks at 2 fields and If one doesnt contain certain characters then set to 0.

I have the following in Excel

Row I K
6 Job_Family_desc NewGrade
7 TAL – Test1 8
8 TAL – Test1 8
9 TAL – Test1 8

For TAL you can only have a grade of 5 to 9
If someone enters 1 against NewGrade, that’s incorrect.

At the end of the process the uses clicks a macro button and then
Go to K7.
If I7 LIKE “TAL*” AND K7 IN (0,5,6,7,8,9) THEN Next
ELSE SET K7 = 0
IF I7 LIKE “BAL*” AND K7 IN (0,1,2,3,4,5,6,7,8,9) THEN Next
ELSE SET K7 = 0
IF I7 LIKE “SAL*” AND K7 IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14) THEN Next
ELSE SET K7 = 0
NEXT got to K8

Any help would be greatly appreciated

Debbie

Ive tried to add an example in a spreadsheet

I cant explain it any better than I have.

I cant provide the result.

The example data is in columns I and K as specified.

The basics are. How do I write

Go to K7.
If I7 LIKE “TAL*” AND K7 IN (0,5,6,7,8,9) THEN Next
ELSE SET K7 = 0
IF I7 LIKE “BAL*” AND K7 IN (0,1,2,3,4,5,6,7,8,9) THEN Next
ELSE SET K7 = 0
IF I7 LIKE “SAL*” AND K7 IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14) THEN Next
ELSE SET K7 = 0
NEXT got to K8

This was my original plan and one Ive been working on (Based on rel data and not the example)

Please Login or Register  to view this content.

The bit in the middle isn the bit that doest work

'And now we have to do the last checks to make sure that the New Grade is correct for the Job Family Desc


If i is Like BPS and K is NOT in 1,2,3,4,5,6,7,8,9,10,11,12,13,14 then

Message box 'BPS is incorrect' and end the IF statement
If i is Like CAT and K is NOT in 1,2,3,4,5,6,7,8,9,10 then
Message box 'CAT is incorrect' and end the statement

Repeat through all the logic

go through all the rows until there is no more data.

Please Login or Register  to view this content.

Looking good!!

Ill see if I can do something with that

Thank you

Ive not been able to implement it. I dont understand the first IF NOT because Im wanting

IF its TAL but not 0,5,6,7,8,9 then SET to 0

Ive tried doing the following

Please Login or Register  to view this content.

But Im getting an error

End If without block IF

Im not used to VBA cod I work in SQL Mainly so Im really struggling with this.

yeah you are right, you have end if written twice, you need:

Please Login or Register  to view this content.

I'm not sure if that will work though, I don't think it will interpret the and and or's as you want it to. To be on the safe side I would put the two statements on different lines:

Please Login or Register  to view this content.

Ive got this together

Please Login or Register  to view this content.

but it doesnt actually work properly.

I have 3 rows all correct

TAL 8
TAL 8
TAL 8

It looks at the firt bit of the code the BPS and sets 8 to 0. Thn it starts again and does the sae for all 3 values.

I was expecting it to eep going until it found the TAL row, then finding it was correct to go onto the next row.

Iv done these kind of loops before so I dont quite kno whats gone wrong

I don't think you can use the and/or commands in an if statement like that. You need two separate statements, one for the TAL part and one for the numbers:

Please Login or Register  to view this content.

Note - I haven't been through all the code, I don't think you posted it all so it doesn't compile as it is in your post. This would be the first thing to try but there might be other problems..

Unfortunately is doesnt seem to like that, but the only line in red is the second like. The If NOT after the IF. everything else seems happy

Please Login or Register  to view this content.

I can see a couple of issues with the code posted:

-The line in red is missing "or not" between "Range("K" & i) = 0" and "Range("K" & i) = 1".
-Your end if statements do not match up to the if statements correctly, your code should have this format:

Please Login or Register  to view this content.

Whops you were right about the or not bit. Ive corrected that. I cant quite see the other issue your taking about based on the small example compared to the one Ive got

Ive currently got an error at the end on Next i (Next without for is the error)

Is it possible to show where I have gone wrong based on the example below? Im basically, in a field and checking one field against the other for each group. When Ive either set it to 0 or found no problems I continue to the next field until I have no rows left

Please Login or Register  to view this content.

you need to change the if statements in your code to the format I posted. You don't have the same number of "end ifs" as you do "ifs"

Please Login or Register  to view this content.

Ah ha, so I needed to end if all the new If Nots! Sorted. So its working again but still not doing what I want. It does get further than last time though. Last time it would only go to the first IF eery time)

My column i are all TAL. and My column k are all 8 (3 rows)

It continues till it gets to TAL, but then it changes the field to 0 even though 8 is valid. So it still looks like its resetting everything back to 0 even what the grades are valid. Hmmmmm. So close through

Please Login or Register  to view this content.

How about this?

Please Login or Register  to view this content.

That it!!!

It now working. Brilliant. Now I need to figure out the best place to put it. Ive got quite bit of logic to check issues in the script already. Its whether to do it on the event of column i changing, or whether to do it right at the end when the user is finalising the spreadsheet.

Ill hve a think about that. Thak you again!!!!