LinkBack URL
About LinkBacksIf 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?
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?
Here's one way (it includes code in BOLD to display a dialog with the cell address where it is found, then selects the cell itself when the user clicks OK in the dialog):
Sub findSomething()
With ActiveSheet.UsedRange.Cells
Set c = .Find("myword", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
MsgBox "myword was found in cell: " & firstAddress & "."
ActiveSheet.Range(firstAddress).Select
Else
MsgBox "No match was found."
End If
End With
End Sub
Hope this helps,
theDude
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
[email protected]
"scott" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> [email protected]
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
> | 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
[email protected]
"scott" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| news:%[email protected]...
| > 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
| >
| > [email protected]
| >
| > "scott" <[email protected]> wrote in message
| > news:[email protected]...
| > | 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" <[email protected]> wrote in message
news:[email protected]...
> Hi Scott
>
> I've just retested and it works for me. Do you have Sheet1 selected?
> --
> XL2002
> Regards
>
> William
>
> [email protected]
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | news:%[email protected]...
> | > 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
> | >
> | > [email protected]
> | >
> | > "scott" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | 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
[email protected]
"scott" <[email protected]> wrote in message
news:[email protected]...
| yes
|
| "William" <[email protected]> wrote in message
| news:[email protected]...
| > Hi Scott
| >
| > I've just retested and it works for me. Do you have Sheet1 selected?
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > [email protected]
| >
| > "scott" <[email protected]> wrote in message
| > news:[email protected]...
| > | 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" <[email protected]> wrote in message
| > | news:%[email protected]...
| > | > 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
| > | >
| > | > [email protected]
| > | >
| > | > "scott" <[email protected]> wrote in message
| > | > news:[email protected]...
| > | > | 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?
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
| >
|
|
i'll try that. thanks.
"William" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> [email protected]
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
> | yes
> |
> | "William" <[email protected]> wrote in message
> | news:[email protected]...
> | > Hi Scott
> | >
> | > I've just retested and it works for me. Do you have Sheet1 selected?
> | > --
> | > XL2002
> | > Regards
> | >
> | > William
> | >
> | > [email protected]
> | >
> | > "scott" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | 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" <[email protected]> wrote in message
> | > | news:%[email protected]...
> | > | > 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
> | > | >
> | > | > [email protected]
> | > | >
> | > | > "scott" <[email protected]> wrote in message
> | > | > news:[email protected]...
> | > | > | 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?
> | > | > |
> | > | > |
> | > | >
> | > | >
> | > |
> | > |
> | >
> | >
> | >
> |
> |
>
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks