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
 
iI dont get i t realy,
how?

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, xlbyrow, xlPrevious, False, , False).Row
End With
yes, I changed it,
the result of batStartRow is row 3 witch is correct,
but the reult of batEndRow is 0 (zero), witch is wrong, it skoul be row 3
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
There's an s missing from the end of xlbyrow
 
Upvote 0
There's an s missing from the end of xlbyrow
thansk Fluff, ?(y)
working great,

this massage appears meny time,
Nu I want to mark that I recived answer to my question.
I can,t fine it how.
1605367404290.png
 
Upvote 0
You're welcome & thanks for the feedback.

What is it that you can't find?
 
Upvote 0
Solution

Forum statistics

Threads
1,212,927
Messages
6,110,727
Members
448,294
Latest member
jmjmjmjmjmjm

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