array data type issues -

[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

 

[GS]

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

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

 

[Matthew Dyer]

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

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

 

[GS]

the specific error i'm getting with the commented line - run-time

error 13 - type mismatch

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

 

[Matthew Dyer]

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

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(....

 

[Claus Busch]

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)

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.

 

[Matthew Dyer]

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.

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

 

[Claus Busch]

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?

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.

 

[GS]

Dim MyArr
Dim TempArr
Dim TempVal As Variant
MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 0 To UBound(TempArr)

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)

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(....

--
Garry

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

 

[GS]

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

line, and so try...

TempVal = TempArr(i, 1)

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

--
Garry

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

 

[Matthew Dyer]

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

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.

 

[GS]

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.

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