Translating excel formula to VBA format with match() and left()

[dcass]

Hello! I have this formula in Excel that finds the cell with the http based link. I have to use the left() language because the links in our spreadsheets are often greater than the 250 characters that match can handle. Here's the formula in Excel: =MATCH("htt*", LEFT(B1:B30, 200), 0), which is returning the correct value. I need to incorporate this into VBA and assign the value to a variable.

This was my attempt to write that line: Var4 = Application.Match("htt*", Left(Range("B1:B30").Value, 200), 0)
But I get an error mismatch dialog. Can someone point me to what i'm doing incorrectly here? Basically I don't know how to nest the Left() function into the match function that i'm using in VBA. Thank you!

 

[mumps]

Try:

Sub test()
    Dim var4 As Range
    Set var4 = Range("B1:B30").Find("htt", LookIn:=xlValues, lookat:=xlPart)
    MsgBox (var4 & " is found in cell " & var4.Address(0, 0))
End Sub

 

[dcass]

That worked, thanks so much!

 

[mumps]

You are very welcome.