Highlight missing or duplicated cells

[Paul_Hossler]

I changed the MDS formatting / checking logic.

The checks don't run until there's an enter in Client (Col B), but the debug message seems to go away

Client Name (Col B) is used that way because you had line numbers in Col that went past real data

[pawcoyote]

Roger that on Worksheet, I will post up a new WB. I will check to be sure I did the DIM.

On the MDS sheet when I use the Clear Macro, it clears all formatting and if I try to use the fields right after that I get the Runtime Error. Right after I press the button and it clears I go to field B6 and enter something and go to the next cell it give the debug. It also stops all the other macros built in from running. No color coding, no Region etc... Nothing works after the clear...

[pawcoyote]

Good morning,

I was able to fix the Worksheet Clear. I changed the name of the sheet and I did miss things in the mod_Global. I made notes in there.

My big issue now is that when I use the Clear MDS Sheet it messes everything up in the area I can add info into. I get the Debug error. Once I click the Clear MDS Data it works good. But when I go into the area I can enter data starting at A7, I type a 1 in there and hit enter or try to go to another cell it gives the debug error. I also then loose all other functions in the cells i.e. Color Coding the dupes, required fields, the auto fill of Region and Site reference. I left and entered back into the sheet and still nothing works. I have attached Sample 30 and two screen shots with the error.

Runtime-Error-032217.jpgRuntime-Error-032217-VBA Section.jpg

I changed the MDS formatting / checking logic.

The checks don't run until there's an enter in Client (Col B), but the debug message seems to go away

Client Name (Col B) is used that way because you had line numbers in Col that went past real data

[Paul_Hossler]

1. I noticed that your WS 'Names' seems to have lists of acceptable values. You can add Data Validation rules to allow picking from a list. I did 'State' and 'Device Type' as examples. You can add more calls to pvtAddValidation for the rest of the fields

Capture4.JPG

Option Explicit
Sub AddValidationRules()
    Init
    Set headerNames = wsNames.Rows(rowHeaderNames)
    UnProtectMDS
    Call pvtAddValidation("Location State", "State", "State", "Pick State from list")
    Call pvtAddValidation("Device Type", "DeviceType")      '   Note - no space
    ProtectMDS
End Sub


Private Sub pvtAddValidation(DataColumn As String, ListColumn As String, Optional Title As String = "Allowed Values", Optional Msg As String = "Select entry from the list")
    Dim r As Range
    Dim colMDS As Long, colNames As Long
    colMDS = GetColumnNumber(DataColumn, headerMDS)
    colNames = GetColumnNumber(ListColumn, headerNames)
    
    Set r = wsNames.Cells(2, colNames)
    Set r = Range(r, r.End(xlDown))

    With wsMDS.Cells(rowDataStartMDS, colMDS).Resize(1000, 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & wsNames.Name & "'!" & r.Address(True, True)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = Title
        .ErrorTitle = vbNullString
        .InputMessage = Msg
        .ErrorMessage = vbNullString
        .ShowInput = True
        .ShowError = True
    End With
    
End Sub

2. I added a call to AddValidationRules to Clear_MDS to add the State at least as a DV rule

3. Region updates from State now (BTW, the 'Region' has 'Filed' instead of 'Field')

Capture5.JPG

[Paul_Hossler]

Good morning,

My big issue now is that when I use the Clear MDS Sheet it messes everything up in the area I can add info into. I get the Debug error. Once I click the Clear MDS Data it works good. But when I go into the area I can enter data starting at A7, I type a 1 in there and hit enter or try to go to another cell it gives the debug error. I also then loose all other functions in the cells i.e. Color Coding the dupes, required fields, the auto fill of Region and Site reference. I left and entered back into the sheet and still nothing works. I have attached Sample 30 and two screen shots with the error.

That should have been corrected in v29 on Post 21 by adding some 'no data' logic (below). I did not see the added logic on your highlighted debug message

'   -------------------------------------------- state
    If rowDataEndMDS - rowDataStartMDS + 1 = 0 Then
        Set rMDS = wsMDS.Rows(rowDataStartMDS)
    Else
        Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)
    End If

Try the ver 30 on post 24 and see

[pawcoyote]

Thank you I will look them both over.. I truly appreciate all your assistance.

