Get cell Value then offset by that number

# Get cell Value then offset by that number

• 2012년 3월 13일 화요일 오전 7:54

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

### 모든 응답

• 2012년 3월 13일 화요일 오전 8:08

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

• 2012년 3월 13일 화요일 오전 8:43

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

• 2012년 3월 13일 화요일 오전 9:08

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

• 2012년 3월 15일 목요일 오전 8:34

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

﻿

• 2012년 3월 15일 목요일 오전 10:11

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