Get cell Value then offset by that number

Question

Hello All 

I am trying to do the following 

Get excell to read cell data then offset  to the right by that amount

then enter that number and offset again untill the number has been reached

the last thing is to loop through until the end of the worksheet

my data looks like this

as an example 

A1 = 25 

move to A26 then enter 25 then move to A51 then enter 25 continue this until it has been done by the values in column A

I have variables in column A 

thanks in advance

Answer 1

Try this macro:

Sub FillSheet()
    Dim r As Long
    Dim m As Long
    Dim i As Long
    Dim n As Long
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 1 To m
        n = Val(Cells(r, 1).Value)
        For i = 1 To n
            Cells(r, i * n + 1) = n
        Next i
    Next r
End Sub

Test on a copy of the worksheet. You may want to use

        For i = 1 To n - 1

---

Regards, Hans Vogelaar

Answer 2

hello Hans,

Thank you very much for the quick reply as describe it worked perfectly

My data starts at column AK and row6 

What do i change to make it work for this point?

thanks for the help

Answer 3

Column AK is the 37th column, so try

Sub FillSheet()
    Const StartRow = 6
    Const StartCol = 37
    Dim r As Long
    Dim m As Long
    Dim i As Long
    Dim n As Long
    m = Cells(Rows.Count, StartCol).End(xlUp).Row
    For r = StartRow To m
        n = Val(Cells(r, StartCol).Value)
        For i = 1 To n
            Cells(r, i * n + StartCol) = n
        Next i
    Next r
End Sub

---

Regards, Hans Vogelaar

Answer 4

hello Hans,

the second code works as expected 

my problem now seems that i want the code to run until a max number of columns of 450

And i need the numbers to populate up to that amount

so if startcol = 37 and the value is 5 i need to code to not repeat 5 times but to repeat 90 times

seems what the bosses wanted is not what they have told us

thanks for the help

regard

Phil

 



Answer 5

So what's new... ;-)

Change the line        For i = 1 To n

to

       For i = 1 To Int(450 / n)

or to

       For i = 1 To 450 \ n

---

Regards, Hans Vogelaar