none
VBA dynamic formula reference RRS feed

  • Question

  • Hey, 

    i have a worksheet with formulas in column "B". Column B should either use this formula or use manual input. I am using this VBA code so far:

    "Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    Dim a As Variant
    Dim Zeilenzahl As Long

    For Each Cell In ActiveSheet.Range("D8:D15")
    a = Cell.Offset(0, -1).Value
    If a = "" Or IsNumeric(a) = False Then
    Cell.Value = ""
    Else

    If Cell.Value = "" Then Cell.FormulaArray = "=INDEX('Route Code Presets'!A1:C300,MATCH(1,('Route Code Presets'!A1:A8=Dashboard!D3)*('Route Code Presets'!C1:C8=Dashboard!C8),0),2)"
    End If

    Next Cell
    End Sub"

    However, this will put the same formula in every cell in the range B8:B15. I want this part of the formula (Route Code Presets'!C1:C8=Dashboard!C8) to use the respective cells in column C. So for Cell B8, the formula should use C8, for B9, it should use C9.

    Thanks for your advise!


    • Edited by philg91 Wednesday, August 3, 2016 10:59 AM
    Wednesday, August 3, 2016 10:58 AM

Answers

  • Solution:

    If Cell.Value = "" Then Cell.FormulaArray = "=INDEX('Route Code Presets'!R1C1:R200C3,MATCH(1,('Route Code Presets'!R1C1:R200C1=Dashboard!R3C4)*('Route Code Presets'!R1C3:R200C3=Dashboard!RC[-1]),0),2)"

     
    • Marked as answer by philg91 Friday, August 5, 2016 11:21 AM
    Friday, August 5, 2016 11:21 AM

All replies

  • You could try Dashboard!C$8.
    Wednesday, August 3, 2016 1:16 PM
  • This will just copy the formula with Dashboard!C$8 in every cell cause VBA doesnt start in the 1st cell and drags it down like you would do manually.

    Thanks for your input though!

    Wednesday, August 3, 2016 1:21 PM
  • Solution:

    If Cell.Value = "" Then Cell.FormulaArray = "=INDEX('Route Code Presets'!R1C1:R200C3,MATCH(1,('Route Code Presets'!R1C1:R200C1=Dashboard!R3C4)*('Route Code Presets'!R1C3:R200C3=Dashboard!RC[-1]),0),2)"

     
    • Marked as answer by philg91 Friday, August 5, 2016 11:21 AM
    Friday, August 5, 2016 11:21 AM