Hi I have a query table that refreshes when my workbook is opened. I have added a column with a Vlookup to look for the query table values in a list on another worksheet called "Completed WO List".
I have a macro which I run every morning which selects all cells in the column with the Vlookup which are returning an NA. It then changes these cell values to yesterdays date using FormulaArray, selects the entire row for all of the selected cells, copies the row and pastes it into the next empty row in the "Completed WO List" sheet.
I have been using it for months with no issue and today I am getting the error: "Unable to set the FormulaArray property of the Range class"
The line which is causing difficulty is:
I am aware of the 255 character limit on FormulaArray but I am a long way from breaching that. Stepping through the Macro up to this point there are 56 lines selected with NA values so all is working up to that point. 56 is more lines than usual for this exercise, I don't know if there is a limit on the array size but if there was I would have thought it was larger than that. Full code is below.
Any help would be great.
I have a macro which I run every morning which selects all cells in the column with the Vlookup which are returning an NA. It then changes these cell values to yesterdays date using FormulaArray, selects the entire row for all of the selected cells, copies the row and pastes it into the next empty row in the "Completed WO List" sheet.
I have been using it for months with no issue and today I am getting the error: "Unable to set the FormulaArray property of the Range class"
The line which is causing difficulty is:
HTML:
Selection.FormulaArray = "=TODAY()-1" 'Insert date into selected column
I am aware of the 255 character limit on FormulaArray but I am a long way from breaching that. Stepping through the Macro up to this point there are 56 lines selected with NA values so all is working up to that point. 56 is more lines than usual for this exercise, I don't know if there is a limit on the array size but if there was I would have thought it was larger than that. Full code is below.
Any help would be great.
HTML:
Sub Macro2()
'
' Macro2 Macro
'
'
Sheets("Query Table").Select 'Go to query table with Sigma data
Range("H2").Select 'Go to Column with possible NA values
Selection.SpecialCells(xlCellTypeFormulas, 16).Select 'Select all cells with a value of NA
Selection.FormulaArray = "=TODAY()-1" 'Insert date into selected column
Selection.EntireRow.Select 'Select entire row for each selected cell
Selection.Copy 'Copy all selected rows
Sheets("Completed WO List").Select 'Go to sheet named "Completed WO List"
Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'Go to first blank cell in column A
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'Paste copied values into next empty rows in "Completed WO List"
Sheets("Query Table").Select 'Go back to "Query table" sheet
Range("H2").Select 'Select column H
Selection.FormulaArray = _
"=VLOOKUP([@WONumber],'Completed WO List'!C[-7],1,FALSE)" 'Insert lookup formula in calumn H to return sheet to original state
Sheets("Completed WO List").Select 'Take the user back to the front sheet
End Sub