Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable from .find
I'm using the following to search for text in a cell and then assign
that cell to variable. later I select the entire row. Dim lclRow As Range With ActiveSheet.UsedRange.Cells Set lclRow = .Find(What:="LCL") End With I want to do three things but am screwing up the procedure. 1. condense the selection to one line. i see no need to use the With statement in this case. 2.Just search the first column Ive tried a few version of the following to no avail: Set lclRow = ActiveSheet.Columns(1).Cells.Find (What:="LCL") Set lclRow = Range("A:A").Find(What:="LCL") both return nothing 3.would like to set lclRow to the entire column in the same statement if possible. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable from .find
You are very close. one thing to note is with Find you want to specify the
necessary arguments. If not the last vales as set by the user will be used. MatchCase, LookIn and LookAt could cause you a problem... To select the entire row will require a few statements as you need to deal with the case where the search text is not found... Dim lclRow As Range Set lclRow = ActiveSheet.Columns(1).Find(what:="LCL", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) If Not lclRow Is Nothing Then Set lclRow = lclRow.EntireRow End If -- HTH... Jim Thomlinson "Robert H" wrote: I'm using the following to search for text in a cell and then assign that cell to variable. later I select the entire row. Dim lclRow As Range With ActiveSheet.UsedRange.Cells Set lclRow = .Find(What:="LCL") End With I want to do three things but am screwing up the procedure. 1. condense the selection to one line. i see no need to use the With statement in this case. 2.Just search the first column Ive tried a few version of the following to no avail: Set lclRow = ActiveSheet.Columns(1).Cells.Find (What:="LCL") Set lclRow = Range("A:A").Find(What:="LCL") both return nothing 3.would like to set lclRow to the entire column in the same statement if possible. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable from .find
Robert,
try adapting something like the below: Dim rngFound As Range On Error Resume Next With Worksheets("Sheet1").Range("A:A") Set rngFound = .Find(What:="LCL"), After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If rngFound.Value < "" then msgbox "Found!" end if end with Corey.... "Robert H" wrote in message ... I'm using the following to search for text in a cell and then assign that cell to variable. later I select the entire row. Dim lclRow As Range With ActiveSheet.UsedRange.Cells Set lclRow = .Find(What:="LCL") End With I want to do three things but am screwing up the procedure. 1. condense the selection to one line. i see no need to use the With statement in this case. 2.Just search the first column Ive tried a few version of the following to no avail: Set lclRow = ActiveSheet.Columns(1).Cells.Find (What:="LCL") Set lclRow = Range("A:A").Find(What:="LCL") both return nothing 3.would like to set lclRow to the entire column in the same statement if possible. thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable from .find
Thanks for responding Jim, but I end up with the same problem. lclRow
= nothing BTW cell A46 contains the text "LCL" without the quotes. thanks again Robert On Jan 7, 5:38*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: You are very close. one thing to note is with Find you want to specify the necessary arguments. If not the last vales as set by the user will be used. MatchCase, LookIn and LookAt could cause you a problem... To select the entire row will require a few statements as you need to deal with the case where the search text is not found... Dim lclRow As Range Set lclRow = ActiveSheet.Columns(1).Find(what:="LCL", _ * * * * * * * * * * * * * * * * * * * * *LookAt:=xlWhole, _ * * * * * * * * * * * * * * * * * * * * *LookIn:=xlFormulas, _ * * * * * * * * * * * * * * * * * * * * *MatchCase:=False) If Not lclRow Is Nothing Then *Set lclRow = lclRow.EntireRow End If -- HTH... Jim Thomlinson "Robert H" wrote: I'm using the following to search for text in a cell and then assign that cell to variable. later I select the entire row. Dim lclRow As Range With ActiveSheet.UsedRange.Cells * * * * * * Set lclRow = .Find(What:="LCL") * * End With I want to do three things but am screwing up the procedure. 1. condense the selection to one line. i see no need to use the With statement in this case. 2.Just search the first column Ive tried a few version of the following to no avail: * * * * * * Set lclRow = ActiveSheet.Columns(1).Cells.Find (What:="LCL") * * * * * * Set lclRow = Range("A:A").Find(What:="LCL") both return nothing 3.would like to set lclRow to the entire column in the same statement if possible. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Find | Excel Discussion (Misc queries) | |||
Find Variable | Excel Programming | |||
Find using a variable value | Excel Programming | |||
Find with a variable | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |