Experts Exchange connects you with the people and services you need so you can get back to work.
Const XFORMULAS = "D8:D30"
Const XCOMPONENTS = "C32:C49"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range
Dim xArray As Variant
Dim xLoaded As Boolean
Dim xHold As String
Dim i As Long
If Intersect(Target, Union(Range(XFORMULAS), Range(XCOMPONENTS))) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each xCell In Range(XFORMULAS)
xHold = xCell.Formula
If Mid(xHold, 1, 1) = "=" Then
If Not xLoaded Then
xArray = WorksheetFunction.Transpose(Range(XCOMPONENTS).Value)
xLoaded = True
For i = 1 To UBound(xArray)
If xArray(i) <> "" Then xHold = Replace(xHold, Replace(Range("D31").Offset(i, 0).Address, "$", ""), xArray(i))
xCell.Offset(0, 2) = "'" & xHold
xCell.Offset(0, 2) = ""
Application.ScreenUpdating = True
Application.EnableEvents = True
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.