Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrong results from MATCH function | Excel Worksheet Functions | |||
eval of concatenate function not displaying results | Excel Discussion (Misc queries) | |||
Displaying how many results in a vlookup | Excel Discussion (Misc queries) | |||
functions are not displaying the results | Excel Worksheet Functions | |||
Sorting and displaying different results | New Users to Excel |