array data type issues -

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

Matthew Dyer

Sub Macro1()
Dim txt As String
Dim newsheet As Worksheet
Dim destination As Range
Dim MyArr
Dim TempArr
Dim TempVal As String
MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 0 To UBound(TempArr)
TempVal = Application.Index(TempArr, i + 1)
'where error occurs TempVal = Mid(TempVal, 11, 3)
Cells(i + 1, 1).Value = TempVal
Next i
Set newsheet = ActiveWorkbook.Worksheets.Add
Columns(2).NumberFormat = "@"
Set destination = newsheet.Range("a1")
destination.Resize(UBound(MyArr, 1), UBound(MyArr, 2)).Value = MyArr
Set destination = newsheet.Range("k1")
destination.Resize(UBound(TempArr, 1), UBound(TempArr, 2)).Value = TempArr
End Sub

so i'm trying to use arrays to do data manipulation and i am running into problems. i've directly tried to declair tempvar as string to do text data manipluation, integer/long/etc to do number manipluation, and nothing seems to work. help please :-)
 
Sub Macro1()
Dim txt As String
Dim newsheet As Worksheet
Dim destination As Range
Dim MyArr
Dim TempArr
Dim TempVal As String
MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 0 To UBound(TempArr)
TempVal = Application.Index(TempArr, i + 1)
'where error occurs TempVal = Mid(TempVal, 11, 3)
Cells(i + 1, 1).Value = TempVal
Next i
Set newsheet = ActiveWorkbook.Worksheets.Add
Columns(2).NumberFormat = "@"
Set destination = newsheet.Range("a1")
destination.Resize(UBound(MyArr, 1), UBound(MyArr, 2)).Value = MyArr
Set destination = newsheet.Range("k1")
destination.Resize(UBound(TempArr, 1), UBound(TempArr, 2)).Value =
TempArr End Sub

so i'm trying to use arrays to do data manipulation and i am running
into problems. i've directly tried to declair tempvar as string to do
text data manipluation, integer/long/etc to do number manipluation,
and nothing seems to work. help please :-)
Click to expand...

Ok.., lets start with the basics so you can revise your code
appropriately...

MyArr is a 1-based 2D array!
TempArr is also a 1-based 2D array.

...then post back your error findings.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ok.., lets start with the basics so you can revise your code
appropriately...

MyArr is a 1-based 2D array!
TempArr is also a 1-based 2D array.

..then post back your error findings.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

MyArr is the 'start data' - purposefully soft-coded as i want to learn how to use as much flexible code as possible. on the specific example i'm working on it's a 8 column x 1000+row data set, with the first row being header row. i believe that yes, this means it is a 1 based 2d array with various data-types (dates, strings, currencies, numbers as string, and plain old numbers)

TempArr is what i'm using to pull the column from MyArr that I'd like to manipulate, so it will always only be one column, so yes another 1 based 2d array.

In this case, i've set TempArr as the 2nd column in the array which holds account numbers which are 17 digits long.

I know that i've been able to load both MyArr and TempArr correctly with the proper data from the destination snippets of code below - the informationgoes into the sheet without a hitch. I also know TempVal pulls the correctdata from the line right below it. those cells get the correct data.

the specific error i'm getting with the commented line - run-time error 13 - type mismatch
 
the specific error i'm getting with the commented line - run-time
error 13 - type mismatch
Click to expand...

Data in a variant array is classed as type Variant. Trying to pass this
data to a String variable causes a type mismatch because VBA expects a
String NOT a Variant.

Change the String var to Variant. Let VBA resolve the type according to
the elements...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Data in a variant array is classed as type Variant. Trying to pass this
data to a String variable causes a type mismatch because VBA expects a
String NOT a Variant.

Change the String var to Variant. Let VBA resolve the type according to
the elements...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

Dim MyArr
Dim TempArr
Dim TempVal As Variant
MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 0 To UBound(TempArr)
TempVal = Application.Index(TempArr, i + 1)
TempVal = Mid(TempVal, 11, 3)
Cells(i + 1, 1).Value = TempVal
Next i

