VBA Code to Match US Region to the State that is entered

Quote:

---

I added the .Offset line to clear the rest of the line if Client is blank like in Line 7

---

Remove this line

Code:

---

.Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents

---

Thank you I will look at that. I am also working on a new sheet that I will want to copy information from the MDS sheet into I am going to add it to a sample and post once done...

Quote:

---

Originally Posted by Paul_Hossler

Remove this line

Code:

---

.Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents

---

---

Hi, I have an add on to this now. I have included a new worksheet called MOST. I need to copy specific fields from the "MDS Equipment Detail Worksheet and put them into the MOST worksheet. On the MOST worksheet I have put the Headers from the MDS worksheet and their column letter so you can see what I am trying to copy over. I would like this set as a macro (button) that I can click once I have all the data keyed in properly on the MDS worksheet.

Thank you for any help.

Please see Sample Workbook_18

Try this

You might need to tweak it

Thank you very much I will be looking it over and again thank you for all your help.

Quote:

---

Originally Posted by Paul_Hossler

Try this

You might need to tweak it

---

Hi,

If I wanted to use the Column Headers for reference on the MOST and MDS worksheet instead of the Column Number do I substitute the number i.e. 1 with the Header name i.e. "Oracle Project Code" on the MDS worksheet and "Project Number" on the MOST worksheet?

Quote:

---

wsMOST.Cells(i - 1, "Project Number").Value = .Cells(i, "Oracle Project Code").Value

---

Additional Question: Why do I get the following when I go to look at my macro's

Quote:

---

'Sample Workbook_19.xlsm'!VersionNotes.VersionNotes

---

Quote:

---

Originally Posted by Paul_Hossler

Try this

You might need to tweak it

---

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Code:

---



Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
   
    i = 0
   
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
       
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
   
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
       
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

---

Quote:

---

Additional Question: Why do I get the following when I go to look at my macro's

---

Probably because you had 2 workbooks open with the same sheet names and macro names

Thank you I will be working on this today. I have a quick question for you. I keep getting the following Sheet added to my workbook and I don't know why. It is a Very Hidden worksheet.

Quote:

---

Originally Posted by Paul_Hossler

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time It goes down 412 Rows and out to Column IV. I had to remove some of the rows to make the file size fit for upload.


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Code:

---



Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
   
    i = 0
   
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
       
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
   
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
       
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

---

Probably because you had 2 workbooks open with the same sheet names and macro names

---

No idea why that sheet is being added

Ver 20 does not have it, so it must be something in your real workbook

It references another workbook if that helps

Attachment 18351

Thank you, I will look at what I might have put in...

Hi,

I no longer get the Regions populated when a State is added nor is the Oracle Site Reference being added. I have added version 21 with changes I have made so you can see what I have done so far. Quick note on the error, could JIVE be causing that to happen?

Quote:

---

Originally Posted by Paul_Hossler

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Code:

---



Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
   
    i = 0
   
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
       
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
   
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
       
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

---

Probably because you had 2 workbooks open with the same sheet names and macro names

---

When I changed it to use column headers instead of column numbers, I had a couple wrong

That would have been on the MDS module correct? I want to be sure I am looking at the right mod for fixes.

Yes

The way I made the LUV() function it takes the col header string so look in that area

Hi, I would like to protect the workbook/worksheets once we are done with the coding. But when I do the VBA and Macro's do not run properly. Also I am seeing that the VBA isn't running in excel 2010. I built this on excel 2007. Any suggestions?

Quote:

---

Originally Posted by Paul_Hossler

Yes

The way I made the LUV() function it takes the col header string so look in that area

---

I found out what is doing it but not sure why. I use Jive and every time I post the workbook using jive it adds this hidden worksheet. Still looking for a fix..

Quote:

---

Originally Posted by Paul_Hossler

No idea why that sheet is being added

Ver 20 does not have it, so it must be something in your real workbook

It references another workbook if that helps

Attachment 18351

---

Don't know what Jive is, but maybe don't use it

To allow some cells to be updated when the sheet is protected, you need to make .Locked = False

Code:

---

Option Explicit

Sub ProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowFiltering:=True
    End With
End Sub


Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

---

Hi, I added the code to the MDS Equipment Detail Sheet in VBA but I keep getting an error once I protect the sheet.

I need to unprotect the following when we enter the MDS Equipment Detail sheet. Allowing all VBA and Macros to run...

Row 5 & 6 Certain Headers Columns CA - CL (I want to be able to change the names of the headers as needed

Row 7 and on all fields to be open for running VBA and Macros..

I am also looking to only copy the information from the MDS Equipment Detail Sheet to the MOST Add Sheet when a Client Name is filled in the Client Name column otherwise do not copy those rows..

When I protect the MDS Equipment Detail sheet this is where the error goes too..

With rMDS
.Interior.Color = xlNone

Quote:

---

Originally Posted by Paul_Hossler

Don't know what Jive is, but maybe don't use it

To allow some cells to be updated when the sheet is protected, you need to make .Locked = False

Code:

---

Option Explicit

Sub ProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowFiltering:=True
    End With
End Sub


Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

---

---

1. I put the protect subs on mod_Util since you have to run them manually and added the CA-CL write capability, marked with <<< below so that you can change it

Code:

---

Sub ProtectMDS()

    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range("CA5").Resize(2, 16).Locked = False      '<<<<<<<<<<<<<<<<<<<
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, _
            DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
            AllowDeletingRows:=True, AllowFiltering:=True
        .EnableSelection = xlUnlockedCells
    End With
End Sub
 
 
Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

---

2. You must have changed the layout of MOST so the header row was wrong

Code:

---

Public Const rowHeaderMOST As Long = 4
Public Const rowDataStartMOST As Long = 5

---

3. I changed the logic to not use the UsedRange on MDS but only down to the last row that has a Client

Yes sorry I did add another Header in Column B.