Prev: Advanced Filtering top values
Next: Execute a variable as an insertion into a VBA code line
From: mju on 15 Apr 2010 17:03
I am having problems with my code. It used to work fine until today. I am
getting type mismatch error in f = Array("STY", "PK", "COLOR", "SZ", "ITEM",
"UP", "STORE")

What am I doing wrong!!

Sample Code

L = 0
L = ActiveCell.SpecialCells(xlLastCell).Row
Dim f() As String

f = Array("STY", "PK", "COLOR", "SZ", "ITEM", "UP", "STORE")
Dim FR(7) As Variant
Dim x As Integer
Dim y As Integer
For x = 10 To 37 ' cycles through columns 10 to 37
For y = 1 To 7 ' cycles through all 7 of the items in the "F" array
' Checks the header to see if it is looking at the particular item from the
"F" array.
' Then assigns whatever column number that item is in. e.g. if the STYLE is
in colume 23 then FR(1) = 23.
If CELLS(1, x) = f(y) Then FR(y) = CELLS(1, x).Column
Next y
Next x

Thanks

From: Rick Rothstein on 15 Apr 2010 17:14
I am assuming you do NOT have an Option Base 1 statement, so your arrays are
defaulted to being zero-based; hence, this line is wrong...

For y = 1 To 7

It should be this...

For y = 0 To 6

because the Array function will automatically produce a zero-based array.
Also, just so you know, this line...

Dim FR(7) As Variant

creates an array of 8 elements with index numbers of 0,1,2,3,4,5,6,7. If you
don't use Option Base 1, then you should probably change that statement to
this...

Dim FR(6) As Variant

--
Rick (MVP - Excel)



"mju" <mju(a)discussions.microsoft.com> wrote in message
news:1752C9B6-33B9-49F7-B8F0-40E464077B13(a)microsoft.com...
> I am having problems with my code. It used to work fine until today. I am
> getting type mismatch error in f = Array("STY", "PK", "COLOR", "SZ",
> "ITEM",
> "UP", "STORE")
>
> What am I doing wrong!!
>
> Sample Code
>
> L = 0
> L = ActiveCell.SpecialCells(xlLastCell).Row
> Dim f() As String
>
> f = Array("STY", "PK", "COLOR", "SZ", "ITEM", "UP", "STORE")
> Dim FR(7) As Variant
> Dim x As Integer
> Dim y As Integer
> For x = 10 To 37 ' cycles through columns 10 to 37
> For y = 1 To 7 ' cycles through all 7 of the items in the "F" array
> ' Checks the header to see if it is looking at the particular item from
> the
> "F" array.
> ' Then assigns whatever column number that item is in. e.g. if the STYLE
> is
> in colume 23 then FR(1) = 23.
> If CELLS(1, x) = f(y) Then FR(y) = CELLS(1, x).Column
> Next y
> Next x
>
> Thanks
>
 | 
Pages: 1
Prev: Advanced Filtering top values
Next: Execute a variable as an insertion into a VBA code line