(Solved) – VBA Worksheet_Change Formula Works For Numbers But Breaks For Strings

(solved)-–-vba-worksheet-change-formula-works-for-numbers-but-breaks-for-strings

This code works for formulas that return a 0 but not for formulas that return a “string”. The effect is that there is a ghost value in the cell a user can overwrite then see again if they delete their value. Can someone help me make this work for both numeric and string outputs? The dugger throws an error on this line:

.Formula = v(i, 2)

Here is the whole block. Thanks for the help!

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i&, v
  DoEvents
  ReDim v(1 To 40, 1 To 2)
  v(1, 1) = "F7": v(1, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),10),0)"
  v(2, 1) = "F8": v(2, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),9),0)"
  v(3, 1) = "F9": v(3, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),11),0)"
  v(4, 1) = "F10": v(4, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),12),0)"
  v(5, 1) = "F11": v(5, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),15),0)"
  v(6, 1) = "F12": v(6, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),14),0)"
  v(7, 1) = "F13": v(7, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),8),0)"
  v(8, 1) = "F16": v(8, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),19),0)"
  v(9, 1) = "B26": v(9, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),1),"")"
  v(10, 1) = "G24": v(10, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (G3=DATABASE!$T$2:$T$3222) * (G4=DATABASE!T2:T3222),0),24),"")"
  v(11, 1) = "G28": v(11, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (G3=DATABASE!$T$2:$T$3222) * (G4=DATABASE!T2:T3222),0),26),"")"
  v(12, 1) = "H24": v(12, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (H3=DATABASE!$T$2:$T$3222) * (H4=DATABASE!U2:U3222),0),24),"")"
  v(13, 1) = "H28": v(13, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (H3=DATABASE!$T$2:$T$3222) * (H4=DATABASE!U2:U3222),0),26),"")"
  v(14, 1) = "I24": v(14, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (I3=DATABASE!$T$2:$T$3222) * (I4=DATABASE!U2:U3222),0),24),"")"
  v(15, 1) = "I28": v(15, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (I3=DATABASE!$T$2:$T$3222) * (I4=DATABASE!U2:U3222),0),26),"")"
  v(16, 1) = "J24": v(16, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (J3=DATABASE!$T$2:$T$3222) * (J4=DATABASE!U2:U3222),0),24),"")"
  v(17, 1) = "J28": v(17, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (J3=DATABASE!$T$2:$T$3222) * (J4=DATABASE!U2:U3222),0),26),"")"
  v(18, 1) = "M24": v(18, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (M3=DATABASE!$T$2:$T$3222) * (M4=DATABASE!U2:U3222),0),24),"")"
  v(19, 1) = "M28": v(19, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (M3=DATABASE!$T$2:$T$3222) * (M4=DATABASE!U2:U3222),0),26),"")"
  v(20, 1) = "N24": v(20, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (N3=DATABASE!$T$2:$T$3222) * (N4=DATABASE!U2:U3222),0),24),"")"
  v(21, 1) = "N28": v(21, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (N3=DATABASE!$T$2:$T$3222) * (N4=DATABASE!U2:U3222),0),26),"")"
  v(22, 1) = "O24": v(22, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (O3=DATABASE!$T$2:$T$3222) * (O4=DATABASE!U2:U3222),0),24),"")"
  v(23, 1) = "O28": v(23, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (O3=DATABASE!$T$2:$T$3222) * (O4=DATABASE!U2:U3222),0),26),"")"
  v(24, 1) = "P24": v(24, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (P3=DATABASE!$T$2:$T$3222) * (P4=DATABASE!V2:V3222),0),24),"")"
  v(25, 1) = "P28": v(25, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (P3=DATABASE!$T$2:$T$3222) * (P4=DATABASE!V2:V3222),0),26),"")"
  v(26, 1) = "Q24": v(26, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (Q3=DATABASE!$T$2:$T$3222) * (Q4=DATABASE!U2:U3222),0),24),"")"
  v(27, 1) = "Q28": v(27, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (Q3=DATABASE!$T$2:$T$3222) * (Q4=DATABASE!U2:U3222),0),26),"")"
  v(28, 1) = "R24": v(28, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (R3=DATABASE!$T$2:$T$3222) * (R4=DATABASE!U2:U3222),0),24),"")"
  v(29, 1) = "R28": v(29, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (R3=DATABASE!$T$2:$T$3222) * (R4=DATABASE!U2:U3222),0),26),"")"
  v(30, 1) = "T24": v(30, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (T3=DATABASE!$T$2:$T$3222) * (T4=DATABASE!U2:U3222),0),24),"")"
  v(31, 1) = "T28": v(31, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (T3=DATABASE!$T$2:$T$3222) * (T4=DATABASE!U2:U3222),0),26),"")"
  v(32, 1) = "U24": v(32, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (U3=DATABASE!$T$2:$T$3222),0),24),"")"
  v(33, 1) = "U28": v(33, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (U3=DATABASE!$T$2:$T$3222),0),26),"")"
  v(34, 1) = "V24": v(34, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (V3=DATABASE!$T$2:$T$3222),0),24),"")"
  v(35, 1) = "V28": v(35, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH(1,($B$11=DATABASE!$E$2:$E$3222) * (V3=DATABASE!$T$2:$T$3222),0),26),"")"
  v(36, 1) = "B26": v(36, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),1),"")"
  v(37, 1) = "B27": v(37, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),5),"")"
  v(38, 1) = "B28": v(38, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),3),"")"
  v(39, 1) = "B29": v(39, 2) = "=IFERROR(INDEX(DATABASE!$D$2:$AG$3222,MATCH('Pricing Grid'!$B$11,DATABASE!$E$2:$E$3222,0),4),"")"
  v(40, 1) = "B31": v(40, 2) = "=IFERROR(INDEX(DATABASE!D2:AG3222,MATCH(B11,DATABASE!E2:E3222,0),7),0)"
  Application.EnableEvents = False
  For i = 1 To UBound(v)
    With Range(v(i, 1))
      If Not Intersect(Target, .Cells) Is Nothing Then
        If Len(.Value2) = 0 Then
          .Formula = v(i, 2)
        End If
      End If
    End With
  Next
  Application.EnableEvents = True
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *