Well this is something I have never come across before, however, I have found a solution. The error message itself got me wondering why the send command wasn’t recognising the defined application. I questioned whether the code was running too fast. (I don’t know where this light bulb moment came from but it has fixed … Read more
Running repeated lookups on a large range by looping through the cells or using Find() can be very slow. Depending on how many rows are being searched and how many lookups you’re running (and whether ID’s can be repeated in the lookup range) there are a few other options such as (eg) creating a “map” … Read more
You can programmtically create an AutoFilter, then select the matching values: Dim ws As Worksheet: Set ws = ActiveSheet With ws .AutoFilterMode = False .Range(“1:1”).AutoFilter .Range(“1:1″).AutoFilter field:=2, Criteria1:=”=Saturday”, Operator:=xlAnd With .AutoFilter.Range On Error Resume Next ‘ if none selected .Offset(1).Resize(.Rows.Count – 1).Columns(2).SpecialCells(xlCellTypeVisible).Select On Error GoTo 0 End With .AutoFilterMode = False End With
To get the exact column in a worksheet, use this code. Option Explicit Sub Sample() Dim ws As Worksheet Dim LastCol As Long Set ws = Sheets(“Sheet1″) ‘~~> This check is required else .FIND will give you error on an empty sheet If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then LastCol = 1 Else LastCol = ws.Cells.Find(What:=”*”, _ … Read more
As comments & answers already said, that’s just the VBE being helpful. However if you find it noisy to have the private fields and public members listed in the locals toolwindow, there’s a way to nicely clean it up – here I put the Test procedure inside ThisWorkbook, and left the class named Class1: So … Read more
from high number To 1 adding (-1) each iteration Note: It’s adding because + AND – in mathematical logic evaluate to a – If rows = 10 then for i = 10 to 1 step -2 would mean loop back from 10 to 1 subtracting 2 from the i in each loop cycle. adding a … Read more
Inserting a formula with VBA requires that you use EN-US standards like, Range(“B64”).Formula = “=INDEX(AK7:AK123, G74, 1)” … or use the regional formula attribute like, Range(“B64”).FormulaLocal = “=INDEX(AK7:AK123; G74; 1)” You may have to also change INDEX to the regional equivalent. The latter is necessary when you have a system with regional settings that do … Read more
you can use this code Private Sub CommandButton3_Click() Dim strng As String Dim lCol As Long, lRow As Long With Me.ListBox1 ‘<–| refer to your listbox: change “ListBox1” with your actual listbox name For lRow = 0 To .ListCount – 1 ‘<–| loop through listbox rows If .selected(lRow) Then ‘<–| if current row selected For … Read more
You do not need to activate the sheet (you’ll take a huge performance hit for doing so, actually). Since you are declaring an object for the sheet, when you call the method starting with “wb.” you are selecting that object. For example, you can jump in between workbooks without activating anything like here: Sub Test() … Read more
I think the problem here was that the macro did not have permission to write to the registry. More information in this page. I could read the key’s value using the WScript object just fine: Debug.Print CreateObject(“WScript.Shell”).RegRead(“HKLM\SYSTEM\CurrentControlSet\Services\USBSTOR\Start”) To write (it should work if you have permissions): CreateObject(“WScript.Shell”).RegWrite “HKLM\SYSTEM\CurrentControlSet\Services\USBSTOR\Start”, 4, “REG_DWORD” How I got it to … Read more