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

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

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

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