Moving an array formula to VBA?

[WSBirch]

Good morning,
I have this array formula that's listed down a column C2:C69.

{=IFERROR(VLOOKUP($B$1,IF('[Workbook2.xlsm]Schedule'!$C$2:$C$5000=$B2,'[Workbook2.xlsm]Schedule'!$A$2:$D$5000,""),4,FALSE),"")}

It checks a two different columns for matching references, and if both match, then it returns the VLOOKUP value for the one that matches both references.

Currently, obviously, the formula runs in each of the 68 cells down the column. C2 starts with the above formula and is repeated down until it stops in C69.

It does a great job. However, is there a way to run this automatically in VBA when there's matching references instead?

For example, the array formula at cell C15 has a value that's returned blank because currently there's no matching references for the VLOOKUP. However, I'd like to be able to manually enter a value, say "123456" into cell C15. While C15 = 123456, if ever there's a time where there is a matching reference that would be returned in C15 instead, I want it to overwrite what's currently written into cell C15.

It's for a dock schedule. I want the cells to automatically return a Trailer # that's recorded in Workbook2, but sometimes there's trailers that we place at locations that are not being recorded and instead need entered manually, but should be overwritten if there's ever something assigned to that location instead.

 

[StephenCrump]

It's possible, but sounds like a recipe for disaster - a column that mixes formulae and values, where the values might be overwritten automatically if values change somewhere else in the workbook.

Much easier to see what's happening with something like:

	A	B	C	D
1		Formula	Manual	Value
2		VLOOKUP	Override	To use
3
4	A	Result1		Result1
5	B		MyEntry1	MyEntry1
6	C	Result2	MyEntry2	Result2
7	D	Result3		Result3
8	E		MyEntry3	MyEntry3
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=IF(B4="",C4,B4)

It would also be easy to add error messages or conditional formatting, e.g. to identify where the formula was blank and a manual override had not been provided.

 

[WSBirch]

That's actually a pretty great solution. I like simple like that. I do need to ask then since it's my original request, but slightly different now because you make a good point, is there a way with VBA to "remember" what manually entered value is being overwritten by the VLOOKUP value so when the VLOOKUP value disappears, the manually written in value reappears?

 

[StephenCrump]

If C2:C69 are formulae, occasionally overwritten with a manual entry when the VLOOKUP errors and returns "", then you could write VBA to

- monitor the lookup table
- replace any manual entry in the results section with a VLOOKUP formula if that VLOOKUP would now work
- store the previous manual entry in case it was needed again
- reinstate any previous manual entries (i.e. retrieve from storage) where the VLOOKUP formula now failed.

The storage/remembering part could be done in a VBA array size 68x1, i.e. the size of the results section.

But for this to persist after you close Excel, you'd need to store somewhere in the workbook. It seems to me the logical place to do this is next to the VLOOKUP formulae, hence my suggestion in Post #2.

So in summary, yes it's possible, but it would complicate the workbook and add VBA calculation overhead.

Just FYI, here's some VBA code for a simpler example - monitoring changes (i.e. user changes, not changes in formula results) in Sheet1!A1:A10:

'Code Module
Public MyValues As Variant
Public rngToMonitor As Range
'ThisWorkbook module
Private Sub Workbook_Open()
    
    Set rngToMonitor = Worksheets("Sheet1").Range("A1:A10")
    MyValues = rngToMonitor.Value2
    
End Sub
'Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim i As Long
    
    If Not Application.Intersect(rngToMonitor, Target) Is Nothing Then
        For i = 1 To UBound(MyValues)
            If rngToMonitor(i).Value <> MyValues(i, 1) Then
                MsgBox "You've changed " & rngToMonitor(i).Address & " from """ & MyValues(i, 1) & """ to """ & rngToMonitor(i).Value & """"
                MyValues(i, 1) = rngToMonitor(i).Value
            End If
        Next i
    End If

End Sub