Error code 438

[duck_crossin]

I am getting the error code '438' object does not support this property or method. I am trying to use the Mid function a Worksheet Function, but I don't think that is the Problem. The error code occurs on the StringA = WorksheetFunction line. Any insight would be approciated!


For Each rCell In rRng.Cells
'stringA = Empty ' Set stringA to empty for error handling
'On Error Resume Next 'Proceeds to next line of code if error occurs
h1 = WorksheetFunction.Search("-", rCell.Value, 1)
x1 = WorksheetFunction.Search("X", rCell.Value, 1)
x2 = WorksheetFunction.Search("X", rCell.Value, x1 + 1)
stringA = WorksheetFunction.Mid(rCell.Value, h1 + 1, x1 - h1)
stringB = WorksheetFunction.Mid(rCell.Value, x1 + 1, x2 - x1) 'HSS16X16X5/8, HSS9X9X3/8, HSS5-1/2X5-1/2X3/8
'On Error GoTo 0 'Default error-handling option, stops code and asks user to debug or end
If stringA = stringB And stringA <> Empty Then

[p45cal]

If the string is not found you'll get this error. If you use
h1 = Application.Search("-", rCell.Value, 1)
instead, you'll get the error code into h1 instead (as long as you haven't Dimmed h1 as some sort of a number (if you are Dimming the variable just Dim h1 without the As xxxx bit after)) which you can handle.
Beware of using On Error Resume Next as when an error occurs h1 will simply retain the value it had on the previous loop iteration (of course, you can circumvent that by including an h1=0 before the h1=... within the loop.

[duck_crossin]

Thanks for looking at my code. h1 is getting error code 2015 'Type Mismatch', but the debugger is still highlighting the stringA=... line. Does h1 need to be Dimmed as a double to run through this Application search?

[p45cal]

When h1 contains an error code, it's not a number, so when you try to use it for arithmetic:
stringA = WorksheetFunction.Mid(rCell.Value, h1 + 1, x1 - h1)
it will fail. You need to handle this.
Perhaps with a:
If IsNumeric(h1) And IsNumeric(x1) And IsNumeric(x1) Then
'..all the code here which does things with h1,x1 and x2
end if

[snb]

use VBA instead of Excelfunctions


For Each rCell In rRng
stringB=split(split(rcell.value,"-")(0),"X")(1)
next

[Aflatoon]

There is no Worksheetfunction.Mid because VBA already has a Mid function.

[p45cal]

There is no Worksheetfunction.Mid because VBA already has a Mid function.ha, ha! I didn't even notice that!

[SamT]

For Each rCell In rRng.Cells
With rCell 'HSS16X16X5/8, HSS9X9X3/8, HSS5-1/2X5-1/2X3/8
Dash1 = InStr(.Text, "-") '=31
Dash2 = InStr(Dash1, .Text, "-") '=37
x1 = InStr(Dash1, .Text, "X") '= 35
StrLen = x1 - Dash1

If Dash1 * Dash2 * x1 = 0 Then Failure

stringA = Mid(.Text, Dash1, StrLen) '= "1/2"
stringB = Mid(.Text, Dash2, StrLen) '= "1/2"

If stringA = stringB And stringA <> Empty Then

[snb]

@SamT

In that case:


For Each rCell In rRng
stringB=split(split(rcell.value,"-")(1),"X")(0)
Next

[SamT]

@snb

?
For Each rcell In rRng
stringA = Split(Split(rcell.Value, "-")(0), "X")(0)
stringB = Split(Split(rcell.Value, "-")(1), "X")(0)
Next

[snb]

@SamT

I think

if the result from 'HSS16X16X5/8, HSS9X9X3/8, HSS5-1/2X5-1/5X3/8
should be stringA: 1/2 ; stringB: 1/5


For Each rcell In rRng
stringA = Split(Split(rcell.Value, "-")(1), "X")(0)
stringB = Split(Split(rcell.Value, "-")(2), "X")(0)
Next