My scenario is to get run a regex pattern that will search across the Sheet1 (all cells) in my excel file. What I'm want as output is the cell number where that pattern is matching in my Sheet2 within same excel file.
With help from lots of questions of stackOverflow and with their answers I managed got how to store result in next sheet. But how should I run my regEx so that it will give result with better performance (my pattern generally will be a string). Code I'm trying now is below :
Sub Sample()
Dim ws As Worksheet
Dim aCell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
lastrow = Range("B" & Rows.Count).End(xlUp).Row
With ws
For i = 1 To lastrow
If InStr(Range("B" & i), "Custom ") > 0 Then
Worksheets("Sheet2").Range("A" & Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1).Value = Range("B" & i).Address
End If
Next i
End With
End Sub
Asked
Active
Viewed 450 times
0
Ralph
- 9,284
- 4
- 32
- 42
Govind Mantri
- 803
- 1
- 11
- 24
-
You should should state what type of pattern you are looking for as there may be a way that does not require an RE. To use an RE see [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) – Alex K. Apr 19 '16 at 15:44
-
@AlexK.Thanks !! I have checked that link... in above code this Range is set for single column I want to search in entire sheet.. if there is any better way to achieve then pls suggest.. my RE is user defined so it can be very simple to complex. – Govind Mantri Apr 19 '16 at 15:49