Autofill Rows with formula

[karlos]

Ok I know I'm pushing my luck! One Guru has already solved one problem.

How to I create a Macro that will paste "=sheet2!C1" in cell A1 (of sheet1) then "=sheet2!C2" in B1 then "=sheet2!C3" in C1 upto Y1 and then do it all over again for row 2 then row 3 etc.

Have just over a 1000 lines with 25 columns to fill in

Can anyone help -

[david000]

Try Copy > PasteSpecial > Paste Link

[karlos]

paste special does not increment like autofill won't when dragiing the product formula to the right across the row.

All I want to do is copy data in column C1 through to C4000 to a linear format ie across the row on another sheet of the same book. So that C1 is A1 on the other sheet. To do this each cell in a row (A to Y)needs the formula =sheet!C1 but the last numerial needs to increment by one, C1 to C25 and then effectivly do a line feed and start again on row 2 C26 to C51 and again and again until all data in sheet1 column C has been copied.

I though a macro might make the process a bit quicker? thanks for the input.

[Bob Phillips]

Public Sub Test()
Dim i As Long

For i = 1 To 1000

Cells(i, "A").Resize(, 25).Formula = "=Sheet2!C" & (i - 1) * 25 + 1
Next i
End Sub

[karlos]

Thanks for the code, the code seems to do the same as autofill to a point in that it increments the column value form C to D to E and not the last digit the row value 1 through to 25, however if I put the $ before the C in your code it fixes the C but does not increment along the row until it starts the next row and starts C26. Is there a way around this?

And thanks again

[Bob Phillips]

Brute force



Public Sub Test()
Dim i As Long
Dim j As Long

For i = 1 To 1000

For j = 1 To 25

Cells(i, j).Resize(, 25).Formula = "=Sheet2!$C" & (i - 1) * 25 + j
Next j
Next i
End Sub

[karlos]

Thanks XLD

the routine works - but only runs to row 220 where its seems to halt with the error Visual basic - " Application-defined or Object-defined error" Aslo the last formula in column Y is copied upto column AW- through rows 1 to 220.

Again many thanks

[david000]

I'm not 100% sure, but I think you can remove the Resize argument.
To:
Cells(i, j).Formula = "=Sheet2!$C" & (i - 1) * 25 + j

[mdmackillop]

Without looping
Sub FiiData()
Range("A1:Y1000").Formula = "=INDIRECT(" & """" & "Sheet2!C" & """" & "& (ROW()-1) * 25+COLUMN())"
End Sub

[Bob Phillips]

Without looping
Sub FiiData()
Range("A1:Y1000").Formula = "=INDIRECT(" & """" & "Sheet2!C" & """" & "& (ROW()-1) * 25+COLUMN())"
End Sub


25,000 INDIRECT formulae! Put the kettle on, loads of time.

[mdmackillop]

Timer returns 0.34375
Not too shabby!

[Bob Phillips]

Not poulating MD, recalc. INDIRECT is volatile, so every change will recalculate all 25,000 cells.

[mdmackillop]

I've populated C1:C65536 with RAND(). Changing a value on Sheet1 does recalculate, but in fact there is no undue delay. Even extending to row 2621 to use all 65k random numbers works OK.

For the OP, I'd probably go for the looping solution myself, to get the neater formula. :thumb

[karlos]

Hi back in front of a PC, Iv'e a attached the rough spread sheet of what it is I trying to do, move all the data in column C on sheet "equipment" to across rows on sheet 1. The data in Equipment!C will run possbilly to 25000 so that means there would be 1000 rows with columns A to Y on sheet1 populated with the formula to bring in the data from "equipment!C"

Not sure if you wanted the sample?

And thanks again one and all

[karlos]

Hi,

if I run the macro in smaller chunks

Public Sub Test()
Dim i As Long
Dim j As Long

For i = 1 To 220

For j = 1 To 25

Cells(i, j).Formula = "=Sheet2!$C" & (i - 1) * 25 + j
Next j
Next i
End Sub

ie only put formula into rows 1 to 220 then run again 221 to 450 it runs perferctly a lot faster than typing in the formula cell by cell. Is there any way of modifing the macro so it starts to look a specific row first like start a row 4 or row 8 then carry on to the end of the data?

Thanks again

[mdmackillop]

How about

Public Sub Test()
Dim i As Long
Dim j As Long
j = inputbox("Enter start value: 0 to exit")
if j = 0 then exit sub
For i = j to j + 200

For j = 1 To 25

Cells(i, j).Formula = "=Sheet2!$C" & (i - 1) * 25 + j
Next j
Next i
Call Test
End Sub

[karlos]

Thanks MD,

the routine needs to know where the start row is on sheet 2 ie start on C2 or C6 or any row number in Column C but the numbering should start from that point so if the first data was in C11 the first formula on sheet1 A1 would start "=sheet2!$C11" counting up to the next 24 (25 all together) until the Y column of the same row ending with "=sheet2$C35"

the next row on sheet1 would the carry on the next 25 starting with "=sheet2$C36"

Hope the above makes sense its been a long day.

Many thanks

[karlos]

Hi, the following works - very very simple but hopefully small steps will lead to bigger things.

Public Sub Equipment()
Dim i As Long
Dim j As Long
Dim o As Long
o = InputBox("Enter start value: 0 to exit")
If o = 0 Then Exit Sub

For i = 1 To 200

For j = 1 To 25

Cells(i, j).Formula = "=Equipment!$C" & (i - 1) * 25 + j + o

Next j
Next i
End Sub

I think I know why the following does not work - is there a simple work around?

Cells(i, j).Formula = "=Equipment!$B&Equipment!$D" & (i - 1) * 25 + j + o