Printable View
Hi, all
how do i convert the formula like "=MID(D5,FIND("E",D5)+1,5)" to using in my macros below :
[VBA]LR = Range("C" & Rows.Count).End(xlUp).Row
For R = 1 To LR
If Cells(R, 4).Value Like "*" & "STATUS CODE" & "*" Then
Sheet2.Cells(IndexI, 4).Value =" the mid and fing formula "
End If
Next R
[/VBA]
actually loop the wording is "STATUS CODE 92 * DADS# 06 * " but i just only want the code "92"
experts kindly help and learn me more thxx
Greetings KK1966,
I'm not sure, but if I am grasping the pattern you want to match, maybe something like:
In a Standard Module:
[VBA]Option Explicit
Sub example()
Dim REX As Object
Dim rngSource As Range
Dim rngDest As Range
Dim arr_vntValues() As Variant
Dim lDestRow As Long
Dim n As Long
'// Find last cell in source column; presumes a header row in row 1. //
Set rngSource = RangeFound(Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Sheet1.Rows.Count, 1)))
'// If no data, bail... //
If rngSource Is Nothing Then
MsgBox "No values"
Exit Sub
End If
'// If data, reset range from row 2 to last row w/data. //
Set rngSource = Sheet1.Range(Sheet1.Cells(2, 1), rngSource)
'// Return first row with no data in destination. //
Set rngDest = RangeFound(Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(Sheet2.Rows.Count, 1)))
If rngDest Is Nothing Then
lDestRow = 2
Else
lDestRow = rngDest.Row + 1
End If
'// Just in case only one row of data, so no hiccup whne we go to loop the array. //
If rngSource.Rows.Count = 1 Then
ReDim arr_vntValues(1 To 1, 1 To 1)
arr_vntValues(1, 1) = rngSource.Value
Else
arr_vntValues = rngSource.Value
End If
Set REX = CreateObject("VBScript.RegExp")
With REX
'// Presuming only one return per cell //
.Global = False
.IgnoreCase = False
'// Create pattern as desired. This one is looking for one match made of two //
'// sub-expressions. The first is "STATUS', followed by zero to two spaces, //
'// followed by "CODE", followed by zero to two spaces. The second sub- //
'// expression is 1 to many digits. If we Match, then we just want the second //
'// sub-expression returned. //
.Pattern = "(STATUS {0,2}CODE {0,2})(\d+)"
For n = 1 To UBound(arr_vntValues, 1)
'// Test first, so that .Execute doesn't botch if no Match. //
If .Test(arr_vntValues(n, 1)) Then
'//If success, return the value. I tacked in including which row it was//
'// found in. //
Sheet2.Cells(lDestRow, 1).Value = _
"In Row: " & n + 1 & _
" I found: " & .Execute(arr_vntValues(n, 1))(0).SubMatches(1)
lDestRow = lDestRow + 1
End If
Next
End With
End Sub
Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function[/VBA]
By the way, where is Anguilla?
Hope that helps,
Mark
perhaps...
[vba]
Sub FindFirstNumericInString()
Dim LR As Long, i As Long
Dim strTest
LR = Range("C" & Rows.Count).End(xlUp).Row
For R = 1 To LR
IndexI = IndexI + 1 'change the position of this line to your requirement
If InStr(1, Cells(R, 4), "STATUS CODE") > 0 Then
strTest = Split(Cells(R, 4), " ")
For i = LBound(strTest) To UBound(strTest)
If IsNumeric(strTest(i)) Then
Sheet2.Cells(IndexI, 4).Value = strTest(i)
Exit For
End If
Next i
End If
Next R
End Sub
[/vba]
i assume you want to extract the first occurence of a number in the string.
IndexI = IndexI + 1 counter makes the code write walues to corresponding rows in Sheet2. so if this is not the case, its place must be changed.
Quote:
Originally Posted by mancubus
yeah ..... thxx its learn me much .thxx
you are welcome...
or ??
[VBA]Sub M_snb()
For each cl in columns(3).specialcells(2)
If InStr(cl.offset(,1), "STATUS CODE") Then cl.offset(,1)=Val(split(cl.offset(,1),"Status code")(1))
Next
End Sub[/VBA]
@GTO.
Re: By the way, where is Anguilla?
Part of the British Virgin Islands if I am not mistaken.
:hi: Thank you :-)Quote:
Originally Posted by jolivanes
I had a moment to look it up. Extremely gorgeous beaches!