• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array (Fast Read, Make Changes in array and Paste values Again)

AAP

AAP

Member
Hi Friends,
I am learning arrays. I have made a sample file from which I want to read price data to an array and do calculation within that array (for example: multiply by 2) and paste data back to its original place.
Also with first column (ProductGroup) would like to read it in an array do some string manupulation (for example: split each word with single space in between) and place back to its original place.
I tried but couldn't figure out.

Many thanks

Kind Regards

Amit
 

Attachments

  • ArrayLearn.xlsm
    67.3 KB · Views: 8
Hi !​
Code: 
Sub Demo1()
    Dim VA, R&, SP$(), S$
    With Sheet1.Cells(1).CurrentRegion.Columns(1)
       VA = .Value
    For R = 2 To UBound(VA)
                  SP = Split(VA(R, 1))
        If UBound(SP) > 0 Then
                   S = SP(0)
               SP(0) = SP(1)
               SP(1) = S
            VA(R, 1) = Join(SP)
        End If
    Next
        .Value = VA
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Hi Amit,

The arrays are 0 based or 1 based (Check Option Base statement). When you use it with ranges, VBA uses option base 1. Also the array has two dimensions. That was the reason why your code was not working. There are few ways to approach this.

Here we use Application.Transpose to change the dimension.
Code: 
Sub ArrayLearn1()
Dim Price() As Variant
Price = Application.Transpose(ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value)
Dim i As Long
For i = LBound(Price) To UBound(Price)
Price(i) = Price(i) * 2
Next i
ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value = Application.Transpose(Price)
End Sub

If we don't want to use Transpose (it has limitation of ~70,000 elements) then we have to explicitly access the element by its both dimensions.
Code: 
Sub ArrayLearn2()
Dim Price() As Variant
Price = ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value
Dim i As Long
For i = LBound(Price) To UBound(Price)
Price(i, 1) = Price(i, 1) * 2
Next i
ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value = Price
End Sub
 
shrivallabha said:
Hi Amit,

The arrays are 0 based or 1 based (Check Option Base statement). When you use it with ranges, VBA uses option base 1. Also the array has two dimensions. That was the reason why your code was not working. There are few ways to approach this.

Here we use Application.Transpose to change the dimension.
Code: 
Sub ArrayLearn1()
Dim Price() As Variant
Price = Application.Transpose(ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value)
Dim i As Long
For i = LBound(Price) To UBound(Price)
Price(i) = Price(i) * 2
Next i
ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value = Application.Transpose(Price)
End Sub

If we don't want to use Transpose (it has limitation of ~70,000 elements) then we have to explicitly access the element by its both dimensions.
Code: 
Sub ArrayLearn2()
Dim Price() As Variant
Price = ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value
Dim i As Long
For i = LBound(Price) To UBound(Price)
Price(i, 1) = Price(i, 1) * 2
Next i
ActiveSheet.Range(Cells(2, 2), Cells(4247, 2)).Value = Price
End Sub
Click to expand...

Beautifully described. Many many thanks :)
 
shrivallabha said:
Good to know. It is good to learn array usage. Good luck!
Click to expand...
Dear Shrivallabha,

Sorry to bother you again, If I use ArrayLearn2 then everything works fine upto 200000 rows (this may be because I am using Excel 2016). But, If I use ArrayLearn1 (with transpose) it paste back the data with errors in the last rows. Please run the ArrayLearn1 & ArraryLearn2 on the new file I am uploading and see where I am doing wrong.

Many thanks for your guidance.
 

Attachments

  • ArrayLearn.xlsm
    1,023.2 KB · Views: 6
You should get a run-time error. Transpose won't work with more than 65536 rows in your array.
 
It stops working precisely at 65536 elements and then if elements are increased it gives error, Run-time error '13'. There's no anything else as far as I know. This limitation of Transpose is known phenomenon.

The possible reason(I am not at all sure about this) for Transpose limit could be that it is more than enough for the current column limit (16384) of Application.

I am using Excel 2010. I cannot comment as to why this doesn't raise an error in Excel 2016.
 
shrivallabha said:
It stops working precisely at 65536 elements and then if elements are increased it gives error, Run-time error '13'. There's no anything else as far as I know. This limitation of Transpose is known phenomenon.

The possible reason(I am not at all sure about this) for Transpose limit could be that it is more than enough for the current column limit (16384) of Application.

I am using Excel 2010. I cannot comment as to why this doesn't raise an error in Excel 2016.
Click to expand...

This means I can use array without transpose method as long as it gives me output. About limitations not sure?
 
In Excel 2016 it seems that the code doesn't error, but for some reason the transposed array is truncated at 17511 elements (for me, the exact number seems a little random). Clearly a bug, and a bad one at that!
 
Hello Friends,
I think I understood array but seems not, because I got a problem again.
Could you please check the file I uploading now and give me suggestion what am I doing wrong.
Thanks a lot for your patience.
 

Attachments

  • ArrayTest.xlsb
    277.1 KB · Views: 4
Brand and Model are 1 dimensional arrays, and start with a lower bound of 2, so you must use Brand(x + 1) instead of Brand(x, 1) and the same with Model.
 
You need to be careful with ReDim part of array.

If you want to use Dimensions same as Range object then you need to make it two dimensional. The way you have written is applying for elements in first column.
Code: 
ReDim Brand(1 To Lrow, 1)
 
shrivallabha said:
You need to be careful with ReDim part of array.

If you want to use Dimensions same as Range object then you need to make it two dimensional. The way you have written is applying for elements in first column.
Code: 
ReDim Brand(1 To Lrow, 1)
Click to expand...

Its works! Many Thanks
But when I paste array back with this statement
Code: 
Sheet1.Range(Cells(2, 3), Cells(Lrow, 3)).Value = Brand
Sheet1.Range(Cells(2, 4), Cells(Lrow, 3)).Value = Model

