CODEWITHSUNDEEP
excelvba
Adrian Burca
Adrian Burca

Reputation: 21

Insert Formula & copy paste range based on last row

Original Question: OK so this is my task:

  1. I need to insert 3 different formulas in C2, D2, E2, (lets say different Sum's)
  2. apply the formula to the range from C2 to E2 till the last row
  3. select all the formulas I have just inserted and copy / paste only the values (so that the formulas go away and only the value remain)

The numbers of rows change every day so I cannot have a fixed range.

How do i write this in VBA for Excel ?

I had something like this to implement the formulas in C2

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Foglio2!C[-1]:C,2,0)"
Selection.AutoFill Destination:=Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

but i do not know why it worked only one time.

Clarification:

I am running my code from a different file so i need to open the 2 flies before i start working this is the code i have so far:

Workbooks.Open Filename:="C:\Users\user\Desktop\HC.xlsx"
Workbooks.Open Filename:="C:\Users\user\Desktop\2020 Rds da gestire.xlsx"

Range("F2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[HC.xlsx]20200608 Rds da gestire'!C5:C6,2,0)"
Selection.AutoFill Destination:=Range("F2:F" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("G2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-2],'[HC.xlsx]20200608 Rds da gestire'!C5:C7,3,0)"
Selection.AutoFill Destination:=Range("G2:G" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("H2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-3],'[HC.xlsx]20200608 Rds da gestire'!C5:C8,4,0)"
Selection.AutoFill Destination:=Range("H2:H" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

With this I am able to correctly implement the formulas everywhere, all i need now is to actually get the value from the formulas and i am good to go !!!

I still need to select all the formulas I have just inserted and copy / paste only the values (so that the formulas go away and only the value remain)

Upvotes: 1

Views: 355

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

You can write the formula into the complete range with only 1 line of code. And then convert it into values with another line of code.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'specify your sheet!
'or the following line (use only one of them)
Set ws = Workbooks.Open(Filename:="C:\Users\user\Desktop\HC.xlsx")

Dim LastRow As Long
LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

'repeat the following for your formulas in the other columns
With ws.Range("C2:C" & LastRow)
    .FormulaR1C1 = "=VLOOKUP(RC[-1],Foglio2!C[-1]:C,2,0)" 'write the formula in the range
    .Value = .Value 'convert formula into values
End With

Note that you should always specify in wich worksheet a Range is otherwise Excel does not know that for sure and guesses (it might guess wrong).

Upvotes: 1

Related Questions