0

Good morning,

Thanks in advance for any help you can give. I have written a code that uses a reference number in b6 on sheet "Y" to count the number of columns on sheet x based on that number and starts the formula 6 rows down. It then pastes a formula in in the proper cell in row six with the variable column.

Here is the code:

Sub testingme4()
Dim myvalue As Double, lastrow As Long
myvalue = Worksheets("x").Cells(6, 2).Value
Worksheets("y").Cells(6, (myvalue) + 21).Cells.FormulaR1C1 = "formula"
End Sub

This works perfectly for me.

However, when I try to get it to paste down the entire row, it gets wonky.

I added this: 

Dim lastrow as long
lastrow = Worksheets("y").Cells(Rows.Count, 1).End(xlUp).Row

Which also work and counts the correct number of rows (used 'MsgBox "Last row of data anywhere: " & lastrow & vbCrLf to verify)

The last part is that I need to loop the formula until it hits the last row of the column picked in my first statement.

I tried:

Dim paster as long,
For paster = Worksheets("x").Cells(6, (myvalue) + 21) To lastrow
Cells.FormulaR1C1 = "formula"
Next paster

This keeps giving me a running out of memory error.

Thanks again for any help.

EP

E.Pyles
  • 9
  • 5
  • 1
    paster should be an `integer` and the Syntax for a `For..To` only needs `Next` without the additional `paster` – Tom K. Jun 17 '16 at 13:45
  • As @tom said, you need integer values for `paster`. Then within the loop you need `Workshssts("y").Cells(paster, 1) = Worksheet("x").Cells(6, myvalue + 21)`. – lonestorm Jun 17 '16 at 14:02
  • @Tom Aren't integers ["deprecated"](http://www.ozgrid.com/forum/showthread.php?t=79584) in Excel [nowadays?](http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) Isn't a Long better? Related to question itself, why don't you try an auto fill instead of looping through every cell? And why don't you try .Formula instead of R1C1, R1C1 has always been a pain for me. – Sgdva Jun 17 '16 at 14:14
  • 1
    @Sgdva of course, you are absolutely right. (also see this answer http://stackoverflow.com/a/10558880/4583203 ) My comment was a bit misleading. I meant that paster cant be a string and has to be a numeric value. Not necessarily an integer. – Tom K. Jun 17 '16 at 14:30

1 Answers1

0

Didn't test this, but it should work.

Sub testingme4()
Dim myvalue As Double, lastrow As Long
Dim paster As String

lastrow = Worksheets("y").Cells(Rows.Count, 1).End(xlUp).Row
myvalue = Worksheets("x").Cells(6, 2).Value
paster = "formula"

'This starts at row 6 and loops until it hits the last row
For i = 6 To lastrow
    Worksheets("y").Cells(i, (myvalue) + 21).Cells = paster
Next

'I dont really know what ".FormulaR1C1" is supposed to accomplish so I didn't include it.

End Sub

Feel free to ask further questions.

HTH

Tom K.
  • 1,020
  • 1
  • 12
  • 28
  • Thanks Tom . The only change I made was to change "formula" in the for next loop to paster. Outstanding! – E.Pyles Jun 17 '16 at 15:02