Looping X number

[hatstand]

Hi, I am trying to get the code below to loop down X number of times. With maxi being a dynamic range name. It copies down cell A2 and adds 1 each time. But to the length maxi declares. If i use a number it works but not if I declare it.

Can someone tell me what I'm doing wrong please.

Sub for_demo()

Dim Add1 As Integer
Dim maxi As Variant

For Add1 = 1 To maxi Step 1

ActiveCell.Value = Range("A2").Value + 1
ActiveCell.Offset(1, 0).Select

Next

End Sub

 

[Weaver]

Where abouts is maxi getting a value from?

If it's a named range in a sheet then

For Add1 = 1 To range("maxi").value Step 1

and lose the 'dim' reference

 

[Yahya]

what is your code doing
Try this
Sub for_demo()

Dim Add1 As Integer
Dim maxi As Integer
maxi = InputBox("Insert Number")
For Add1 = 1 To maxi Step 1

Range("A" & Add1).Value = ActiveCell.Value

Next

End Sub

 

[hatstand]

'maxi' is a dynamic range formula: =OFFSET(Sheet1!$C$1,0,0,COUNT(Sheet2!$C:$C),1)

It gives me a count of entries into column C.

 

[hatstand]

My code copies cell 'A2' value, then adds 1 then moves down 1 cell. My rpblem is that I want' maxi' my dynamic range formula to decide how many times to copy down.

 

[Weaver]

If you're using code anyway, why not count the number of entries in column C in VB?

or maybe, since maxi is defining a range, you could replace the whole thing with this?

for each c in range("maxi").cells
c.value=range("A2")+1
next c

 

[Yahya]

Try this code , may can solve your Problem

Sub Test()
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    Cells(i, 1).Value = Cells(i - 1, 1) + 1
Next i
End Sub

 

[Trebor76]

Here's a slightly different way to do it in one swoop:

With Range("maxi")
        .Formula = "=ROW()-1"
        .Value = .Value
    End With

Regards,

Robert

 

[hatstand]

Hi,

Sorry to both of you. I can't get either to work. My fault I know, as I'm still learning the VBA thing.

 

[mirabeau]

Maybe this one?

Sub hatstand()
Range("A1") = 17 'or whatever
maxi = 6    'or whatever
Range("A1").Resize(maxi).DataSeries
End Sub