Asked  7 Months ago    Answers:  5   Viewed   36 times

I have this code:

Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim ws As Worksheet: Set ws = Sheets("2012")
Dim rngLook As Range: Set rngLook = ws.Range("A:M")

'within a loop
  currName = "Example"
  cellNum = wsFunc.VLookup(currName, rngLook, 13, False)

VLookup is not expected to always find a result; but when it does not find a result the line errors out before I can even error check it the next line.

The error:

Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class

It works fine when a result is found. What's a good way to handle errors here?

 Answers

68

There is a way to skip the errors inside the code and go on with the loop anyway, hope it helps:

Sub new1()

Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim ws As Worksheet: Set ws = Sheets(1)
Dim rngLook As Range: Set rngLook = ws.Range("A:M")

currName = "Example"
On Error Resume Next ''if error, the code will go on anyway
cellNum = wsFunc.VLookup(currName, rngLook, 13, 0)

If Err.Number <> 0 Then
''error appeared
    MsgBox "currName not found" ''optional, no need to do anything
End If

On Error GoTo 0 ''no error, coming back to default conditions

End Sub
Tuesday, June 1, 2021
 
Fernando
answered 7 Months ago
57

in AppExceptionsHandler.php Return the user to the form with a new valid CSRF token, so the page will refreshed and logout button will not exist.

public function render($request, Exception $exception)
{ 
   if($exception instanceof TokenMismatchException)
   { 
      return redirect()
               ->back()
               ->with('your msg');
   }
   return parent::render($request, $exception); 
}

this looking like, page was refreshed.

Don't Replace POST with Get. It will not Safe And Standard.

Saturday, May 29, 2021
 
Zach
answered 7 Months ago
59

I have found what seems to be the solution:

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        '.PrintArea = Worksheets(ReportWsName).UsedRange
        .FitToPagesWide = 1
        '.FitToPagesTall = 1
    End With
Application.PrintCommunication = True

I needed to add the Application.PrintCommunication part to the equation. For whatever reason Excel would overwrite the settings I was putting if I ran the code without it.

Friday, July 30, 2021
 
hillz
answered 5 Months ago
19

To use array formulas within VBA code set ReferenceStyle for Application object to xlR1C1 (temporarily, only during your function is executed). Finally call Evaluate to get the result of the formula.

Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

And so it looks on sheet:

enter image description here

enter image description here

Wednesday, August 11, 2021
 
lewiguez
answered 4 Months ago
80

Use the Preferences API:

var PreferencesManager = brackets.getModule("preferences/PreferencesManager");

prefs = PreferencesManager.getExtensionPrefs("jslint");

prefs.set("options.es6", true);

Or set it one of the config files:

  • the brackets.json file of the user directory

or:

  • the .brackets.json file of the project directory:

As such:

{
    "jslint.options": {
        "es6": true
     }
}

References

  • brackets: .brackets.json

  • brackets: jslint extension - main.js

  • brackets wiki: Preferences Overview

  • PreferencesManager - Brackets API

Saturday, October 16, 2021
 
Steven Frank
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share