Databases
StackOverflow2013
Postsdbo
POSo Close - IsErr Function in VBA Not working?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSo Close - IsErr Function in VBA Not working?
    primarykey
    data
    text
    <p>I am very close to my solution, but I seem to have one roadblock. I've copied the code below. In Summary, I use a VLookup to pull pseudo-formulas from another worksheet, replace a bogus "ZZZ" with a true cell reference, then turn those cell values into a final formula. That all works great, except that the users who input the pseudo formulas are wont to use erroneous formulas, so I need some error checking. First, I need any VLookup values that don't return a result to be renamed. Second, I need any of the final formulas that are invalid (such as missing a parentheses) to be renamed. Here's what I have so far:</p> <pre><code>Public Sub VLookup() ActiveSheet.EnableCalculation = True 'Define what our Rows are for the calculations Dim NumRecords As Long NumRecords = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").Range("B" &amp; Rows.Count).End(xlUp).Row Dim CellsForFormula As Range Set CellsForFormula = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").Range("G2", "G" &amp; NumRecords) Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").Select 'Now Insert the VLookup Dim WSLogic As Worksheet Dim WSData As Worksheet Set WSData = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data") Set WSLogic = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Logic Statements") 'Write the Vlookup in the cell CellsForFormula(1, 1).Formula = _ "=VLOOKUP('Paste Daily Data'!B2,'Logic Statements'!A:D,4,False)" 'Copy the Vlookup down CellsForFormula(1, 1).Copy _ Destination:=Range(CellsForFormula(2, 1), CellsForFormula(NumRecords - 1, 1)) 'Make sure the formulas actually calculate Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").UsedRange.Calculate 'Copy and Paste so we just keep the result of the Vlookup CellsForFormula.Copy CellsForFormula.PasteSpecial Paste:=xlPasteValues 'Now we can replace the "ZZZ" and "zzz" with the cell reference Application.EnableEvents = False Dim Cell As Variant On Error Resume Next For Each Cell In CellsForFormula If Cell.Value = "#N/A" Then Cell.Value = "Bill-to Not in POVA" 'ElseIf Cell.Formula.IsErr = "=" &amp; Cell.Value Then 'Cell.Value = "Logic Code Incorrect" Else Cell.Formula = "=" &amp; Cell.Value ActiveSheet.EnableCalculation = True End If Next Cell For Each Cell In CellsForFormula Call Cell.Replace("ZZZ", Cell.Offset(0, -4).Address) Cell.Application.WorksheetFunction = "=" ActiveSheet.EnableCalculation = True Next Cell For Each Cell In CellsForFormula If Cell.Value = "#N/A" Then Cell.Value = "Bill-to Not in POVA" ActiveSheet.EnableCalculation = True End If Next Cell Application.EnableEvents = True Sheet.Calculate End Sub </code></pre> <p>The problem is those formulas which are invalid - because of the On Error Resume Next, it's just leaving them alone:</p> <pre><code>AND(LEN($C$37)=10,ISNUMBER(VALUE($C$37)) </code></pre> <p>Any help would be greatly appreciated!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJ-Man
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload