To fix the issue you can do I have four worksheets and I write one formula in cell F2 which trigger a filldown for all the rest. I already have the trigger private sub to call my procedure that works. Is there a way to make this sub more efficient? , It could look something like this... Option Explicit
Sub FillDownFormula_test2()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Filldown a formula for in column of data.
' Assumes a data table with headings in the first row,
' the formula in the second row and is the active cell.
' this one is tested based on set range
' Test results were positive
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = wb.Sheets("Feuil1")
With ws
Call FillDownData(.Range("F2"), Range("F2").CurrentRegion)
Call FillDownData(.Range("G2"), Range("G2").CurrentRegion)
End With
Set ws2 = wb.Sheets("Feuil2")
With ws2
Call FillDownData(.Range("F2"), Range("F2").CurrentRegion)
Call FillDownData(.Range("G2"), Range("G2").CurrentRegion)
End With
End Sub
Function FillDownData(rng As Range, rngData As Range)
Dim rowData As Long
Dim colData As Long
Dim rngFormula As Range
' Set the row and column variables
rowData = rngData.CurrentRegion.Rows.Count
colData = rng.column
Debug.Print rowData & " " & colData
' Set the formula range and fill down the formula
'Set rngFormula = rngData.Offset(1, colData  1).Resize(rowData  1, 1)
'rngFormula.FillDown
End Function
Boards Message : 
You Must Login
Or Sign Up
to Add Your Comments . 
Share :

Excel VBA formula, called from a cell, stops processing VBA, or encounters an AppDefined error, when a worksheet cell i
Tag : excel , By : Topher Cyll
Date : March 29 2020, 07:55 AM
this will help This is just the builtin behavior of Excel. Functions called from the worksheet (often called UDFs in Excel terminology  userdefined functions) can't do anything to the worksheet other than return a value. In your code above, there appears to be another error, though. Set destination.Value = ChannelCode
destination.Value = ChannelCode

EXCEL How to set a cell to a specific color based on a formula that involves another worksheet?
Date : March 29 2020, 07:55 AM
I wish this help you You cannot refer to another worksheet in conditional formatting formulas (this would have been the easiest solution  try it, excel will throw error) There is a simple way around that however  Named Ranges! [A] [B] [C] [D] [E] [F]
[1] Q1 Q2 Q3 Q3 Q4 Q5
[2] 20 20 30 30 50 80
[3]

Copy cell range from one worksheet and paste in a different worksheet as a value rather than formula
Tag : excel , By : Jody Bannon
Date : March 29 2020, 07:55 AM
This might help you This is essentially transposing rows to columns from one worksheet to another. Instead of the many lines of copy/paste with specific ranges, use a loop, Reducing 2550 lines into 3. Private Sub CopyAuditData_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim DestRow As Long, lRow As Long
Set ws1 = Sheets("Call Audit Sheet")
Set ws2 = Sheets("HiddenData")
DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
'CHANGES HERE
For lRow = 1 To 25
ws2.Cells(DestRow, lRow).Value = ws1.Cells(lRow, "V").Value
Next lRow
End Sub

How to make formula in conditional formatting rule manager filldown (varies depending on cell it's applied to)
Tag : excel , By : mlapida
Date : March 29 2020, 07:55 AM
this will help I am using conditional formatting rules manager with the formula below.If the following statement is true, then the colour of the cell is set to red and the font colour is set to yellow: Please look into the below code, you can change the condition as per your requirement
Dim Redcolour As Long
Dim yellowcolour As Long
Redcolour = RGB(255, 0, 0)
yellowcolour = RGB(255, 255, 0)
If Condition Then
ActiveSheet.Range("b7").Interior.Color = Redcolour
ActiveSheet.Range("b7").Font.Color = yellowcolour
End If

VBA to FillDown Formula with changing cell reference
Tag : excel , By : Robert M
Date : March 29 2020, 07:55 AM
This might help you I have a Sumifs function to be done in macro with 2 criterias. my excel file is quite big. So my code looks like this : , It sounds like you are after something like: Sub SumIfs()
With ThisWorkbook.Sheets("Sheet1")
.Range("L2:L" & .Range("G" & .Rows.Count).End(xlUp).Row).Formula = _
"=SUMIFS(G:G,A:A,A2,F:F,""John"")"
End With
End Sub
Sub SumIfs()
Dim LastRow As Long
With ThisWorkbook.Sheets("Sheet1")
'Assume we want to create values for every cell in column L down until
'we get to the last cell in column G
LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
'Paste the formula
.Range("L2:L" & LastRow).Formula = _
"=SUMIFS(G:G,A:A,A2,F:F,""John"")"
'Convert to values
.Range("L2:L" & LastRow).Value = _
.Range("L2:L" & LastRow).Value
End With
End Sub

