How to fix the Run-time error "13": Type mismatch? 1

[feipezi]

Hi,
I'm running a extremely simple procedure but it gave me the error and I have no idea what Excel wants. Here goes the code.

Sub testing()
Dim DmnIt As Variant
Set DmnIt = Range("a10:f10").Value
For i = 1 To UBound(DmnIt, 1)
MsgBox i
Next
End Sub

It's a one-row range trying to read in as an Array. Error found on the 3rd row: the Set statement.
BTW, the values in DmnIt is "a, b, c, d, e, f".
Thanks in advance.
John Z.

 

[DaveInIowa]

Remove Set when assigning to your Variant. Set would be okay if you were assigning the Range itself, but not its .Value.

 

[SoCalAccessPro]

Try this :

Sub testing()
Dim DmnIt  As Variant
DmnIt = Range("A1:A6").Value

For i = 1 To UBound(DmnIt)
    Debug.Print DmnIt(i, 1)
Next i
End Sub

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[feipezi]

Hi folks,
Thanks for the input! But I did not make myself clear. What I want is a single row range (and I don't want to use Transpose unless it's necessary). Range("a1:a6") is a column range.

Dave,
I tried this following what you told me:

Sub testing()
Dim DmnIt As Variant
DmnIt = Range("a30:f30")
For i = 1 To UBound(DmnIt, 1)
If DmnIt(30, i) = "e" Then MsgBox i
Next
End Sub

But I got Run-time error: '9' indicating Excel won't recognize it's a row-range.

Of course, I can do a For loop:

...
For Each c in Range("a30:f30")
If c = "e" Then ...
Next
...

I'm sure it will work but if I have about 200 columns to search with more manipulation, Array should be more efficient. That's the whole idea.
Thanks again for your efforts.
John Z.

 

[SoCalAccessPro]

If DmnIt(30, i) = "e" Then MsgBox i

Your array doesn't have 30 dimensions.....

Have you looked into Hlookup and Dlookup instead ?

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SkipVought]

Sub testing()
    Dim DmnIt As Variant, i, j
    DmnIt = Selection
    For i = 1 To UBound(DmnIt, 2)
        For j = 1 To UBound(DmnIt, 1)
            If DmnIt(j, i) = 4 Then MsgBox i
        Next
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,
This is exactly what I want.
One last question. Is that safe if I do the following? I tested it and it worked too: just trying to get rid of one Loop.

Sub testing()
Dim DmnIt As Variant, i
DmnIt = range("a30:f30")
For i = 1 To UBound(DmnIt, 2)
If DmnIt(1, i) = "e" Then MsgBox i
Next
End Sub

Thanks a lot.
John Z.

 

[SkipVought]

Breath easy!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}