If I have a cell that contains a word in cell A10, but may change to A15
sometimes, what vba code can find the first cell in a column containing
'"myword"?

Do I need to use offset?

Hi Scott

Sub test()
Dim x As String, r As Range
x = "myword"
Set r = Sheets("Sheet1").Columns("A:A")
If Application.CountIf(r, x) 0 Then _
r.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole).Activate
End Sub

--
XL2002
Regards

William



"scott" wrote in message
...
| If I have a cell that contains a word in cell A10, but may change to A15
| sometimes, what vba code can find the first cell in a column containing
| '"myword"?
|
| Do I need to use offset?
|
|

This gives error below. What I'm tring to do is find the first cell that
contains 'myword', then select the rows from A1 to that cell minus 1 row.

Run-time error 91
object variable or with block variable not set


liam" wrote in message
...
Hi Scott

Sub test()
Dim x As String, r As Range
x = "myword"
Set r = Sheets("Sheet1").Columns("A:A")
If Application.CountIf(r, x) 0 Then _
r.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole).Activate
End Sub

--
XL2002
Regards

William



"scott" wrote in message
...
| If I have a cell that contains a word in cell A10, but may change to A15
| sometimes, what vba code can find the first cell in a column containing
| '"myword"?
|
| Do I need to use offset?
|
|

Hi Scott

I've just retested and it works for me. Do you have Sheet1 selected?
--
XL2002
Regards

William



"scott" wrote in message
...
| This gives error below. What I'm tring to do is find the first cell that
| contains 'myword', then select the rows from A1 to that cell minus 1 row.
|
| Run-time error 91
| object variable or with block variable not set
|
|
| liam" wrote in message
| ...
| Hi Scott
|
| Sub test()
| Dim x As String, r As Range
| x = "myword"
| Set r = Sheets("Sheet1").Columns("A:A")
| If Application.CountIf(r, x) 0 Then _
| r.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole).Activate
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "scott" wrote in message
| ...
| | If I have a cell that contains a word in cell A10, but may change to
A15
| | sometimes, what vba code can find the first cell in a column
containing
| | '"myword"?
| |
| | Do I need to use offset?
| |
| |
|
|
|
|

yes

"William" wrote in message
...
Hi Scott

I've just retested and it works for me. Do you have Sheet1 selected?
--
XL2002
Regards

William



"scott" wrote in message
...
| This gives error below. What I'm tring to do is find the first cell that
| contains 'myword', then select the rows from A1 to that cell minus 1
row.
|
| Run-time error 91
| object variable or with block variable not set
|
|
| liam" wrote in message
| ...
| Hi Scott
|
| Sub test()
| Dim x As String, r As Range
| x = "myword"
| Set r = Sheets("Sheet1").Columns("A:A")
| If Application.CountIf(r, x) 0 Then _
| r.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole).Activate
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "scott" wrote in message
| ...
| | If I have a cell that contains a word in cell A10, but may change to
A15
| | sometimes, what vba code can find the first cell in a column
containing
| | '"myword"?
| |
| | Do I need to use offset?
| |
| |
|
|
|
|

Hi Scott

I assumed Column A contains hard data - if "myword" is a formula
relating to another cell, you may need to amend the code to...

Sub test()
Dim x As String, r As Range
x = "myword"
Set r = Sheets("Sheet1").Columns("A:A")
If Application.CountIf(r, x) 0 Then _
r.Find(What:=x, LookIn:=xlValues, LookAt:=xlWhole).Activate
End Sub

--
XL2002
Regards

William



"scott" wrote in message
...
| yes
|
| "William" wrote in message
| ...
| Hi Scott
|
| I've just retested and it works for me. Do you have Sheet1 selected?
| --
| XL2002
| Regards
|
| William
|
|
|
| "scott" wrote in message
| ...
| | This gives error below. What I'm tring to do is find the first cell
that
| | contains 'myword', then select the rows from A1 to that cell minus 1
| row.
| |
| | Run-time error 91
| | object variable or with block variable not set
| |
| |
| | liam" wrote in message
| | ...
| | Hi Scott
| |
| | Sub test()
| | Dim x As String, r As Range
| | x = "myword"
| | Set r = Sheets("Sheet1").Columns("A:A")
| | If Application.CountIf(r, x) 0 Then _
| | r.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole).Activate
| | End Sub
| |
| | --
| | XL2002
| | Regards
| |
| | William
| |
| |
| |
| | "scott" wrote in message
| | ...
| | | If I have a cell that contains a word in cell A10, but may change
to
| A15
| | | sometimes, what vba code can find the first cell in a column
| containing
| | | '"myword"?
| | |
| | | Do I need to use offset?
| | |
| | |
| |
| |
| |
| |
|
|
|
|
|