Array problem...

daniel50096230

Board Regular
Joined
May 2, 2008
Messages
163
I would like to use array v to store all the results of the for loop u...How can Ido it?

Code:
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
AnsRange1 = myRange.Row
Dim u As Integer
Dim v As Integer
Dim var() As Single
v = 0
For u = 23 To 24022 Step 9
 var(v) = u
Next u
If Not (AnsRange1 = v) Then
MsgBox AnsRange1
Else
Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
End If
End If
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Is this what you mean

Code:
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
    Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
    AnsRange1 = myRange.Row
    Dim u As Integer
    Dim v As Integer
    Dim var As Variant
    ReDim var(1 To 1000)
    v = 0
    For u = 23 To 24022 Step 9
        v = v + 1
        var(v) = u
    Next u
    ReDim Preserve var(1 To v)
    If Not (AnsRange1 = v) Then
        MsgBox AnsRange1
    Else
        Rows(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
    End If
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
I mis-read the size

Code:
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
    Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
    AnsRange1 = myRange.Row
    Dim u As Integer
    Dim v As Integer
    Dim var As Variant
    ReDim var(1 To 10000)
    v = 0
    For u = 23 To 24022 Step 9
        v = v + 1
        var(v) = u
    Next u
    ReDim Preserve var(1 To v)
    If Not (AnsRange1 = v) Then
        MsgBox AnsRange1
    Else
        Rows(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
    End If
 

daniel50096230

Board Regular
Joined
May 2, 2008
Messages
163

ADVERTISEMENT

Thanks....there is no more error,but if the record match, it will not proceed to the Else statement "Rows(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown".....

It will just keep on prompt out the messagebox although I match the record.
 

daniel50096230

Board Regular
Joined
May 2, 2008
Messages
163
I noticed that your code is add on all the results of the for loop...But I just want to use an array to store all the result of for loop one by one and not adding the number of the results...
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378

ADVERTISEMENT

Sorry, I don't understand what you are saying.
 

daniel50096230

Board Regular
Joined
May 2, 2008
Messages
163
For example,in the for loop,all the results such as 32,41,50.....all will be sum it....But what I want is use an array to store all this 32,41,50....and not sum it...
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
It is not a sum, it is the row numbers, you are stepping through them 9 at a time, so it starts at your first row and increments by 9.
 

daniel50096230

Board Regular
Joined
May 2, 2008
Messages
163
But I had try to click F8 to trace it,the v is keep on increase where it is sum all the results of for loop....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top