logo
Home JAVASCRIPTJAVACHASHPYTHONPHPCPPIOSSQLHTML
down
shadow

trigger filldown formula based on cell of another worksheet


trigger filldown formula based on cell of another worksheet

Content Index :

trigger filldown formula based on cell of another worksheet
Tag : excel , By : woxorz
Date : January 12 2021, 08:33 AM

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

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Tags :

trigger filldown formula based on cell of another worksheet , VBA to FillDown Formula with changing cell reference , EXCEL How to set a cell to a specific color based on a formula that involves another worksheet , How to make formula in conditional formatting rule manager filldown (varies depending on cel ,

Excel VBA formula, called from a cell, stops processing VBA, or encounters an App-Defined 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 built-in behavior of Excel. Functions called from the worksheet (often called UDFs in Excel terminology - user-defined 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?

Tag : development , By : RyanMcG
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 25-50 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
Related Posts Related QUESTIONS :
  • Shorten a cell, nested Ifs Excel
  • Finding the volume of a tank in VBA given the radius, height, and depth
  • Compile Error on simple VBA form and command button click code
  • Looping through Workbooks and Copy a Dynamic range to Master Workbook
  • Using .NET HashTable Return Type in VBA
  • How do I not get Run-time error '13': Type mismatch while iterating rows?
  • How to change image of a Powerpoint shape?
  • Adding character before text in Excel without using formula
  • How can I create a list of numbers that is like the following?
  • How to fix " Run-time error '380' in Excel VBA?
  • How can you repeat a number x times in a column in a continuation, in excel
  • How to delete the content of a txt file with vba?
  • How to work with more Target.Address (range)
  • Change arrow color based on its label or value
  • how to get inner text of html under id?
  • Getting "Argument not optional" error on segment of code
  • can't add 1 to current cell value
  • Role/permission analysis in Excel: comparing list of user permissions with pairs of disallowed roles
  • Excel Range.PivotField equivalent for JavaScript API
  • How to fix 1004 error with macros in Excel VBA?
  • How to copy rows between worksheets based on a text value
  • Even though method exist in sheet
  • How to delete a row if certain cells are blank in VBA
  • how to get address cell
  • Change Worksheet CodeName in protected VBProject
  • Column Offset Excel Formula
  • Convert UTC time stamp in text to Excel date
  • Formula where Number is constant
  • Excel VBA / Split String Into Columns based on Character Count
  • getElementById won't work in VBA, error 438
  • Writing to Excel file in specific format using Perl
  • Filter text out of barcode while scanning physical barcodes
  • Trouble getting custom function syntax right: Contains()
  • Import multiple csv files to specific sheets in a workbook
  • Range.Find Function
  • I need to assign time value to a variable
  • Run event whenever date changes in a cell A1
  • Printing values from a multidimensional array
  • How do I make a cell equal to the value of the cell address returned from another cell?
  • How to loop through a column adding values from a second column?
  • How to create a Excel file with a ColdFusion which has a column which we can sort by Largest to Smallest or vice versa?
  • VBA Dictionary.Exists Method Returning False Negative Given String From String Array
  • Object Defined error when adding data validation
  • How to fix error 1004 on a wb.SaveAs function
  • Trying to split data from one cell into multiple cells
  • Excel VBA : Hyperlink that fills target cell's contents with hyperlinked text
  • Why is VBA function giving compile error for collection but not for string
  • Error 91 Object variable or With block variable not set
  • How to find the sum for customer's multiple payments using Excel formula?
  • Depends of what columns are selected create a new sheet
  • VBA converting formula to comment
  • Clear contents of active cell if it contains specific text
  • While dynamic range
  • How to make "edit links" VBA code run faster?
  • Deleting Rows with Reverse Loop - VB
  • VBA change font properties of chart axes without using With-EndWith Statement
  • Find a string within an Excel file and get the row index
  • Referencing Public Constants in Cell Formulas
  • How to return worksheet or workbook name in cell
  • Why is this VBA function slow?
    		•
    shadow
    Privacy Policy - Terms - Contact Us © scrbit.com