selecting range of cells - variable # of rows
 

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?

selecting range of cells - variable # of rows
 

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?

selecting range of cells - variable # of rows
 

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

selecting range of cells - variable # of rows
 

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

selecting range of cells - variable # of rows
 

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?

selecting range of cells - variable # of rows
 

"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

selecting range of cells - variable # of rows
 

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