Match function and displaying results
 

I have the following modified code (provided by Tom Ogilvy and Bob Phillips):

Dim rng as Range, rng1 as Range
dim res as Variant
SetRng = VMM_Workbook.Worksheets(ShortVMM_FileName).Range(" D1:D2000")
res = Application.Match(Range(ID_Location), VMM_Sig_Name_Rng.Offset(0, -2), 0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
msgbox rng1.Value & " Address: " & rng1.Address
End if

Offset(1, 15).Value = rng1 ????

I want to have two actions happen:
1. If there is an error I want to assign "SIGNAL ID NOT DEFINED" to the rng1.
2. Error or not, I want to insert the results into a relative address, which
depends on the present location.

Thanks

Match function and displaying results
 

If there is an error there is no rng1 - and you can't assign a string value
to it.

if iserror(res) then
Activecell.offset(1,15).Value = "SIGNAL ID NOT DEFINED"
else
ActiveCell.offset(1,15).Value = _
VMM_Sig_Name_Rng.Offset(res, -2).Resize(1,1).Value
End sub

--
Regards,
Tom Ogilvy


"Dan" wrote:

I have the following modified code (provided by Tom Ogilvy and Bob Phillips):

Dim rng as Range, rng1 as Range
dim res as Variant
SetRng = VMM_Workbook.Worksheets(ShortVMM_FileName).Range(" D1:D2000")
res = Application.Match(Range(ID_Location), VMM_Sig_Name_Rng.Offset(0, -2), 0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
msgbox rng1.Value & " Address: " & rng1.Address
End if

Offset(1, 15).Value = rng1 ????

I want to have two actions happen:
1. If there is an error I want to assign "SIGNAL ID NOT DEFINED" to the rng1.
2. Error or not, I want to insert the results into a relative address, which
depends on the present location.

Thanks

Match function and displaying results
 

Thanks for the response. It's what I thought but hoped someone had some tricks.

"Tom Ogilvy" wrote:

If there is an error there is no rng1 - and you can't assign a string value
to it.

if iserror(res) then
Activecell.offset(1,15).Value = "SIGNAL ID NOT DEFINED"
else
ActiveCell.offset(1,15).Value = _
VMM_Sig_Name_Rng.Offset(res, -2).Resize(1,1).Value
End sub

--
Regards,
Tom Ogilvy


"Dan" wrote:

I have the following modified code (provided by Tom Ogilvy and Bob Phillips):

Dim rng as Range, rng1 as Range
dim res as Variant
SetRng = VMM_Workbook.Worksheets(ShortVMM_FileName).Range(" D1:D2000")
res = Application.Match(Range(ID_Location), VMM_Sig_Name_Rng.Offset(0, -2), 0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
msgbox rng1.Value & " Address: " & rng1.Address
End if

Offset(1, 15).Value = rng1 ????

I want to have two actions happen:
1. If there is an error I want to assign "SIGNAL ID NOT DEFINED" to the rng1.
2. Error or not, I want to insert the results into a relative address, which
depends on the present location.

Thanks

Match function and displaying results
 

Why does
set rng1 = rng(res) return the first value in the range and
ActiveCell.offset(1,15).Value = _
VMM_Sig_Name_Rng.Offset(res, -2).Resize(1,1).Value return the second?

How do I get the first?

Also please explain the resize function? Will this allow me to extract
multiple (rows/columns) from the range? i.e. return the first 5 rows and 3
columns?

Thanks

"Tom Ogilvy" wrote:

If there is an error there is no rng1 - and you can't assign a string value
to it.

if iserror(res) then
Activecell.offset(1,15).Value = "SIGNAL ID NOT DEFINED"
else
ActiveCell.offset(1,15).Value = _
VMM_Sig_Name_Rng.Offset(res, -2).Resize(1,1).Value
End sub

--
Regards,
Tom Ogilvy


"Dan" wrote:

I have the following modified code (provided by Tom Ogilvy and Bob Phillips):

Dim rng as Range, rng1 as Range
dim res as Variant
SetRng = VMM_Workbook.Worksheets(ShortVMM_FileName).Range(" D1:D2000")
res = Application.Match(Range(ID_Location), VMM_Sig_Name_Rng.Offset(0, -2), 0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
msgbox rng1.Value & " Address: " & rng1.Address
End if

Offset(1, 15).Value = rng1 ????

I want to have two actions happen:
1. If there is an error I want to assign "SIGNAL ID NOT DEFINED" to the rng1.
2. Error or not, I want to insert the results into a relative address, which
depends on the present location.

Thanks