This does not work
 
AAP said:
Its works! Many Thanks
But when I paste array back with this statement
Code: 
Sheet1.Range(Cells(2, 3), Cells(Lrow, 3)).Value = Brand
Sheet1.Range(Cells(2, 4), Cells(Lrow, 3)).Value = Model

This does not work
Click to expand...
I tried with OptionBase 1 Then it works.
Can you help me understand.
 
AAP said:
I tried with OptionBase 1 Then it works.
Can you help me understand.
Click to expand...
Base 0 = LBound(Array) = Count begins from 0
Base 1 = LBound(Array) = Count begins from 1
So when you define array size as 1 in default case it means two rows or two columns depending on the dimension. So after making it option base 1 the dimension matches correctly.

It is not really the dimension but the sizing the array when you are ReDim-ing. You are arriving LRow which is last row (138) and therefore Array will have 138 elements. At the end of code it will have 137 rows populated and one empty row. So your dimension reference has to be correct.

So following code will work.
Code: 
Option Explicit
Option Base 1
Sub LearnArray()
Dim BrandModel() As Variant
Dim Brand() As Variant
Dim Model() As Variant
Dim x, Lrow As Long

If WorksheetFunction.CountA(Cells) > 0 Then
Lrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

BrandModel = Sheet1.Range(Cells(2, 1), Cells(Lrow, 1)).Value

ReDim Brand(1 To UBound(BrandModel), 1) '// Notice it takes limit from
ReDim Model(1 To UBound(BrandModel), 1) '// BrandModel array size

For x = LBound(BrandModel) To UBound(BrandModel)
Brand(x, 1) = Left(BrandModel(x, 1), InStr(BrandModel(x, 1), " "))
Model(x, 1) = Replace(BrandModel(x, 1), Brand(x, 1), "")
Next x

Sheet1.Range(Cells(2, 3), Cells(Lrow, 3)).Value = Brand
Sheet1.Range(Cells(2, 4), Cells(Lrow, 4)).Value = Model

End Sub
 
  • Like
Reactions: AAP

Notice even with Option Base 1 some functions can return

a zero base array like Split for example …
​
 
shrivallabha said:
Base 0 = LBound(Array) = Count begins from 0
Base 1 = LBound(Array) = Count begins from 1
So when you define array size as 1 in default case it means two rows or two columns depending on the dimension. So after making it option base 1 the dimension matches correctly.

It is not really the dimension but the sizing the array when you are ReDim-ing. You are arriving LRow which is last row (138) and therefore Array will have 138 elements. At the end of code it will have 137 rows populated and one empty row. So your dimension reference has to be correct.

So following code will work.
Code: 
Option Explicit
Option Base 1
Sub LearnArray()
Dim BrandModel() As Variant
Dim Brand() As Variant
Dim Model() As Variant
Dim x, Lrow As Long

If WorksheetFunction.CountA(Cells) > 0 Then
Lrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

BrandModel = Sheet1.Range(Cells(2, 1), Cells(Lrow, 1)).Value

ReDim Brand(1 To UBound(BrandModel), 1) '// Notice it takes limit from
ReDim Model(1 To UBound(BrandModel), 1) '// BrandModel array size

For x = LBound(BrandModel) To UBound(BrandModel)
Brand(x, 1) = Left(BrandModel(x, 1), InStr(BrandModel(x, 1), " "))
Model(x, 1) = Replace(BrandModel(x, 1), Brand(x, 1), "")
Next x

Sheet1.Range(Cells(2, 3), Cells(Lrow, 3)).Value = Brand
Sheet1.Range(Cells(2, 4), Cells(Lrow, 4)).Value = Model

End Sub
Click to expand...
shrivallabha said:
Base 0 = LBound(Array) = Count begins from 0
Base 1 = LBound(Array) = Count begins from 1
So when you define array size as 1 in default case it means two rows or two columns depending on the dimension. So after making it option base 1 the dimension matches correctly.

It is not really the dimension but the sizing the array when you are ReDim-ing. You are arriving LRow which is last row (138) and therefore Array will have 138 elements. At the end of code it will have 137 rows populated and one empty row. So your dimension reference has to be correct.

So following code will work.
Code: 
Option Explicit
Option Base 1
Sub LearnArray()
Dim BrandModel() As Variant
Dim Brand() As Variant
Dim Model() As Variant
Dim x, Lrow As Long

If WorksheetFunction.CountA(Cells) > 0 Then
Lrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

BrandModel = Sheet1.Range(Cells(2, 1), Cells(Lrow, 1)).Value

ReDim Brand(1 To UBound(BrandModel), 1) '// Notice it takes limit from
ReDim Model(1 To UBound(BrandModel), 1) '// BrandModel array size

For x = LBound(BrandModel) To UBound(BrandModel)
Brand(x, 1) = Left(BrandModel(x, 1), InStr(BrandModel(x, 1), " "))
Model(x, 1) = Replace(BrandModel(x, 1), Brand(x, 1), "")
Next x

Sheet1.Range(Cells(2, 3), Cells(Lrow, 3)).Value = Brand
Sheet1.Range(Cells(2, 4), Cells(Lrow, 4)).Value = Model

End Sub
Click to expand...

I am very grateful to you. :)
 
One more question: I am scraping websites using VBA right now I am fetching elements from website and paste back in excel cells one by one. Sometimes data is so huge so that excel start to hang.
After learning array from you I see the clear advantage but from website I don't known beforehand how many rows will be required to complete my task. How can I solve this type of problem.
 

I never had this kind of issue 'cause number of rows is known …

Needs a fresh new thread with a crystal clear explanation !
​
 
You must log in or register to reply here.
Back
Top