UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Excel Vba To Check If Cell Value Correct (and In A Range), Office 2010    
 
   
Berty
post Apr 15 2019, 12:45 PM
Post#1



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi Folks

Firstly - many thanks to Debaser who helped me with my first iteration of my problem - as often happens - things have now changed and I feel like I'm back to square 1

I have a file that will have lots of data entered into it (I've attached an example file so you can see exactly what I'm trying to achieve with a VBA procedure) tab name Test Data

What I need to do is the following ...

Depending on the country found in Column C, the values that have been entered for several fields (there are in fact over 80 of them) need to be exactly as found in the ValidationData tab.

If there is a difference then the cell with the data in it should turn red

Now, there is an added complication (well it's complicated for me). For a couple of data fields we need to check that they have entered a valid entry - these will be found in the ValidationData tab B10:B18 for the Reason field and B19:B24 for the Vehicle field. (entry goes red if it doesn't match the ValidationData value. Now, depending on what they entered, we need to check that their charge rate matches that found in the ValidationData cell. E.G Germany have entered Crash in their reason cell (spelt correctly - i.e. it is found in range B10:B1 on the ValidationData tab). In the Reason Charge field - we now need to make sure that they are claiming the right amount (in this case 36.00 would be correct, anything else - the cell turns red)

I hope this makes sense - and I'm very, very grateful if you could help me with my VBA code


John


Attached File(s)
Attached File  Data_Validation_File.zip ( 17.46K )Number of downloads: 5
 
Go to the top of the page
 
ADezii
post Apr 17 2019, 07:24 AM
Post#2



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Just subscribing for now, need to wrap my head around this one! iconfused.gif
Go to the top of the page
 
dflak
post Apr 18 2019, 11:55 AM
Post#3


Utter Access VIP
Posts: 6,283
Joined: 22-June 04
From: North Carolina


This is not quite what you asked for, but maybe an improvement. Instead of validating bad charges, this spreadsheet looks up the good values and fills them in for you.

Also, you have Excel 2010, which means you have tables, so I made use of them. http://www.UtterAccess.com/wiki/Tables_in_Excel.

The major overhaul is with the validation tables. The data should be entered in a normalized format. You can see what I did on this sheet. You could enter the data as you had, but then there would be another step to convert the data to the normalized format.

The green table is used in each of the orange tables to validate country name. It is also used on the Test Data Tab.

The blue tables are used to validate data in the first column of each of the orange tables. This assures consistent spelling.

After the orange tables comes a series of pivot tables, these look up the values associated with the selected country. There is a piece of VB code on the test data sheet that sets the filters for these pivot tables when a country is selected in the main table.

Each of these pivot tables are overlaid with two named dynamic ranges: http://www.UtterAccess.com/wiki/Offset_and_Dynamic_Ranges. One range covers the first column for the purposes of data validation. The second range encompasses both columns and is used to lookup the value.

So, when you enter a date, a new line is added to the table with all the data validation in place.

The month is calculated automatically from the date. You have a drop-down for the country name. Selecting a country name automatically populates Collection, Air Ticket, Hotel and Other.

Selecting a reason automatically populates the cell to the right.

Selecting a vehicle automatically populates the cell to the right.


Attached File(s)
Attached File  Data_Validation_File.zip ( 38.98K )Number of downloads: 2
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
ADezii
post Apr 18 2019, 01:54 PM
Post#4



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


I initially started out taking a brute force approach using Explicit Ranges and a lot of Code. Soon, I realized that this approach would be very difficult to maintain as well as needing constant updating as soon as a new Country, Reason, etc. was added. I quickly abandoned this idea but I'll Upload what I ended with. Simply click on the Validate Test Data Command Button and the RESET Background to see how it all works.

P.S. - My initial idea was to perform the Validation in either the SelectionChange() or Change() Event of the Worksheet in order to catch an Invalid Entry once it's entered and possibly correct it.
This post has been edited by ADezii: Apr 18 2019, 02:08 PM
Attached File(s)
Attached File  Data_Validation_File.zip ( 35.09K )Number of downloads: 1
 
Go to the top of the page
 
Berty
post Apr 19 2019, 05:47 AM
Post#5



Posts: 669
Joined: 10-October 04
From: Gloucester UK


HI dflak

Very many thanks for your hard work here - and it makes perfect sense - I'll definitely make use of this

However, there's always a however isn't there! I don't have any control over the ValidationTable - its a real pain - but that's what happens when you work between several departments

Many thanks again though


John
Go to the top of the page
 
Berty
post Apr 19 2019, 05:53 AM
Post#6



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

Hey! what a star you are - I've had a quick look and I'm going to follow your VBA into my real file

But first look and try-out is amazing

Thank you so much

In your debt


John
Go to the top of the page
 
Berty
post Apr 19 2019, 06:18 AM
Post#7



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

So, now tested in my working environment and what you've created works perfectly.

Now, the very last bit (if I can plead for this last piece of code)

If the Reason finds a match - can we then do a check to see if the value in Col H matches the ValidationTable entry for that reason and country?

I realise as I type this - that this must be very complicated as I cant think where I would start frown.gif


Many thanks in Advance - just in case you can help me


John


CODE
'Let's Validate the Reason Column
intRow = 4      'RESET Row Counter
Do While Cells(intRow, "G") <> ""
  var = Application.Match(Cells(intRow, "G"), Worksheets("ValidationTable").Range("B10:B18"), 0)
    If IsError(var) Then        'Not found
      Cells(intRow, "G").Interior.Color = vbRed
    End If
  intRow = intRow + 1
Loop
Go to the top of the page
 
ADezii
post Apr 19 2019, 07:20 AM
Post#8



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I realise as I type this - that this must be very complicated as I cant think where I would start

Give me a little time and I'll see what I can come up with.
Go to the top of the page
 
Berty
post Apr 19 2019, 08:25 AM
Post#9



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Thanks so very much

John
Go to the top of the page
 
ADezii
post Apr 19 2019, 08:29 AM
Post#10



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I realise as I type this - that this must be very complicated as I cant think where I would start

Give me a little time and I'll see what I can come up with. Times up!
  1. If a Reason is Valid, then the Code much check and see if the associated Reason Charge is acceptable for the specific Country AND Reason, not an easy task. What I did was to pass the Country and Reason to a Public Function which determines what the exact Charge should be given the Country/Reason criteria. The Function now returns this Value as a SINGLE. If, for any reason, the appropriate Reason Charge cannot be determined, the Function returns 0.
  2. The Return Value is tested against the actual Value for Reason Charge in the Test Data Worksheet. If it is Valid then nothing happens but if it is no the Cell's Background for the Charge is turned to Red.
  3. All of this starts in the Else Clause of the If...Else...End If Construct seen below.
    CODE
    Do While Cells(intRow, "G") <> ""
      var = Application.Match(Cells(intRow, "G"), Worksheets("ValidationTable").Range("B10:B18"), 0)
        If IsError(var) Then        'Not found
          Cells(intRow, "G").Interior.Color = vbRed
        Else    'Reason is Valid, but is the Reason Charge for the Country?
          strCountry = Cells(intRow, "C"): strReason = Cells(intRow, "G")
          sngCharge = Cells(intRow, "H")
            If fRetValidCharge(strCountry, strReason) <> sngCharge Then
              Cells(intRow, "H").Interior.Color = vbRed
            End If
        End If
      intRow = intRow + 1
    Loop
  4. Function Definition:
    CODE
    Public Function fRetValidCharge(strTheCountry As String, strTheReason As String) As Single
    On Error GoTo Err_fRetValidCharge
    Dim ws As Excel.Worksheet
    Dim rngData As Excel.Range
    Dim rngReason As Excel.Range
    Dim rngCountry As Excel.Range
    Dim varRet As Variant

    Set ws = ActiveWorkbook.Worksheets("ValidationTable")

    With ws
      Set rngData = .Range("C6:E18")
      Set rngReason = .Range("B6:B18")
      Set rngCountry = .Range("C5:E5")
    End With

    With Application.WorksheetFunction
      varRet = .Index(rngData, .Match(strTheReason, rngReason, 0), .Match(strTheCountry, rngCountry, 0))
        fRetValidCharge = varRet
    End With

    Exit_fRetValidCharge:
      Exit Function

    Err_fRetValidCharge:
      fRetValidCharge = 0
      MsgBox Err.Description, vbExclamation, "Error in fRetValidCharge()"
        Resume Exit_fRetValidCharge
    End Function
  5. I have Attached the Revised Demo for your review.

P.S. - Again, I do not mean to criticism my own Code but it will be very difficult to maintain unless the Data is Static. As an example, another Function would have to be created or the current one expanded for Vehicle and Vehicle Charge.
This post has been edited by ADezii: Apr 19 2019, 08:32 AM
Attached File(s)
Attached File  Data_Validation_File_Revised.zip ( 39.08K )Number of downloads: 3
 
Go to the top of the page
 
Berty
post Apr 19 2019, 11:16 AM
Post#11



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

Wow! - many thanks again

I'll put it into my file and see what happens - I'm sure it will be super


I'm so grateful to you

Best wishes

John
Go to the top of the page
 
Berty
post Apr 19 2019, 12:22 PM
Post#12



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

Well .... The test file - works brilliantly and does exactly what its meant to.

Now, I've applied the cope (with what I thought were appropriate changes to names and ranges references) and I'm getting a "Subscript out of range' message for each record. At the top of this message box is "Error in fRetValidCharge()


Now I really do recognise that I am being a bit of a pest - but any ideas where I should look?


I've attached my actual file and I'm sure you may wet yourself we
When you see the actual ValitationTable (in this file its named Tabelle)


I'm on bended knees ADezii

Grateful for anything - I'll try and make this the last time - honest!

Many thanks


John

Attached File(s)
Attached File  Invoicing_File_Validation_for_ADezii.zip ( 170.26K )Number of downloads: 4
 
Go to the top of the page
 
ADezii
post Apr 19 2019, 01:16 PM
Post#13



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Will not be able to have a look at the DB until tomorrow.
Go to the top of the page
 
Berty
post Apr 19 2019, 01:18 PM
Post#14



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

I'm just so grateful that you will have a look - thanks

I'm just not sure where I've gone wrong -


very, very many thanks


John
Go to the top of the page
 
ADezii
post Apr 19 2019, 05:54 PM
Post#15



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Immediately, a couple of things become quite evident.
  2. The following line of Code will fail since there is no ClearColourFormats Procedure.
    CODE
    ClearColourFormats  ' Clears all colour formats from the 'Test Data' sheet so that it starts its analysis fresh
  3. The following line of Code will generate a Subscript out of range Error since there is no ValidationTable Worksheet. Where are your acceptable Reason Codes stored?
    CODE
    var = Application.Match(Cells(intRow, "BD"), Worksheets("ValidationTable").Range("C42:C112"), 0)
  4. I'm afraid that this appears to be just the beginning.

Go to the top of the page
 
Berty
post Apr 20 2019, 05:42 AM
Post#16



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

My fault - in my haste (my stress levels have gone off the scale as I'm meant to have this completed - and unfortunately panic has set in) I sent you my file before I had checked what I was sending - sincere apologies

I've attached it again

Some notes:

In my first example file I referred to a ValidationTable - in the attached 'real' file this is named "Tabelle"
In my first example file I referred Reasons - in the attached file - these are vehicles (these are in the Tabelle tab rows C43:C112 (marked with the purple background)

The countries appear in the Tabelle file in Row2 as 3 letter codes (as shown in cells F2, J2, N2 etc). The data I will want to 'validate will be in columns found in this Tabelle tabe in these same columns (F, J, N etc). Its a horrible format I agree (There is only partial data input at the moment - the majority of it being there so that the values can be pasted into the appropriate rows (F, J N etc at some point prior to use)


I have tested the file and it is giving me the error that I mentioned previously. I'm guessing that I have a range inappropriately specified in your function or something utterly stupid



So, I know I'm pushing too hard here - but one last look? please?


John
Attached File(s)
Attached File  Invoicing_File_Validation_5.0.zip ( 177.65K )Number of downloads: 3
 
Go to the top of the page
 
Berty
post Apr 20 2019, 08:53 AM
Post#17



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Update:


Just noticed that the Set ws = ActiveWorkbook.Worksheets("Tabelle") was set to ValidationTable

Now the error message is Type Mismatch (error in fRetValidCharge() at the tope of the message box)


John

The below code is where I have amended as above

CODE
Public Function fRetValidCharge(strTheCountry As String, strTheReason As String) As Single
On Error GoTo Err_fRetValidCharge
Dim ws As Excel.Worksheet
Dim rngData As Excel.Range
Dim rngReason As Excel.Range
Dim rngCountry As Excel.Range
Dim varRet As Variant


Set ws = ActiveWorkbook.Worksheets("Tabelle")

With ws
  Set rngData = .Range("F2:FJ112")    '.Range("C6:E18")
  Set rngReason = .Range("C43:C112")  '.Range("B6:B18")
  Set rngCountry = .Range("F2:FJ2")  '.Range("C5:E5")
End With

With Application.WorksheetFunction
  varRet = .Index(rngData, .Match(strTheReason, rngReason, 0), .Match(strTheCountry, rngCountry, 0))
    fRetValidCharge = varRet
End With

Exit_fRetValidCharge:
  Exit Function

Err_fRetValidCharge:
  fRetValidCharge = 0
  MsgBox Err.Description, vbExclamation, "Error in fRetValidCharge()"
    Resume Exit_fRetValidCharge
End Function

Go to the top of the page
 
ADezii
post Apr 20 2019, 09:26 AM
Post#18



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Try making the following three changes in fRetValidCharge()
    • Change the Reference to the Validation Table.
      CODE
      'Set ws = ActiveWorkbook.Worksheets("ValidationTable")
      Set ws = ActiveWorkbook.Worksheets("Tabelle")
    • Change the Data Range in Tabelle.
      CODE
      'Set rngData = .Range("F2:FJ112")
      Set rngData = .Range("F6:FM112")
    • REM out the Msgbox reference in the Error Trap.
      CODE
      'MsgBox Err.Description, vbExclamation, "Error in fRetValidCharge()"
  2. Revised Function Definition:
    CODE
    Public Function fRetValidCharge(strTheCountry As String, strTheReason As String) As Single
    On Error GoTo Err_fRetValidCharge
    Dim ws As Excel.Worksheet
    Dim rngData As Excel.Range
    Dim rngReason As Excel.Range
    Dim rngCountry As Excel.Range
    Dim varRet As Variant

    'Set ws = ActiveWorkbook.Worksheets("ValidationTable")
    Set ws = ActiveWorkbook.Worksheets("Tabelle")

    With ws
      'Set rngData = .Range("F2:FJ112")
      Set rngData = .Range("F6:FM112")    '.Range("C6:E18")
      Set rngReason = .Range("C43:C112")  '.Range("B6:B18")
      Set rngCountry = .Range("F2:FJ2")  '.Range("C5:E5")
    End With

    With Application.WorksheetFunction
      varRet = .Index(rngData, .Match(strTheReason, rngReason, 0), .Match(strTheCountry, rngCountry, 0))
        fRetValidCharge = varRet
    End With

    Exit_fRetValidCharge:
      Exit Function

    Err_fRetValidCharge:
      fRetValidCharge = 0
      'MsgBox Err.Description, vbExclamation, "Error in fRetValidCharge()"
        Resume Exit_fRetValidCharge
    End Function
Go to the top of the page
 
Berty
post Apr 20 2019, 09:39 AM
Post#19



Posts: 669
Joined: 10-October 04
From: Gloucester UK


Hi ADezii

As ever - in your debt

Coincidentally - just as your reply came in - I thought this was where I'd tripped up

Works great - many, many, many thanks



John
Go to the top of the page
 
ADezii
post Apr 20 2019, 09:47 AM
Post#20



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 06:44 AM