UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Vba Reference To A Named Range    
 
   
robbya2
post Feb 23 2012, 01:34 PM
Post #1

UtterAccess Veteran
Posts: 420



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

THere 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.
Go to the top of the page
 
+
norie
post Feb 23 2012, 01:50 PM
Post #2

UtterAccess VIP
Posts: 4,448



Try adding a worksheet reference to the range.
CODE
Case Worksheets("Rates-Lists").Range("Employees").Value

My the way, is 'Employees' just one cell?
If it isn't I'm not sure it'll work in the Select Case
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 01:54 PM
Post #3

UtterAccess Veteran
Posts: 420



I will try this, "Employees" is the name of a named range.
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 02:00 PM
Post #4

UtterAccess Veteran
Posts: 420



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.
On 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
Go to the top of the page
 
+
MedicalServices
post 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??
Go to the top of the page
 
+
norie
post Feb 23 2012, 02:11 PM
Post #6

UtterAccess VIP
Posts: 4,448



I know Employees is a named range, I was asking if it contained more than one cell.<
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 02:13 PM
Post #7

UtterAccess Veteran
Posts: 420



Ohh ok sorry < 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.
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 02:16 PM
Post #8

UtterAccess Veteran
Posts: 420



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??
Go to the top of the page
 
+
MedicalServices
post Feb 23 2012, 02:20 PM
Post #9

UtterAccess Enthusiast
Posts: 55



sorry
newbie mistake!
Go to the top of the page
 
+
MedicalServices
post 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
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 02:46 PM
Post #11

UtterAccess Veteran
Posts: 420



Do I have to declare x as anyting? It gives me an error that an object is required when it falls on line 1.
Go to the top of the page
 
+
MedicalServices
post 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

also changed the for each to a range--sorry about that in a hurry again
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 02:55 PM
Post #13

UtterAccess Veteran
Posts: 420



No problem, I will be away for the weekend but will try this asap and report back...
Thanks!
Go to the top of the page
 
+
robbya2
post Feb 23 2012, 02:56 PM
Post #14

UtterAccess Veteran
Posts: 420



just had a chance to try quick and it fails on the first line with error 1004 Method range...
will check in again on Monday... thanks!!
Go to the top of the page
 
+
MedicalServices
post 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")
Oedited the post to the correct range.
Go to the top of the page
 
+
robbya2
post Feb 29 2012, 11:08 AM
Post #16

UtterAccess Veteran
Posts: 420



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,...
Go to the top of the page
 
+
MedicalServices
post 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?
Go to the top of the page
 
+
robbya2
post Mar 2 2012, 07:18 AM
Post #18

UtterAccess Veteran
Posts: 420



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...
Go to the top of the page
 
+
MedicalServices
post 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
Go to the top of the page
 
+
robbya2
post Mar 2 2012, 01:27 PM
Post #20

UtterAccess Veteran
Posts: 420



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
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 28th November 2014 - 06:59 AM