Paste into active cell, + Rows below

[kcroft88]

Hi,

Im looking for some support for a VBA macro to paste a formula into the active cell + a number of rows bellow.
I would like the following formula to paste in the active cell...

=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4),0)

i would like this copied down the next 32 rows.
Then the same formula pasted into rows 40 - 63 (still in the column of the active cell.)
Then the same formula again pasted into rows 75 - 88 (still in the column of the active cell.)

Is it also possible once it has done this to then select all the cells we just pasted this formula into and copy and paste them into the same cell to remove the formula? leaving just the value the formula returned? I require this as this data will be populated on a daily basis and by copying and pasting to remove the formula reduces the constant calculations of formulas and wont slow the sheet down.

I hope that makes sense and someone is able to support

thank you,

 

[mumps]

Try:

Sub CopyFormula()
    ActiveCell.Resize(33, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
    Cells(40, ActiveCell.Column).Resize(24, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
    Cells(75, ActiveCell.Column).Resize(14, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
    Columns(ActiveCell.Column).SpecialCells(xlCellTypeFormulas).Value = Columns(ActiveCell.Column).SpecialCells(xlCellTypeFormulas).Value
End Sub

 

[kcroft88]

Hi thanks for the reply. the code you provided just returns 0 in every cell stated? however if i manually add the formula to each cell it returns the data required?

 

[Fluff]

Will you have other formulae in the column that you want to keep?

 

[mumps]

I tried a macro on a dummy sheet and it worked properly. It copies your formula to the ranges you requested and then the last line of the macro copies and pastes the values to remove the formulas. After adding the formula manually, click on any cell in the column and run only the last line of the macro. See what happens.

 

[kcroft88]

hi, no other formulas i want to keep.

i have tidied the sheet and require the formula stated in the following:
rows 3 - up to and including row 35
rows 40 - up to and including row 65
rows 70 - up to and including row 95
rows 100 - up to and including row 124

The copy and paste at the end is simply to remove the formula but keep values. as there are so many cells calculating. keeping formulas in was crashing my sheet :/

Thanks

 

[Fluff]

@mumps
If you have non-contiguous ranges then the method you used will convert to values incorrectly values. The second range will be filled with the 1st value of the 1st range.

 

[Fluff]

Try changing the last line of Mumps code to

Columns(ActiveCell.column).Value = Columns(ActiveCell.column).Value

 

[mumps]

With Fluff's suggestion:

Sub CopyFormula()
    Cells(3, ActiveCell.Column).Resize(33, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
    Cells(40, ActiveCell.Column).Resize(26, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
    Cells(70, ActiveCell.Column).Resize(26, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
    Cells(100, ActiveCell.Column).Resize(25, 1).Formula = "=IFERROR(INDEX(QueryReport!$A:$J,MATCH(1,(QueryReport!$A:$A=$B3)*(QueryReport!$C:$C=$A$3)*(QueryReport!$J:$J=G$2),0),4), 0)"
   Columns(ActiveCell.Column).Value = Columns(ActiveCell.Column).Value
End Sub

 

[mumps]

@Fluff: Thank you for that information. I wasn't aware of that.