Find start and end av a text in column A

haseft

Active Member
Joined
Jun 10, 2014
Messages
321
Hi,
I am trying to find start and end of a text in column A,
The text I am searching is WERW
The data in column A is: (in this ex. A2:A5)
WEBBTV
WERW
WERWER
WERWER

Its not working.
batStartRow giv result row 3
batEndRow giv result row 5

Both start and end should be row 3

How to solve this?

Rich (BB code):
Sub Findmytext()

Dim batStartRow As Long
Dim batEndRow As Long
Dim mytext As String
mytext = UserForm16.ListBox1.Value ' in this ex. the value of selected item is WERW
'ListBox1 has single row

With Sheets("Sheet2")
  batStartRow = .Range("A:A").Find(What:=mytext, After:=.Range("A1")).Row ' find first match
  batEndRow = .Range("A:A").Find(What:=mytext, After:=.Range("A1"), SearchDirection:=xlPrevious).Row ' fiknd last match
End With

End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
When using find you should specify all relevant arguments to prevent incorrect results like
VBA Code:
Range("A:A").Find(mytext, , , xlWhole, xlByRows, xlNext, False, , False).Row
Range("A:A").Find(mytext, , , xlWhole, xlbyrow, xlPrevious, False, , False).Row
 
Upvote 0
When using find you should specify all relevant arguments to prevent incorrect results like
VBA Code:
Range("A:A").Find(mytext, , , xlWhole, xlByRows, xlNext, False, , False).Row
Range("A:A").Find(mytext, , , xlWhole, xlbyrow, xlPrevious, False, , False).Row

thansk, Fluff,
i get error, it highlight ".Row" at the end of the first cod
 
Upvote 0
Did you replace all of this
Rich (BB code):
  batStartRow = .Range("A:A").Find(What:=mytext, After:=.Range("A1")).Row ' find first match
  batEndRow = .Range("A:A").Find(What:=mytext, After:=.Range("A1"), SearchDirection:=xlPrevious).Row ' fiknd last match
or just the bits in blue?
 
Upvote 0
Did you replace all of this
Rich (BB code):
  batStartRow = .Range("A:A").Find(What:=mytext, After:=.Range("A1")).Row ' find first match
  batEndRow = .Range("A:A").Find(What:=mytext, After:=.Range("A1"), SearchDirection:=xlPrevious).Row ' fiknd last match
or just the bits in blue?
yes I did,
VBA Code:
With Sheets("Sheet2")
  Range("A:A").Find(mytext, , , xlWhole, xlByRows, xlNext, False, , False).Row
  Range("A:A").Find(mytext, , , xlWhole, xlbyrow, xlPrevious, False, , False).Row
End With
 
Upvote 0
In that case you need to change it so that only the blue part is changed.
 
Upvote 0
Like
VBA Code:
With Sheets("Sheet2")
  batStartRow = .Range("A:A").Find(mytext, , , xlWhole, xlByRows, xlNext, False, , False).Row
  batEndRow = .Range("A:A").Find(mytext, , , xlWhole, xlByRows, xlPrevious, False, , False).Row
End With
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top