Excel Worksheet Change VBA to update formula range in a worksheet for changing input data

Question

I have found this answer on SO that is close to what I want to do, but my knowledge of VBA is too basic to modify for my specific situation.

Automatically copy formulas as data expands

The difference in my case is that the data my formulas refer to is not the entire previous worksheet. For example, I want my formulas in sheet "B" to use data from sheet "A" that corresponds to those rows containing cells with string "XYZ." Sheet "A" is linked to a data source that updates regularly, changing the number of rows containing said string. As "A" updates, "B" calculates what it needs to, then is plotted automatically (which I already have a sub for), but I cannot figure out how to automatically make the range of formulas reflect the fluctuating range of data.

Any ideas?

Is there one cell on sheet B for every row in A that contains the string "XYZ"? Are the cells with "XYZ" contiguous, or will there be some rows that don't have it? I'll try to make a simple example based on some assumptions... — Floris

Floris Floris · Accepted Answer · 2013-05-31T23:12:00

Based on some reasonable assumptions of what I think you are trying to do I wrote and tested the following code snippet (adapted from your link). Add this to the code of worksheet A, and see how it works when you have strings in column A, and values in column B of sheet A. Where there is "XYZ" in A, the value in column B will be transferred to sheet B, column A. When you change anything on sheet A, B will be updated.

If this doesn't help you solve the problem, you will have to explain more clearly what is missing...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' when worksheet changes, copy value adjacent to a cell with the string "XYZ" in it
' to worksheet "B", starting in "A1".
' after clearing out everything that was in that range first

    Dim oRangeDest As Range
    Dim oRangeSearch As Range
    Dim searchString As String
    Dim foundCell As Range
    Dim firstFound As String

    'Define output range
    Set oRangeDest = Worksheets("B").Range("A1")
    Set oRangeSearch = Worksheets("A").Range("A:A")

    ' value of string to search for:
    searchString = "XYZ"

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' clear the formulas in sheet B:
    Worksheets("B").Activate
    With ActiveSheet
      .Range(oRangeDest, oRangeDest.End(xlDown)).Select
      Selection.Clear
      .Range("A1").Select
    End With

    Worksheets("A").Activate ' can only search on active sheet

    ' find cells in column A on sheet 1 with "XYZ" in them:
    Set foundCell = oRangeSearch.Find(What:=searchString, _
        After:=ActiveSheet.Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)
    If Not foundCell Is Nothing Then
        ' if found, remember location
        firstFound = foundCell.Address
        ' copy the value of the next cell over to the next available cell on sheet B
        Do
            ' copy something from a cell adjacent to the one we found:
            ' obviously we could access a different column, value or formula, etc...
            oRangeDest.Value = foundCell.Offset(0, 1).Value
            Set oRangeDest = oRangeDest.Offset(1, 0) ' down one row for next time
            ' find next instance
            Set foundCell = oRangeSearch.FindNext(After:=foundCell)
            ' repeat until back where we started
        Loop Until firstFound = foundCell.Address
    End If

    Application.EnableEvents = True

End Sub

Screen shots of sheets A and B (slightly messed up by the alt key...):

enter image description here

Excel Worksheet Change VBA to update formula range in a worksheet for changing input data

1 Answers