| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range for a look up
I have a vlookup formula, but want to programatically change the range the
look up looks at. The data set is filtered by vba by user selection from a drop down list If I want to change =IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE)) to the same range as is filtered. In one scenario the data range is filtered to rows 2008 to 2243 (rather than 2 to 4000), how can I change the formula to =IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE)) Presumably by defining a named range each time the filter is applied. Thanks in anticipation |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining a range for a look up
If the range will Always be contiguous
Dim rng as range, rng1 as Range Range("D2:S4000").Name = "Table1" if Activesheet.AutoFilterMode then set rng = Activesheet.AutoFilterRange rng.offset(1,0).Resize(rng.rows.count-1) On Error Resume Next set rng1 = rng.SpecialCells(xlVisible) On Error goto 0 if rng1 is nothing then msgbox "No visible rows" exit sub elseif rng1.areas.count 1 then msgbox "Filtered area is not contiguous" exit sub end if rng1.Name = "Table1" =IF(S4="","",VLOOKUP(S4,Table1,16,FALSE)) -- Regards, Tom Ogilvy "MH UK" wrote in message ... I have a vlookup formula, but want to programatically change the range the look up looks at. The data set is filtered by vba by user selection from a drop down list If I want to change =IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE)) to the same range as is filtered. In one scenario the data range is filtered to rows 2008 to 2243 (rather than 2 to 4000), how can I change the formula to =IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE)) Presumably by defining a named range each time the filter is applied. Thanks in anticipation |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining a range for a look up
Tom,
Thank you. I am getting a runtime error "Invalid Use of property" at rng.Offset(1, 0).Resize (rng.Rows.Count - 1) I shall have a play around to try and fix, unless you beat me to the fix! Martin. "Tom Ogilvy" wrote: If the range will Always be contiguous Dim rng as range, rng1 as Range Range("D2:S4000").Name = "Table1" if Activesheet.AutoFilterMode then set rng = Activesheet.AutoFilterRange rng.offset(1,0).Resize(rng.rows.count-1) On Error Resume Next set rng1 = rng.SpecialCells(xlVisible) On Error goto 0 if rng1 is nothing then msgbox "No visible rows" exit sub elseif rng1.areas.count 1 then msgbox "Filtered area is not contiguous" exit sub end if rng1.Name = "Table1" =IF(S4="","",VLOOKUP(S4,Table1,16,FALSE)) -- Regards, Tom Ogilvy "MH UK" wrote in message ... I have a vlookup formula, but want to programatically change the range the look up looks at. The data set is filtered by vba by user selection from a drop down list If I want to change =IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE)) to the same range as is filtered. In one scenario the data range is filtered to rows 2008 to 2243 (rather than 2 to 4000), how can I change the formula to =IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE)) Presumably by defining a named range each time the filter is applied. Thanks in anticipation |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining a range for a look up
Tom,
What if the range is not contiguous? "Tom Ogilvy" wrote: If the range will Always be contiguous Dim rng as range, rng1 as Range Range("D2:S4000").Name = "Table1" if Activesheet.AutoFilterMode then set rng = Activesheet.AutoFilterRange rng.offset(1,0).Resize(rng.rows.count-1) On Error Resume Next set rng1 = rng.SpecialCells(xlVisible) On Error goto 0 if rng1 is nothing then msgbox "No visible rows" exit sub elseif rng1.areas.count 1 then msgbox "Filtered area is not contiguous" exit sub end if rng1.Name = "Table1" =IF(S4="","",VLOOKUP(S4,Table1,16,FALSE)) -- Regards, Tom Ogilvy "MH UK" wrote in message ... I have a vlookup formula, but want to programatically change the range the look up looks at. The data set is filtered by vba by user selection from a drop down list If I want to change =IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE)) to the same range as is filtered. In one scenario the data range is filtered to rows 2008 to 2243 (rather than 2 to 4000), how can I change the formula to =IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE)) Presumably by defining a named range each time the filter is applied. Thanks in anticipation |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining a range for a look up
Here is the revised code:
Sub abc() Dim rng As Range, rng1 As Range Range("D2:S4000").Name = "Table1" If ActiveSheet.AutoFilterMode Then Set rng = ActiveSheet.AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) On Error Resume Next Set rng1 = rng.SpecialCells(xlVisible) On Error GoTo 0 If rng1 Is Nothing Then MsgBox "No visible rows" Exit Sub ElseIf rng1.Areas.Count 1 Then Debug.Print rng1.Address MsgBox "Filtered area is not contiguous" Exit Sub End If rng1.Name = "Table1" End If End Sub You can't look up a none contiguous range. -- Regards, Tom Ogilvy "MH UK" wrote in message ... Tom, What if the range is not contiguous? "Tom Ogilvy" wrote: If the range will Always be contiguous Dim rng as range, rng1 as Range Range("D2:S4000").Name = "Table1" if Activesheet.AutoFilterMode then set rng = Activesheet.AutoFilterRange rng.offset(1,0).Resize(rng.rows.count-1) On Error Resume Next set rng1 = rng.SpecialCells(xlVisible) On Error goto 0 if rng1 is nothing then msgbox "No visible rows" exit sub elseif rng1.areas.count 1 then msgbox "Filtered area is not contiguous" exit sub end if rng1.Name = "Table1" =IF(S4="","",VLOOKUP(S4,Table1,16,FALSE)) -- Regards, Tom Ogilvy "MH UK" wrote in message ... I have a vlookup formula, but want to programatically change the range the look up looks at. The data set is filtered by vba by user selection from a drop down list If I want to change =IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE)) to the same range as is filtered. In one scenario the data range is filtered to rows 2008 to 2243 (rather than 2 to 4000), how can I change the formula to =IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE)) Presumably by defining a named range each time the filter is applied. Thanks in anticipation |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining a range for a look up
Tom,
Thanks - you pointed me in the right direction and is now sorted. "Tom Ogilvy" wrote: If the range will Always be contiguous Dim rng as range, rng1 as Range Range("D2:S4000").Name = "Table1" if Activesheet.AutoFilterMode then set rng = Activesheet.AutoFilterRange rng.offset(1,0).Resize(rng.rows.count-1) On Error Resume Next set rng1 = rng.SpecialCells(xlVisible) On Error goto 0 if rng1 is nothing then msgbox "No visible rows" exit sub elseif rng1.areas.count 1 then msgbox "Filtered area is not contiguous" exit sub end if rng1.Name = "Table1" =IF(S4="","",VLOOKUP(S4,Table1,16,FALSE)) -- Regards, Tom Ogilvy "MH UK" wrote in message ... I have a vlookup formula, but want to programatically change the range the look up looks at. The data set is filtered by vba by user selection from a drop down list If I want to change =IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE)) to the same range as is filtered. In one scenario the data range is filtered to rows 2008 to 2243 (rather than 2 to 4000), how can I change the formula to =IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE)) Presumably by defining a named range each time the filter is applied. Thanks in anticipation |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Defining a range and use it's value | Excel Programming | |||
| Help with defining a Range | Excel Programming | |||
| Defining a range | Excel Worksheet Functions | |||
| Defining a Range | Excel Programming | |||
| Defining a range | Excel Programming | |||
Linear Mode
