selecting range of cells - variable # of rows

[miasmal]

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?

 

[Bob Phillips]

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)

 

[gmunro]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Tom Ogilvy]

"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.

 

[miasmal]

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.