Find start and end av a text in column A

haseft

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

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
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
 

haseft

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
What error did you get?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
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?
 

haseft

Active Member
Joined
Jun 10, 2014
Messages
276

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
In that case you need to change it so that only the blue part is changed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

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