[pawcoyote]

Would it be hard on the MDS Sheet to Start Clearing from Column B Row 7. Leaving Column A alone? Or would that mess up everything you have done.

That should have been corrected in v29 on Post 21 by adding some 'no data' logic (below). I did not see the added logic on your highlighted debug message

'   -------------------------------------------- state
    If rowDataEndMDS - rowDataStartMDS + 1 = 0 Then
        Set rMDS = wsMDS.Rows(rowDataStartMDS)
    Else
        Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)
    End If

Try the ver 30 on post 24 and see

[Paul_Hossler]

Would it be hard on the MDS Sheet to Start Clearing from Column B Row 7. Leaving Column A alone? Or would that mess up everything you have done.

This is Excel and VBA ... NOTHING is impossible

Well, maybe not everything

Look at the last ver 30 (I forgot to bump the version suffix I guess) and I'll at this and your feedback into ver 31

[pawcoyote]

Hi, that is cool.. Do you want a copy of the most up to date version I have? I am attaching it here version 33... I am still getting the error when I click the Clear MDS Data .. Maybe I didn't write the code right?

This is Excel and VBA ... NOTHING is impossible

Well, maybe not everything

Look at the last ver 30 (I forgot to bump the version suffix I guess) and I'll at this and your feedback into ver 31

[pawcoyote]

I fixed my DataValidations with the Named Ranges. I also have the complete workbook up to date with every sheet. I am still getting the errors when I try to Clear the MDS Equipment Sheet.

[Paul_Hossler]

It looks like a change was not in your 33a, so I put it in 34

[pawcoyote]

Not sure what you changed but that worked. But, the State to Region and Build of the Site Reference stopped working. I also added in the .ClearContent of the PreMDS Sheet, which works.

It looks like a change was not in your 33a, so I put it in 34

[Paul_Hossler]

The State-Region used to be in J:K on Names, now it's in L:N

Capture.JPG

So when looking up a State, no entry was found

That also affected 'Oracle Site Reference'

[pawcoyote]

I redid the Names Sheet to make it easier for viewing and finding things and I lined them up with how the names flow on the MDS sheet. Where would I fix them if I add items into the Names sheet or move things?

I went to the mod_MDS and Changed the line to be "Set rStatesRegions = Worksheets("Names").Range("L:N")" But It keeps showing M (Floor) associated to the State. It works on your sheet but I would like to see where I make the changes to make this work.. Are there other locations I need to update?

The Site Reference is coming in Correctly.

[pawcoyote]

I was able to get it to work by just copying your mod_MDS to mine but I would love to know where changes happen for my edification!

[Paul_Hossler]

I redid the Names Sheet to make it easier for viewing and finding things and I lined them up with how the names flow on the MDS sheet. Where would I fix them if I add items into the Names sheet or move things?

I went to the mod_MDS and Changed the line to be "Set rStatesRegions = Worksheets("Names").Range("L:N")" But It keeps showing M (Floor) associated to the State. It works on your sheet but I would like to see where I make the changes to make this work.. Are there other locations I need to update?

The Site Reference is coming in Correctly.

In the associated VLookup, change the 2 to 3 since before using J:K the region was in the second column of the Range, but now using L:N region is in the third

[pawcoyote]

Roger thank you, one more question.. In the Data Validation fields can I have a auto complete or search type feature? i.e. I start to Type MFD and as I type it starts to fill in or allow me to select from the list.. It would be neat to be able to do that without having to scroll through the listings... Just a thought..

In the associated VLookup, change the 2 to 3 since before using J:K the region was in the second column of the Range, but now using L:N region is in the third

[Paul_Hossler]

Roger thank you, one more question.. In the Data Validation fields can I have a auto complete or search type feature? i.e. I start to Type MFD and as I type it starts to fill in or allow me to select from the list.. It would be neat to be able to do that without having to scroll through the listings... Just a thought..

No way to do that I'm aware of

Sorry

[pawcoyote]

Roger that... I will be starting a new thread soon. This one is complete. I will be using the same workbook but different criteria.

[pawcoyote]

Quick question for you.. When copying from the MDS to the MOST on the Ship to Address it is dropping the Lead Zero for the Zip Code is there a way to fix that?

No way to do that I'm aware of

Sorry