Hello,

I am trying to figure out a VB script for Excel macro in order to do
the following:
- determine row # where a particular word exists - make this target_row
- select range of cells from a specific cell (say B2000) to column L &
target_row

example:

1 1 1 1 1
2 2 2 2 2
3 3 x 3 z
4 4 4 4 4
5 5 5 5 5
6 y 6 6 6

determine row where "x" exists - row # 3
select range from y to z - B6 <- E3

y - row/column known
x,z - column known


I think I am having more trouble with the code used to select the range
rather than finding the row?

Do you mean

Range("B2000:L" & found_row).Select

where found_row is the row you found stored in a variable


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Hello,

I am trying to figure out a VB script for Excel macro in order to do
the following:
- determine row # where a particular word exists - make this target_row
- select range of cells from a specific cell (say B2000) to column L &
target_row

example:

1 1 1 1 1
2 2 2 2 2
3 3 x 3 z
4 4 4 4 4
5 5 5 5 5
6 y 6 6 6

determine row where "x" exists - row # 3
select range from y to z - B6 <- E3

y - row/column known
x,z - column known


I think I am having more trouble with the code used to select the range
rather than finding the row?

I am having a similar problem based on finding the value of an
inputbox, where in the above example ine input box entry might be "X3Z"

Any help greatly appreciated

Sub FindSomething()
dim rng as Range
dim res as String
res = InputBox("Enter string to find")
if res < "" then
set rng = cell.Find(What:=res, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Activate
end if
End if
End Sub

Change xlPart to xlWhole if you want to match the entire value of the cell.
Change xlFormulas to xlValue if you want to find something produced by a
formula.

--
Regards,
Tom Ogilvy


"gmunro" wrote in message
oups.com...
I am having a similar problem based on finding the value of an
inputbox, where in the above example ine input box entry might be "X3Z"

Any help greatly appreciated

"cell" should be "cells"

Sub FindSomething()
dim rng as Range
dim res as String
res = InputBox("Enter string to find")
if res < "" then
set rng = cells.Find(What:=res, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Activate
end if
End if
End Sub

Change xlPart to xlWhole if you want to match the entire value of the cell.
Change xlFormulas to xlValue if you want to find something produced by a
formula.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Sub FindSomething()
dim rng as Range
dim res as String
res = InputBox("Enter string to find")
if res < "" then
set rng = cell.Find(What:=res, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Activate
end if
End if
End Sub

Change xlPart to xlWhole if you want to match the entire value of the
cell.
Change xlFormulas to xlValue if you want to find something produced by a
formula.

--
Regards,
Tom Ogilvy


"gmunro" wrote in message
oups.com...
I am having a similar problem based on finding the value of an
inputbox, where in the above example ine input box entry might be "X3Z"

Any help greatly appreciated

Find "X" (Only one value?)


LookupValue = "X"
With Worksheets(1).Range("a1:z500")
Set c = .Find(LookupValue, LookIn:=xlValues)
If Not c Is Nothing Then
xRow=C.row
End If
End With


Select Range:

Dim Rng as range

Set rng=range(Cells(xrow,zcol),Cells(yrow,ycol))
rng.select

HTH

" wrote:

Hello,

I am trying to figure out a VB script for Excel macro in order to do
the following:
- determine row # where a particular word exists - make this target_row
- select range of cells from a specific cell (say B2000) to column L &
target_row

example:

1 1 1 1 1
2 2 2 2 2
3 3 x 3 z
4 4 4 4 4
5 5 5 5 5
6 y 6 6 6

determine row where "x" exists - row # 3
select range from y to z - B6 <- E3

y - row/column known
x,z - column known


I think I am having more trouble with the code used to select the range
rather than finding the row?

Toppers wrote:
.....
Select Range:

Dim Rng as range

Set rng=range(Cells(xrow,zcol),Cells(yrow,ycol))
rng.select


Thank you, your method works. Can the 2 arguments be flipped in rng?
I was thinking backwards for my specific problem... I'll play with it.


HTH

" wrote:

Hello,

I am trying to figure out a VB script for Excel macro in order to
do
the following:
- determine row # where a particular word exists - make this
target_row
- select range of cells from a specific cell (say B2000) to column
L &
target_row

example:

1 1 1 1 1
2 2 2 2 2
3 3 x 3 z
4 4 4 4 4
5 5 5 5 5
6 y 6 6 6