VBA macro: Changing StrConv from static to variable length

[chbau]

Hello,
The following VBA macro has been written by someone who left our organization. This macro allows the automatic conversion of a cvs file to an xlsx file with many conditions.

Everything works great, but I want to modify one detail in the following line of code:


If w.ActiveSheet.Range("E" & i) Like "*BUSINESS TO BUSINESS ACH melio*" Then

dw.ActiveSheet.Range("F" & Rows.Count).End(xlUp).Offset(0, -4) = StrConv(Mid(w.ActiveSheet.Range("E" & i), WorksheetFunction.Search("Melio", w.ActiveSheet.Range("E" & i)) + 33, 15), vbProperCase)





The string I want to convert starts at the correct place (+33 blue in the code) and extracts the following 15 (15 orange in the code) characters.
Instead of 15 characters, I would like to extract a variable length of characters, starting at the same position (+33), but then extracting a variable length until the characters "Inv" appear.

Examples:
The cell "BUSINESS TO BUSINESS ACH melio Anthony Pe 220628 e5164719 Anthony Peter BakerInv #752" should be converted to "Anthony Peter Baker"
The cell "BUSINESS TO BUSINESS ACH melio Edward San 220628 e5129896 Edward SantoInv #753" should be converted to "Edward Santo"

I believe this is possible with a different condition for the string conversion, maybe using Len or InStr or Find.
I am not educated enough to know how to best edit for this result.
I would be grateful for any help here - Thank you!

 

[Zot]

I want to make line looks shorter. So, I created another 2 variable. So, modification should be like this (hope no syntax error )

Dim m As Long, n As Long
m = WorksheetFunction.Search("Melio", w.ActiveSheet.Range("E" & i)) + 33
n = InStr(1, w.ActiveSheet.Range("E" & i), "Inv", 0)
dw.ActiveSheet.Range("F" & Rows.Count).End(xlUp).Offset(0, -4) = StrConv(Mid(w.ActiveSheet.Range("E" & i), m, n - m), vbProperCase)

 

[chbau]

I want to make line looks shorter. So, I created another 2 variable. So, modification should be like this (hope no syntax error )

Dim m As Long, n As Long
m = WorksheetFunction.Search("Melio", w.ActiveSheet.Range("E" & i)) + 33
n = InStr(1, w.ActiveSheet.Range("E" & i), "Inv", 0)
dw.ActiveSheet.Range("F" & Rows.Count).End(xlUp).Offset(0, -4) = StrConv(Mid(w.ActiveSheet.Range("E" & i), m, n - m), vbProperCase)

It absolutely worked.
THANK YOU!!

 

[Zot]

It absolutely worked.
THANK YOU!!

Phew... Glad could help