Access Help and How-to - Microsoft Office Vba Reference To A Named Range

Home

Discussion Forums

Search

Vba Reference To A Named Range

Options

robbya2 View Member Profile	Feb 23 2012, 01:34 PM Post #1
UtterAccess Veteran Posts: 388	Hello, I have a workbook with a few sheets. One of which is an OT reporting list. I have the name of the EE field enabled with a drop down to select the name. I have created some code that fills out rates attached to that employee in the next fields. This is currently reliant on a reference list I placed on the same sheet over in column O. I have created a separate sheet to contain my lists to make it easier to maintain and also need to modify this so it's a dynamic list. I have already learned how to create a dynamic named range and that is what I am using for the drop down source. I now need to use this named range as my references in the VBA code that populates my fields based on the name selected. Below you will find the code I am using in the interim which is reliant on the list never changing hence the need to imporve it... CODE Private Sub Worksheet_Change(ByVal Target As Range) Dim NewCell As String Dim Rate As String Dim clrRange As String If Target.Column = "2" Then If Target.Cells.Count > 1 Then Exit Sub End If Level = ColumnLetter(ActiveCell.Column + 1) & ActiveCell.Row Rate = ColumnLetter(ActiveCell.Column + 2) & ActiveCell.Row Select Case ActiveCell.Value Case Range("O10").Value Range(Level).Value = Range("P10").Value Range(Rate).Value = Range("Q10").Value Case Range("O11").Value Range(Level).Value = Range("P11").Value Range(Rate).Value = Range("Q11").Value Case Range("O12").Value Range(Level).Value = Range("P12").Value Range(Rate).Value = Range("Q12").Value Case Range("O13").Value Range(Level).Value = Range("P13").Value Range(Rate).Value = Range("Q13").Value Case Range("O14").Value Range(Level).Value = Range("P14").Value Range(Rate).Value = Range("Q14").Value Case Range("O15").Value Range(Level).Value = Range("P15").Value Range(Rate).Value = Range("Q15").Value Case Range("O16").Value Range(Level).Value = Range("P16").Value Range(Rate).Value = Range("Q16").Value Case Range("O17").Value Range(Level).Value = Range("P17").Value Range(Rate).Value = Range("Q17").Value Case Range("O18").Value Range(Level).Value = Range("P18").Value Range(Rate).Value = Range("Q18").Value Case Range("O19").Value Range(Level).Value = Range("P19").Value Range(Rate).Value = Range("Q19").Value Case Range("O20").Value Range(Level).Value = Range("P20").Value Range(Rate).Value = Range("Q20").Value Case Range("O21").Value Range(Level).Value = Range("P21").Value Range(Rate).Value = Range("Q21").Value Case Range("O22").Value Range(Level).Value = Range("P22").Value Range(Rate).Value = Range("Q22").Value Case Range("O23").Value Range(Level).Value = Range("P23").Value Range(Rate).Value = Range("Q23").Value Case Range("O24").Value Range(Level).Value = Range("P24").Value Range(Rate).Value = Range("Q24").Value Case Range("O25").Value Range(Level).Value = Range("P25").Value Range(Rate).Value = Range("Q25").Value Case Range("O26").Value Range(Level).Value = Range("P26").Value Range(Rate).Value = Range("Q26").Value Case Range("O27").Value Range(Level).Value = Range("P27").Value Range(Rate).Value = Range("Q27").Value Case Else Range(Level).Value = Null Range(Rate).Value = Null End Select ElseIf Target.Column = "9" Then clrRange = "A1" Select Case ActiveCell.Value Case "Estimate" Range(ActiveCell.Address).EntireRow.Characters.Font.Color = vbBlack Case "Confirmed" Range(ActiveCell.Address).EntireRow.Characters.Font.Color = 6723891 Case "" Range(ActiveCell.Address).EntireRow.Characters.Font.Color = vbBlack Case Null Range(ActiveCell.Address).EntireRow.Characters.Font.Color = vbBlack End Select End If End Sub Here is what I have an issue with for now. The named range on the Rates-Lists sheet is called "Employees". When I change the code to refer to it rather than a cell I get an error "Runtime 1004, Method 'range' of object '_Worksheet' failed" CODE Select Case ActiveCell.Value Case Range("Employees").Value Any help would be great!! Thanks.

Replies

robbya2 View Member Profile	Mar 2 2012, 01:27 PM Post #2
UtterAccess Veteran Posts: 388	QUOTE (MedicalServices @ Mar 2 2012, 03:11 PM) If memory serves, I had my named range on the same sheet. If you set a range variable to the named range on a separate sheet it should work fine. See if this works... CODE Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Range, rngEmp As Range Set rngEmp = ThisWorkbook.Worksheets("Sheet2").Range("Employees") ''Change to suit For Each x In rngEmp.Cells If x = Target.Value Then Range(Level).Value = x.Offset(0, 1) Range(Rate).Value = x.Offset(0, 2) Exit For End If Next End Sub Got it, I used the above to modify the code you gave me earlier and it works great, so instead of referring to Range("Employees") it wants me to refer to the thisworkbook.worksheets... Thank you!! CODE For Each x In ThisWorkbook.Worksheets("Rates-Lists").Range("Employees") If x = Target.Value Then Range(Level).Value = x.Offset(0, 1) Range(Rate).Value = x.Offset(0, 2) Exit For End If Next

Posts in this topic

robbya2 Vba Reference To A Named Range Feb 23 2012, 01:34 PM

norie Try adding a worksheet reference to the range. COD... Feb 23 2012, 01:50 PM

robbya2 QUOTE (norie @ Feb 23 2012, 07:50 PM) Try... Feb 23 2012, 01:54 PM

robbya2 Thinking about it a little further what I am tryng... Feb 23 2012, 02:00 PM

MedicalServices If "Employees" is a named range then I d... Feb 23 2012, 02:06 PM

norie I know Employees is a named range, I was asking if... Feb 23 2012, 02:11 PM

robbya2 QUOTE (norie @ Feb 23 2012, 08:11 PM) I k... Feb 23 2012, 02:13 PM

robbya2 QUOTE (MedicalServices @ Feb 23 2012, 08... Feb 23 2012, 02:16 PM

MedicalServices sorry newbie mistake! Feb 23 2012, 02:20 PM

MedicalServices Yes try something like this: CODEFor Each x In Emp... Feb 23 2012, 02:23 PM

robbya2 QUOTE (MedicalServices @ Feb 23 2012, 08... Feb 23 2012, 02:46 PM

MedicalServices yes sorry I was just trying to get the jest of it ... Feb 23 2012, 02:54 PM

robbya2 QUOTE (MedicalServices @ Feb 23 2012, 08... Feb 23 2012, 02:55 PM

robbya2 just had a chance to try quick and it fails on the... Feb 23 2012, 02:56 PM

MedicalServices That's because I had Range("Employee... Feb 23 2012, 03:01 PM

robbya2 QUOTE (MedicalServices @ Feb 23 2012, 08... Feb 29 2012, 11:08 AM

MedicalServices I don't understand- I tried it out on a named ... Mar 1 2012, 05:23 PM

robbya2 QUOTE (MedicalServices @ Mar 1 2012, 11:2... Mar 2 2012, 07:18 AM

MedicalServices If memory serves, I had my named range on the same... Mar 2 2012, 09:11 AM

robbya2 QUOTE (MedicalServices @ Mar 2 2012, 03:1... Mar 2 2012, 01:27 PM

« Next Oldest · Microsoft Excel · Next Newest »

Jump To Forum: