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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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....
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,384
Messages
5,836,940
Members
430,463
Latest member
mikmob

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