i have a workbook with two sheets-dump & calc.in the dump sheet i dump data on daily basis ,in calc sheet have calculated formulas which i drag down depending on the no of rows of data in the dump sheet.i m looking for a code which will drag the formulas down in calc sheet and except the last row will change all the formulas into value
Fill formulas down column and convert to values
-
-
-
Re: macro to drag formulas down depending on data in the other sheet of the same work
please help
-
Re: macro to drag formulas down depending on data in the other sheet of the same work
Hi Sudharsan,
Please give us a sample data from your worksheets which will help us to suggest a solution for your problem.
Shree
-
Re: macro to drag formulas down depending on data in the other sheet of the same work
Hi Shree,
i am attaching the dummy file. I have to dump more than 2000 rows of data daily in dump sheet and then have to manually drag formulas in calc sheet accordingly.we use one file for a months and by the end of month file gets very heavy and takes a lot of time to update.please check the dummy file hope that will make it more clear.
Sudarshan -
Re: macro to drag formulas down depending on data in the other sheet of the same work
Hi Sudharsan,
Sorry, I was bit late as I had another work.
Here is your code:
You have to run "fillFormulae" macro in the below code.
Code
Display MoreSub fillFormulae() Dim offRow As Long, totCol As Long, tmp offRow = Sheets("Dump").UsedRange.Rows.Count - 2 totCol = WorksheetFunction.CountA(Sheets("Dump").Range("A1:IV1")) Sheets("Calc").Range("A2:IV" & Cells.Rows.Count).Clear For tmp = 0 To totCol With Sheets("Calc").Range("A2").Offset(offRow, tmp) Range(Range("A2").Offset(0, tmp).Address, .Address).Formula = getColVal(tmp, offRow) Range(Range("A2").Offset(0, tmp).Address, .Address).Formula = Range(Range("A2").Offset(0, tmp).Address, .Address).Value End With Select Case tmp + 1 Case 3, 5, 6, 9, 12 To 14, 16 To 25, 28 To 38 Range(Range("A2").Offset(0, tmp).Address, .Address).NumberFormat = "[h]:mm:ss;@" End Select Next tmp End Sub Public Function getColVal(ByVal colNum As Long, ByVal rowOff As Long) As String Select Case colNum + 1 Case 1, 2, 4, 7, 8, 10, 11, 15, 26, 27, 39, 40 getColVal = "=" & Sheets("Dump").Name & "!" & Replace(Range(Range("A2").Offset(0, colNum).Address, Sheets("Calc").Range("A2").Offset(offRow, colNum).Address).Address, "$", "") Case 41 getColVal = "=" & Sheets("Dump").Name & "!" & Replace(Range(Range("A2").Offset(0, 2).Address, Sheets("Calc").Range("A2").Offset(offRow, 2).Address).Address, "$", "") & "*" & Sheets("Dump").Name & "!" & Replace(Range(Range("A2").Offset(0, 3).Address, Sheets("Calc").Range("A2").Offset(offRow, 3).Address).Address, "$", "") Case 3, 5, 6, 9, 12 To 14, 16 To 25, 28 To 38 getColVal = "=" & Sheets("Dump").Name & "!" & Replace(Range(Range("A2").Offset(0, colNum).Address, Sheets("Calc").Range("A2").Offset(offRow, colNum).Address).Address, "$", "") & "/3600/24" End Select End Function
Please change the format of the cells for the time and date after you run the macro.
Shree
-
-
Re: macro to drag formulas down depending on data in the other sheet of the same work
Hello,
I have attached the solution.
Code
Display MoreSub Cal() Dim maxrow, maxcol As Integer Application.DisplayAlerts = False Worksheets("Dump").Activate maxrow = Application.WorksheetFunction.CountA(Columns(1)) maxcol = Application.WorksheetFunction.CountA(Rows(1)) Worksheets("calc").Select Cells.Delete tem = "" & "C2:C" & maxrow & "," & "E2:E" & maxrow & "," & "F2:F" & maxrow & "," & "I2:I" & maxrow & "," & _ "L2:L" & maxrow & "," & "M2:M" & maxrow & "," & "N2:N" & maxrow & "," & "P2:P" & maxrow & "," & "Q2:Q" & maxrow & "," & "R2:R" & maxrow & "," & _ "S2:S" & maxrow & "," & "T2:T" & maxrow & "," & "U2:U" & maxrow & "," & "V2:V" & maxrow & "," & "W2:W" & maxrow & "," & "X2:X" & maxrow & "," & "Y2:Y" & maxrow & "," & _ "AB2:AB" & maxrow & "," & "AC2:AC" & maxrow & "," & "AD2:AD" & maxrow & "," & "AE2:AE" & maxrow & "," & "AF2:AF" & maxrow & "," & "AG2:AG" & maxrow & "," & "AH2:AH" & _ maxrow & "," & "AI2:AI" & maxrow & "," & "AJ2:AJ" & maxrow & "," & "AK2:AK" & maxrow & "," & "AL2:AL" & maxrow & "" For i = 1 To maxcol If i = 1 Or i = 2 Or i = 4 Or i = 7 Or i = 8 Or i = 10 Or i = 11 Or i = 15 Or i = 26 Or i = 27 Or i = 39 Or i = 40 Or i = 41 Then Worksheets("Dump").Activate Worksheets("Dump").Range(Cells(1, i), Cells(maxrow, i)).Copy Worksheets("Calc").Cells(1, i).PasteSpecial Else Worksheets("Calc").Activate Worksheets("calc").Cells(1, i).Value = "=Dump!RC" Worksheets("calc").Cells(2, i).Select ActiveCell.FormulaR1C1 = "=Dump!RC/3600/24" End If Next Worksheets("Calc").Select Range("AO1").Value = "TT" Range("AO2").Select ActiveCell.FormulaR1C1 = "=Dump!RC[-38]*Dump!RC[-37]" Worksheets("Calc").Range(Cells(2, 41), Cells(maxrow, 41)).Select Selection.FillDown Range(tem).Select Selection.NumberFormat = "[h]:mm:ss;@" Selection.FillDown Sheets("Calc").Cells(1, 1).Select Application.DisplayAlerts = False MsgBox "Report Completed..." End Sub
Cheers,
Nagesh -
Re: macro to drag formulas down depending on data in the other sheet of the same work
Hi Nagesh,
Please do not forget to add "code" tags for your codes.
[ code]<your code>[/code]
Shree
-
Re: macro to drag formulas down depending on data in the other sheet of the same work
Hi Shree,
Thank you!!!
Nagesh
-
Re: macro to drag formulas down depending on data in the other sheet of the same work
nageshpl,
Per the Forum Rules, VBA code must be wrapped in code tags, which you omitted.
I've added the tags for you this time; please be sure to use them in future posts when needed.See the link in my signature for more information on how to use tags.
[COLOR="blue"]How to use code tags[/COLOR]
[noparse][/noparse]
-
Re: Fill formulas down column and convert to values
Hi Shree/Nagesh,
Thanks a lot for your help.
Regards,
Sudarshan -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!