Check and update record if wrong

Sharid · May 1, 2018

On my workbook I have 2 sheet

Sheet1 “Customer Records”
Sheet2 “Treaty Discount”

The user fills in a web form with their details and state that they wish to apply for a discount under treaty X for their country and X percentage amount that treaty allows.

I need a way of checking if they have used the right treaty code and discount amount for their country, if Not it changes it to the correct amount and highlights the cells.

Also if the customer’s country is not in the treaty then a 30% is applied in column J Sheet1. See attached workbook for more info.

The Range is A to J in Sheet1 “Customer Records”
Column
H = Country
I = Treaty
J = %

The Range is A to C in Sheet2 “Treaty Discount”
Column
A = Country
B = Treaty
C = %

https://app.box.com/s/4z6bwy7st1kmupibjllm7y8lsex0eyy5

<tbody>
[TD="class: xl69"][/TD]

</tbody>

Roderick_E · May 1, 2018

How about on the customer record tab, instead of creating a new table under the existing, add columns K & L as "ACTUAL" and name I & J as "SUBMITTED"

In K3 this code and dragdown:

Code:

=INDEX('Treaty Discount'!B:B,MATCH($H3,'Treaty Discount'!$A:$A,0))

In L3 this code and dragdown:

Code:

=IFERROR(INDEX('Treaty Discount'!C:C,MATCH($H3,'Treaty Discount'!$A:$A,0)),30)

This way you can also track errors.

Sharid · May 2, 2018

[FONT=&quot]Sorry my fault. I should have explained that the second table was for your reference, so you could see what I was trying to get at, a Before and After

There will only be 1 table it will have 1 to 1000s of records, the data is submitted in an online form, so the amount will change daily. From the websites DB I will extract this data daily or weekly in excel format. I will then need to check that the customer has filled in the form correctly as they are applying for a TAX discount.

E.G. A customer in country X may fill the form incorrect in error or deliberately and if I apply the wrong TAX discount I will end up becoming liable and have to pay the tax difference.

Therefore I need a way to check that the data is correct and if not correct it and highlight it. I can then input the right discount amount and as the changes are highlighted I will allow me to ID which customer filled the form in wrong and notify them of the error. It not hard if its’ a record or two, but if there are 1000 records a week then ID the customer manually will be a major issue.

The code that you gave, will that work on a command button?[/FONT]

Sharid · May 2, 2018

Still stuck on this one, could someone please help.

Thanks

jmacleary · May 2, 2018

Is it possible to put the validation in the web page the user fills in so it only allows valid entries? Better to trap it before it arrives I think.

Sharid · May 2, 2018

I already have a validation to ensure the the required boxes are filled before the form can be submitted.

The data I need comes from 3 lists, problem is the customer might select the wrong data and submit the form.

There are 66 Countries to choose from 14 U.S Treaties and 4 %

I don't think I can validated them all without PHP code, which I can't do, hence excel

Roderick_E · May 2, 2018

I'm not certain why my solution isn't able to fix the issue. But I agree, it should be validated at the website so the customer can't pick the wrong data to begin with.

MickG · May 2, 2018

Try this:-

Code:

[COLOR=navy]Sub[/COLOR] MG02May54
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
    [COLOR=navy]With[/COLOR] Sheets("treaty Discount")
[COLOR=navy]        Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]    End[/COLOR] With
[COLOR=navy]
With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]
For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng: [COLOR=navy]Set[/COLOR] .Item(Dn.Value) = Dn: [COLOR=navy]Next[/COLOR]
[COLOR=navy]
With[/COLOR] Sheets("Customer Record")
[COLOR=navy]Set[/COLOR] Rng = .Range("H3", .Range("H" & Rows.Count).End(xlUp))
Rng.Resize(, 3).Interior.Color = xlNone
[COLOR=navy]End[/COLOR] With

[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] .exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Not .Item(Dn.Value).Offset(, 1).Value = Dn.Offset(, 1).Value Or Not _
                .Item(Dn.Value).Offset(, 2).Value = Dn.Offset(, 2).Value [COLOR=navy]Then[/COLOR]
                Dn.Offset(, 1).Value = .Item(Dn.Value).Offset(, 1).Value
                Dn.Offset(, 2).Value = .Item(Dn.Value).Offset(, 2).Value
                Dn.Offset(, 1).Resize(, 2).Interior.ColorIndex = 40
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Else[/COLOR]
        Dn.Resize(, 3).Interior.ColorIndex = 6
        Dn.Offset(, 2).Value = 30
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Sharid · May 2, 2018

Mick G

Your Code is Super, once again you have saved my life

MickG · May 3, 2018

You're welcome

Check and update record if wrong

Sharid

Well-known Member

Excel Facts

Roderick_E

Well-known Member

Sharid

Well-known Member

Sharid

Well-known Member

jmacleary

Well-known Member

Sharid

Well-known Member

Roderick_E

Well-known Member

MickG

MrExcel MVP

Sharid

Well-known Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Check and update record if wrong

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock