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. |
 |
 
|
|
Feb 23 2012, 01:50 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 4,308 |
Try adding a worksheet reference to the range.
CODE Case Worksheets("Rates-Lists").Range("Employees").Value By the way, is 'Employees' just one cell? If it isn't I'm not sure it'll work in the Select Case |
|
|
|
|
Feb 23 2012, 01:54 PM
Post
#3
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (norie @ Feb 23 2012, 07:50 PM)  Try adding a worksheet reference to the range. CODE Case Worksheets("Rates-Lists").Range("Employees").Value By the way, is 'Employees' just one cell? If it isn't I'm not sure it'll work in the Select Case I will try this, "Employees" is the name of a named range. |
|
|
|
|
Feb 23 2012, 02:00 PM
Post
#4
|
|
|
UtterAccess Veteran Posts: 388 |
Thinking about it a little further what I am tryng to do doesn't make real good sense. I need the code to say when a particular name is selected to fill in the fields with the proper values however if I change the reference to a named range the code wouldn't know which one to use... I have to rethink this through i think.
In my lists sheet I have a list of 3 columns including their name, position level, rate of OT. When the name is selected in the drop down I want it to take values from the list column 2,3 that column 1 matches with the drop down selection copied to the columns 2,3 on the tracking sheet. The code I have works but as I said it doesn't allow flexibility if employees change since it relies on the cell designated to that employee... perhaps a new perspective could yeild a different solution... thanks |
|
|
|
|
Feb 23 2012, 02:06 PM
Post
#5
|
|
|
UtterAccess Enthusiast Posts: 55 |
If "Employees" is a named range then I don't think you can use it as a 'Case'
Fom what I can determine, if you want to examine the target cell and compare it's value to the values in the "Employees" named range then you can use a For Each to loop through the named range and compare each to the active cell's value. When you find a match use the .Offset property to collect the proper values you area looking for.. Is that what you are after?? |
|
|
|
|
Feb 23 2012, 02:11 PM
Post
#6
|
|
|
UtterAccess VIP Posts: 4,308 |
I know Employees is a named range, I was asking if it contained more than one cell.(IMG:style_emoticons/default/smile.gif)
|
|
|
|
|
Feb 23 2012, 02:13 PM
Post
#7
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (norie @ Feb 23 2012, 08:11 PM) I know Employees is a named range, I was asking if it contained more than one cell.(IMG:style_emoticons/default/smile.gif) Ohh ok sorry (IMG:style_emoticons/default/smile.gif) no it contains multiple cells, 17. That is what the select case is running through actually the list of employees but I have them on the same sheet right now in a separate list. |
|
|
|
|
Feb 23 2012, 02:16 PM
Post
#8
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (MedicalServices @ Feb 23 2012, 08:06 PM) If "Employees" is a named range then I don't think you can use it as a 'Case' Fom what I can determine, if you want to examine the target cell and compare it's value to the values in the "Employees" named range then you can use a For Each to loop through the named range and compare each to the active cell's value. When you find a match use the .Offset property to collect the proper values you area looking for.. Is that what you are after?? Yes this would be what I would need, that way if the range changes the loop would just look at the current list. So once the loop locates a match to the target cell, i would then use offset to indivudually move over by column + 1 and copy the value to the cell I need?? |
|
|
|
|
Feb 23 2012, 02:20 PM
Post
#9
|
|
|
UtterAccess Enthusiast Posts: 55 |
sorry
newbie mistake! This post has been edited by MedicalServices: Feb 23 2012, 02:24 PM |
|
|
|
|
Feb 23 2012, 02:23 PM
Post
#10
|
|
|
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 |
|
|
|
|
Feb 23 2012, 02:46 PM
Post
#11
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (MedicalServices @ Feb 23 2012, 08:23 PM) 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 Do I have to declare x as anyting? It gives me an error that an object is required when it falls on line 1. |
|
|
|
|
Feb 23 2012, 02:54 PM
Post
#12
|
|
|
UtterAccess Enthusiast Posts: 55 |
yes sorry I was just trying to get the jest of it out there
CODE Dim x as range For Each x In Range("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 I also changed the for each to a range--sorry about that in a hurry again This post has been edited by MedicalServices: Feb 23 2012, 02:55 PM |
|
|
|
|
Feb 23 2012, 02:55 PM
Post
#13
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (MedicalServices @ Feb 23 2012, 08:54 PM) yes sorry I was just trying to get the jest of it out there CODE Dim x as range For Each x In Range("Employee").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 I also changed the for each to a range--sorry about that in a hurry again No problem, I will be away for the weekend but will try this asap and report back... Thanks! |
|
|
|
|
Feb 23 2012, 02:56 PM
Post
#14
|
|
|
UtterAccess Veteran Posts: 388 |
just had a chance to try quick and it fails on the first line with error 1004 Method range...
I will check in again on Monday... thanks!! |
|
|
|
|
Feb 23 2012, 03:01 PM
Post
#15
|
|
|
UtterAccess Enthusiast Posts: 55 |
That's because I had Range("Employee") in there instead of Range("Employees")
I edited the post to the correct range. QUOTE For Each x In Range("Employee").Cells
|
|
|
|
|
Feb 29 2012, 11:08 AM
Post
#16
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (MedicalServices @ Feb 23 2012, 08:01 PM) That's because I had Range("Employee") in there instead of Range("Employees") I edited the post to the correct range. It gives the same error as soon as it tries to refer to the "Employees" range... Its really wierd when it should be working and it doesn't,... |
|
|
|
|
Mar 1 2012, 05:23 PM
Post
#17
|
|
|
UtterAccess Enthusiast Posts: 55 |
I don't understand- I tried it out on a named range in the context that you are talking about and it works for me...??
Do you have an example or sample you can attach? |
|
|
|
|
Mar 2 2012, 07:18 AM
Post
#18
|
|
|
UtterAccess Veteran Posts: 388 |
QUOTE (MedicalServices @ Mar 1 2012, 11:23 PM) I don't understand- I tried it out on a named range in the context that you are talking about and it works for me...?? Do you have an example or sample you can attach? You tried it with the named range on a separate sheet? Wierd, I wonder if there are some references that need to be activated like in Access or some declarations at the top of the code that you have and I don't... |
|
|
|
|
Mar 2 2012, 09:11 AM
Post
#19
|
|
|
UtterAccess Enthusiast Posts: 55 |
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 This post has been edited by MedicalServices: Mar 2 2012, 09:16 AM |
|
|
|
|
Mar 2 2012, 01:27 PM
Post
#20
|
|
|
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 |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th June 2013 - 03:57 PM |