My Assistant
Site Message
(Message will auto close in 2 seconds)
 Vba Reference To A Named Range
|
|
 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
|
Feb 23 2012, 02:23 PM
Post
#2
|
|
|
UtterAccess Enthusiast Posts: 55 |
Yes try something like this:
CODE For Each x In Employees.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 |
|
|
|
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 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 |
 |
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th June 2013 - 11:19 AM |