Need VB Script to Return Formula Value when worksheet_change target is met.

[Latchmaker]

I have a spreadsheet with several cells in same row that have similar formulas =IF(ISBLANK(B49),"",VLOOKUP(B49,'ITEMBLZ DOWNLOAD'!C:P,13,0))

I also have a cell that date codes based off of an entry into Cell B49 this is a worksheet_change within the same worksheet as the formulas.

My goal is to eliminate a sheet of formulas and have the worsheet_change just return the values of all my formulas when the same worksheet_change is met.

This is my worksheet_change code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B:B")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
If Target.Value >= 0 Then Target.Offset(0, 13).Value = Now()
End If
Set rng = Target.Parent.Range("H:H")
If Not Intersect(Target, rng) Is Nothing Then
If Target.Value >= 0 Then Target.Offset(0, 8).Value = Now()
End If

End Sub

I feel if someone can help me with the coding for the given formula I can add the other formulas in to this code....
Thank You

 

[Jerry Sullivan]

The code below will put a formula like your example in Column C next to each entry in Column B. It will also insert the date stamps.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    On Error GoTo CleanUp
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        With Target.Offset(0, 1)
            .FormulaR1C1 = "=VLOOKUP(RC2,'ITEMBLZ DOWNLOAD'!C3:C16,13,0)"
       End With
        If Target.Value >= 0 Then Target.Offset(0, 13).Value = Now()
    Else
        If Not Intersect(Target, Range("H:H")) Is Nothing Then
            Application.EnableEvents = False
            If Target.Value >= 0 Then Target.Offset(0, 8).Value = Now()
        End If
    End If
CleanUp:
    Application.EnableEvents = True
End Sub

Depending on the relationship of your other formulas, you might be able to extend your formulas
to the other cells by replacing the code in blue font above with something like:

With Target.Offset(0, 1).Resize(1, 4)
      .FormulaR1C1 = "=VLOOKUP(RC2,'ITEMBLZ DOWNLOAD'!C3:C16,COLUMN()-1,0)"
End With

 

[Latchmaker]

So what script can I use so after all formulas are inserted that the final thing to do is copy this row and Paste special values so there is no formulas only hard values?

 

[Jerry Sullivan]

Just add this line after the .FormulaR1C1 statement....

With Target.Offset(0, 1).Resize(1, 4)
      .FormulaR1C1 = "=VLOOKUP(RC2,'ITEMBLZ DOWNLOAD'!C3:C16,COLUMN()-1,0)"
      .FormulaR1C1 = .Value