CODEWITHSUNDEEP
vbaexcelfor-loop
Iuli
Iuli

Reputation: 167

Copy a cell for x times in other cells

I have some information in the following cells: H3,H4 and H5. Exactly: H3 contains letter a, H4 contains b and H5 contains c. Now I would like to put the content of each these cells as follows: 

C11=a,C12=a,C13=a,C14=a,C15=a,C16=a. 

C17=b,C18=b,C19=b,C20=b,C21=b,C22=b. 

C23=b, C24=c,C25=c,C26=c,C27=c,C28=c.

It can be observe that we have a step of 6.

I have tried the following code:

For s = 0 To 17
Cells(s + 11, 3).Value = Cells(CInt(3 + (s / 6)), 8)
Next

My problem is, that the cells are not filled in a correct way and also C26, C27, C28 remain empty.

Thanks!

Upvotes: 0

Views: 63

Answers (3)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can achieve same result without VBA.

In cell C11 put following formula and copy down to get the results you need

=INDEX($H$3:$H$5,CEILING(ROWS($C$11:C11)/6,1))

Upvotes: 0

WNG
WNG

Reputation: 3805

CInt

performs a rounding and not a truncating of the integer

you should use Int

For s = 0 To 17
Cells(s + 11, 3).Value = Cells(Int(3 + (s / 6)), 8)
Next

Upvotes: 1

Vityata
Vityata

Reputation: 43575

Run it like this:

Public Sub TestMe()

    Dim s   As Long

    For s = 0 To 17
        Debug.Print Cells(s + 11, 3).Address
        Debug.Print Cells(CInt(3 + (s / 6)), 8).Address
        Cells(s + 11, 3).Value = Cells(CInt(3 + (s / 6)), 8)
    Next s

End Sub

Then press Ctrl+G and see the printed addresses in the immediate window.

Upvotes: 1

Related Questions