VBA Paste Formula

[Claret]

Hello,

I have a spreadsheet set up to take a cleansed report which I need to apply formula to.

The report size could change (i.e. the number of rows used will not be fixed, but the columns will).

The report is pasted into cell E1 and covers columns E to F.

In columns A, B, C and D - I would like to add the formula shown below so that the formula in each column is extended to the last row containing data in column E. The report itself is currently over 15,000 rows but it could be significantly bigger than this, so I'd like to copy the formula and paste as values, once they have been added to keep the size of the spreadsheet to a mimimum.


Any help with this would be much appreciated as anything that I've tried so far has resulted in Excel freezing (presumably because I don't know the most efficient way to write this code).

Thanks

David

' Column A formula
ActiveCell.FormulaR1C1 = "=IF(RC[4]=""Agent:"",RC[5],R[-1]C)"

' Column B Formula
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(RC5:RC[3],""open the call appropriately"",RC1:RC[-1],RC[-1])"

' Column C Formula
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[2])"
' Column D Formula
ActiveCell.FormulaR1C1 = _
"=IF(IF(ISERR(LEFT(RC[3],1)*1),"""",VALUE(LEFT(RC[3],1)))=0,1,"""")"

 

[Vinoth_Shob]

Function past_formula()
Dim k As Long
Dim lastrow As Long
lastrow = Sheet3.Range("E" & Rows.Count).End(xlUp).Row


For k = 1 To lastrow
Sheet3.Range("A" & k).FormulaR1C1 = "=IF(RC[4]=""Agent:"",RC[5],R[-1]C)"


Sheet3.Range("B" & k).FormulaR1C1 = _
"=COUNTIFS(RC5:RC[3],""open the call appropriately"",RC1:RC[-1],RC[-1])"


Sheet3.Range("C" & k).FormulaR1C1 = "=CONCATENATE(RC[-2],RC[2])"
Sheet3.Range("D" & k).FormulaR1C1 = _
"=IF(IF(ISERR(LEFT(RC[3],1)*1),"""",VALUE(LEFT(RC[3],1)))=0,1,"""")"


Next
End Function

 

[Fluff]

Untested, but try

Sub PasteFormulaValues()

   Dim UdsRws As Long
   UsdRws = Range("E" & Rows.Count).End(xlUp).row
   
   With Range("A2:A" & UsdRws)
      .FormulaR1C1 = "=IF(RC[4]=""Agent:"",RC[5],R[-1]C)"
      .value = .value
   End With
   With Range("B2:B" & UsdRws)
      .FormulaR1C1 = "=COUNTIFS(RC5:RC[3],""open the call appropriately"",RC1:RC[-1],RC[-1])"
      .value = .value
   End With
   With Range("C2:C" & UsdRws)
      .FormulaR1C1 = "=CONCATENATE(RC[-2],RC[2])"
      .value = .value
   End With
   With Range("D2:D" & UsdRws)
      .FormulaR1C1 = "=IF(IF(ISERR(LEFT(RC[3],1)*1),"""",VALUE(LEFT(RC[3],1)))=0,1,"""")"
      .value = .value
   End With

End Sub

 

[Claret]

Wow!! That works brilliantly - thanks so much to you both, really appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[GiorgiPepe]

Hello Claret,

regarding your code i would give you couple suggestions to have better code:

1)set range to to cell where you paste report in this case you have A1 but in case you will move it to B1 you will have to change whole code;
so for ex. Dim ReportRng = sheet1.cells(1,1)
2)once u have set ReportRng next you wanna do is to use offset in your VBA code, what i mean is that you know that in report B might change to C (so move one column left, it means A column became B column) the point is that from A to B or From B to C the difference is 1 and always will be one, having said that you wanna use following code: ReportRng.offset(0,1), of if you have headers in first row and formula starts from second row you need to wright ReportRng.offset(1,1) and etc. for other columns too.
3) you must have last row number in your report and code for that will be: Lrow = Sheet1.cells(rows.count,ReportRng.column).End(xlUp).Row
4) after that what you can do is that use for each loop but it will consume time if Data is big, instead you could enter code in one cell and jut copy from Row(2) (Row(1) is header ) to Lrow. Think it will be much faster.

P.S. I am new on this forum and Think my post is too BIG :D I will work on that
Good Luck with your VBA Code