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 :-)

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

On Monday, December 8, 2014 8:27:29 PM UTC-7, GS wrote:
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

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 information goes into the sheet without a hitch. I also know TempVal pulls the correct data 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

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

On Tuesday, December 9, 2014 7:42:29 AM UTC-7, GS wrote:
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

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)

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Tuesday, December 9, 2014 7:42:29 AM UTC-7, GS wrote:
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

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