Find start and end av a text in column A

haseft

Active Member
Joined
Jun 10, 2014
Messages
278
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
 

haseft

Active Member
Joined
Jun 10, 2014
Messages
278
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Do you get any errors?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
There's an s missing from the end of xlbyrow
 

haseft

Active Member
Joined
Jun 10, 2014
Messages
278

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

What is it that you can't find?
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,277
Messages
5,635,252
Members
416,849
Latest member
truerock

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
Top