Defining a range for a look up

[MH UK]

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,FALSE))
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

 

[Tom Ogilvy]

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))

 

[MH UK]

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.

 

[MH UK]

Tom,

What if the range is not contiguous?

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))

 

[Tom Ogilvy]

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.

 

[MH UK]

Tom,

Thanks - you pointed me in the right direction and is now sorted.