still getting error at TempVal = Mid(....
 
Hi Matthew,

Am Tue, 9 Dec 2014 07:00:08 -0800 (PST) schrieb Matthew Dyer:
TempVal = Application.Index(TempArr, i + 1)
TempVal = Mid(TempVal, 11, 3)
Click to expand...

if you want to write data into an array you have to declare it with
brackets and dimensions or declare it only with brackets and Redim the
array into the code:

Sub Macro1()
Dim newsheet As Worksheet
Dim destination As Range
Dim MyArr, TempArr, TempVal()
Dim i As Long, n As Long

MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 1 To UBound(TempArr)
ReDim Preserve TempVal(UBound(TempArr) - 1)
TempVal(n) = Mid(TempArr(i, 1), 11, 3)
n = n + 1
Next i

Range("A1").Resize(UBound(TempVal) + 1) = _
Application.Transpose(TempVal)

Set newsheet = ActiveWorkbook.Worksheets.Add
Columns(2).NumberFormat = "@"
Set destination = newsheet.Range("a1")
destination.Resize(UBound(MyArr, 1), UBound(MyArr, 2)).Value = MyArr
Set destination = newsheet.Range("k1")
destination.Resize(UBound(TempArr), 1).Value = TempArr
End Sub


Regards
Claus B.
 
Hi Matthew,

Am Tue, 9 Dec 2014 07:00:08 -0800 (PST) schrieb Matthew Dyer:


if you want to write data into an array you have to declare it with
brackets and dimensions or declare it only with brackets and Redim the
array into the code:

Sub Macro1()
Dim newsheet As Worksheet
Dim destination As Range
Dim MyArr, TempArr, TempVal()
Dim i As Long, n As Long

MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 1 To UBound(TempArr)
ReDim Preserve TempVal(UBound(TempArr) - 1)
TempVal(n) = Mid(TempArr(i, 1), 11, 3)
n = n + 1
Next i

Range("A1").Resize(UBound(TempVal) + 1) = _
Application.Transpose(TempVal)

Set newsheet = ActiveWorkbook.Worksheets.Add
Columns(2).NumberFormat = "@"
Set destination = newsheet.Range("a1")
destination.Resize(UBound(MyArr, 1), UBound(MyArr, 2)).Value = MyArr
Set destination = newsheet.Range("k1")
destination.Resize(UBound(TempArr), 1).Value = TempArr
End Sub


Regards
Claus B.
Click to expand...

so, what i'm gathering is TempVal cannot be an independent variable/data type, it needs to be treated as a 1x1 array?
 
Hi Matthew,

Am Tue, 9 Dec 2014 07:34:46 -0800 (PST) schrieb Matthew Dyer:
so, what i'm gathering is TempVal cannot be an independent variable/data type, it needs to be treated as a 1x1 array?
Click to expand...

you can also make TempVal a 2D-Array:

For i = 1 To UBound(TempArr)
ReDim Preserve TempVal(UBound(TempArr) - 1, 0)
TempVal(n, 0) = Mid(TempArr(i, 1), 11, 3)
n = n + 1
Next i

Range("A1").Resize(UBound(TempVal) + 1) = TempVal


Regards
Claus B.
 
Dim MyArr
Dim TempArr
Dim TempVal As Variant
MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 0 To UBound(TempArr)
Click to expand...

TempArr is 1-based so why are you starting your counter at zero? Better
to use...

For i = LBound(TempArr) To UBound(TempArr)
TempVal = Application.Index(TempArr, i + 1)
Click to expand...
This line requires both dims because TempArr is same as UBound(MyArr)
rows-wise, with 1 col for 2nd dim! This throws the error for the next
line...

TempVal = TempArr(i, 1)

...since your counter now starts at 1 (LBound(TempArr))!
TempVal = Mid(TempVal, 11, 3)
Cells(i + 1, 1).Value = TempVal
Next i

still getting error at TempVal = Mid(....
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
typo...
This line requires both dims because TempArr is same as UBound(MyArr)
rows-wise, with 1 col for 2nd dim! This throws the error for the next
Click to expand...

line, and so try...
TempVal = TempArr(i, 1)

..since your counter now starts at 1 (LBound(TempArr))!
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
typo...


line, and so try...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

the TempVal = Application.Index(TempArr, i + 1) line was my problem. I don't know why i was trying to access the element in wanted via index in an array - i am trying to convert to arrays to i can use array syntax to make life easier/faster. DUH! haha. thanks for the help.
 
the TempVal = Application.Index(TempArr, i + 1) line was my problem.
I don't know why i was trying to access the element in wanted via
index in an array - i am trying to convert to arrays to i can use
array syntax to make life easier/faster. DUH! haha. thanks for the
help.
Click to expand...

Glad to help where I can!

I was focussed on your statement from the other post...

"so i'm trying to teach myself how to do data manipulations in arrays
instead of in the excel cells directly"

...where I also posted a download link to some samples I'm working on.
(They are still considered 'work-in-progress' as yet!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top