View Full Version : Solved: Find string in the cell and then output value
Ann_BBO
10-21-2009, 06:52 PM
Hi All,
How to write a simple marco to find a string "Block" in cell and then output the string after "Block".
If Range("A1") = Block 0xA: sys_param_hdr_init
or
Range("A1") = HS Block A. CP parameters
then output w=0A
Sub Marco()
Dim w as string
With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
Cells.Find(What:="Block", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate
w = Application.ActiveCell.Value
End Sub
Thanks,
Ann
geekgirlau
10-21-2009, 08:55 PM
Hi Ann,
I'm not understanding your logic here.
The first part is simple. Once you locate the word "Block", you can extract everything after that word.
w = Mid(ActiveCell.Value, Instr(1, ActiveCell.Value, "Block") + 1)
However I'm not seeing how
Block 0xA: sys_param_hdr_init
OR
HS Block A. CP parameters
is supposed to translate to 0A. Are you wanting to capture a certain number of characters after the word "Block"? Is there some other consistent character that will appear in the text that will act as the end point?
Ann_BBO
10-21-2009, 11:43 PM
Hi geekgirlau,
Thanks for your help. My difficulties are that there is no consistent character that will appear in the text that will acts as the end point. Lucky, it only has 2 types.
One type is Block 0x?:sys_param_hdr_init
where ? = 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,11,12,….FF
(i.e. extract characters between "x" and ":")
One type is HS Block ?. CP parameters
where ? = 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,11,12,….FF
(i.e. extract characters between " " and ".")
Example
If ? = 0; W=00
If ? = 1; W=01.
If ? = 11; W=11
Is it possible to solve it.
Thanks,
Ann
Bob Phillips
10-22-2009, 01:12 AM
Sub Marco()
Dim w As String
Dim pos1 As Long
Dim pos2 As Long
With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
Cells.Find(What:="Block", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate
w = Application.ActiveCell.Value
pos1 = InStr(w, "Block ")
If pos1 > 0 Then
pos2 = InStr(pos1, w, ":")
If pos2 > 0 Then
w = Format(Val(Replace(Mid$(w, pos1 + 6, pos2 - pos1 - 6), "x", "")), "00")
Else
pos2 = InStr(pos1, w, ".")
If pos2 > 0 Then
w = Format(Val(Mid$(w, pos1 + 6, pos2 - pos1 - 6)), "00")
End If
End If
End If
End Sub
p45cal
10-22-2009, 02:39 AM
I think there might be a problem when Val encounters letters. You could prefix the result with '&H' and Val will convert to decimal:
Format(Val("&H" & Mid$(w, pos1 + 6, pos2 - pos1 - 6)), "00")
Of course, there's more than one way to crack a nut:w = Application.ActiveCell.Value
pos1 = InStr(w, "Block 0x")
If pos1 > 0 Then
pos1 = pos1 + 8
pos2 = InStr(pos1, w, ":")
Else
pos1 = InStr(w, "Block ")
pos1 = pos1 + 6
pos2 = InStr(pos1, w, ".")
End If
If pos1 > 0 And pos2 > 0 Then
w = Mid(w, pos1, pos2 - pos1)
If Len(w) = 1 Then w = "0" & w
Else
w = "Expected characters after 'Block' not found"
End If
MsgBox w
Bob Phillips
10-22-2009, 03:12 AM
I think there might be a problem when Val encounters letters.
Ann said the data would be of two types, so there should be no problem.
p45cal
10-22-2009, 03:45 AM
The values can be anything from 0 to FF.
Using her examples, Val("A") returns 0
She was expecting '0A', while your code returns '00'.
Ann_BBO
10-22-2009, 07:33 PM
Hi xld & p45cal
Thanks for both help! It is good teaching to me. p45cal, you are right. Your code is suitable for my situation. Thanks.
Regards,
Ann
Ann_BBO
10-26-2009, 07:46 AM
One more question:
Since i use the "Find" function to find the "Block" string with Bold function. Can VBA will announce the finding is completed when the finding is back to 1st result (redundancy)? Or can i use the array function to store the previous finding result to compare the current one? Note that the Block 0A, 0B.. is unique.
Thanks&Regards,
Ann
Bob Phillips
10-26-2009, 07:52 AM
If you look at theexample in VBA help for find, it shows you excatly how to do that.
Ann_BBO
10-26-2009, 07:02 PM
Is using "FindFirst" function?? If you don't mind, would you mind to give a simple example to me.
Thanks,
Ann
Bob Phillips
10-27-2009, 01:22 AM
What Findfirst function are you referring to?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.