PDA

View Full Version : Error code 438



duck_crossin
08-31-2013, 01:58 AM
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
08-31-2013, 02:49 AM
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
08-31-2013, 03:33 AM
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
08-31-2013, 04:23 AM
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
08-31-2013, 04:28 AM
use VBA instead of Excelfunctions


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

Aflatoon
09-02-2013, 02:30 AM
There is no Worksheetfunction.Mid because VBA already has a Mid function.

p45cal
09-02-2013, 03:31 AM
There is no Worksheetfunction.Mid because VBA already has a Mid function.ha, ha! I didn't even notice that!

SamT
09-02-2013, 04:48 AM
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
09-02-2013, 05:50 AM
@SamT

In that case:


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

SamT
09-02-2013, 06:13 AM
@snb

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

snb
09-02-2013, 06:54 AM
@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