Solved: Find string in the cell and then output value

[Ann_BBO]

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

[vba]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[/vba]

Thanks,
Ann

[geekgirlau]

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.

[vba]w = Mid(ActiveCell.Value, Instr(1, ActiveCell.Value, "Block") + 1)[/vba]

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]

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]

[vba]

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
[/vba]

[p45cal]

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:[vba]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
[/vba]

[Bob Phillips]

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]

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]

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]

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]

If you look at theexample in VBA help for find, it shows you excatly how to do that.

[Ann_BBO]

Is using "FindFirst" function?? If you don't mind, would you mind to give a simple example to me.

Thanks,
Ann

[Bob Phillips]

What Findfirst function are you referring to?