variant

I have been trying to make a UDF to return salary based on various factors (COLA/Location, Job Title, Performance, etc). I have been running into issues incorporating ListObjects and assigning them to Ranges. I have looked at other forums and I don't know if this is possible, but I have tried...

Hello, I am trying to remove filter from WB1 Sheet3 and WB2 Sheet1. My code is as follows below. My issue here is I can't get WB2 to be activated to sort the data in WB2 Sheet1. My code brings me to WB1 Sheet1 instead. Thank you in advance. Dim varSheetA As Variant Dim varSheetB As Variant...

The code below work in early binding: Dim DIC As Scripting.Dictionary 'Dim DIC As Object Set DIC = New Scripting.Dictionary 'Set DIC = CreateObject("Scripting.Dictionary") Dim MyArray() As Variant MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value...

I am trying to set up a file to auto log into Hypersion. Declare Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long Declare Function HypCreateConnection Lib "HsAddin" (ByVal...

Good Morning, I have the following code that creates a PDF document for me based off a named range on the "Menu" sheet, cell A55. This all work perfectly. The issue I have is, I have a hyperlink on the "Overview" sheet cell B7, however when the PDF document is created, the hyperlink does not...

Hi all, I am currently finishing my master thesis including simulations and have run into an issue. I am running simulations through a "Monte Carlo" macro that was imported from another work book. After each run I receive the error message "Runtime error 9: subscript out of range". When I...

Hello I need to import a data table from SQL to Excel. I have the 2 queries in stored procedures (one that provides one part of the data table, and another provides the other half of the data in the table). What I want to do is for users to key in the start and end dates in Excel and the...

The code below works to remove duplicates: Option Explicit Sub test() Dim DIC As Scripting.Dictionary Set DIC = New Scripting.Dictionary Dim MyArray() As Variant Columns(3).ClearContents MyArray = Cells(1, 1).CurrentRegion.Value Dim n As Long For n = 1 To UBound(MyArray, 1)...

Excel 2016 I was hoping someone could give me some pointers please, i'm having some issues with the following and can't quite understand why. I have the following code Dim var1 as Variant, var2 as Variant var1 = Sheets("Sheet1").Range("R16:AC16") var2 = Sheets("Sheet2").Range("P4:P15")...

I want to store the SUB below in a module then to call it from anywhere so that I don't have to write the code over and over again... My problem is how/what to declare of the variables (in the SUB line) THEN when this sub is called, it will be as below: SUB SortSubtotal("client", "C2:C",3) SUB...

Dear All, I have few doubt in VBA. when ever I write the VBA code i am getting Auto Properties list after dot(.). for some cases i could not the list while writing the code. but there is no error. even if I run the code I am getting the result. there is no error in code. But why the Auto...

I am trying to auto-update label ("label9) from userform1 based on the description chosen from a combobox ("mt"). I have a worksheet ("Lists") that contains all the descriptions that populate a combobox in my userform1 starting at cell A1 and going down. Starting in cell C1, I have a...

Hello, I taught myself to make macros in Excel VBA and I have some gaps in my knowledge. I recently looked at optomising some of my code and learnt that you can use a variant array to store varing data types in the same array. Previously I used a string array for both text and numeric values...

Hi Guys, Do you know any way? The function is to Vlookup the array and return a name of the boss based on employees name. I tried to declare the Array as a public but this does not work. Maybe there is a way to pass a constant parameter to a function in a form of variant array? I tried...

Found this code to Find and Replace; Sub FindReplaceAll() 'PURPOSE: Find & Replace text/values throughout entire workbook 'SOURCE: www.TheSpreadsheetGuru.com Dim sht As Worksheet Dim fnd As Variant Dim rplc As Variant fnd = "April" rplc = "May" For Each sht In ActiveWorkbook.Worksheets...

Hey all, I have a macro that filters a table, and then fills out a few columns of another table with totals based on the filtered table. Most of the time, it works perfectly, and is tremendously much faster than a sumifs in the worksheet. Unfortunately, out of 160 iterations, it errors out...

In the code below, why am I getting a Type mismatch error on this line? a = rect.AreaItem(l, w) Standard Module: Option Explicit Sub Start() Dim l As Double Dim w As Double Dim rect As New clsRectangle l = 10 w = 20 rect.Area = l * w...

Hi Everybody, I am trying to get a list of the array elements in the intermediate window. The code is erroring (code 9) on the debug.Print line. The elements are being loaded into the array as I am getting the correct value for the variable x. Sub Data() Dim lr As Long Dim x As Long Dim i...

Standard module: Dim MyVar(1 To 10, 1 To 10) As MyClass Set MyVar(1 To 10, 1 To 10) = New MyClass MyVar(1, 1).Category = "Some category" MyVar(2, 1).Fruit = "Some fruit" MyClass: Private pCategory As Variant Private pFruit As Variant Public Property Get Category() As Variant...

I am receiving this error message when pulling data from the web using JSON Convert: Err.Raise 10001, "JSONConverter", json_ParseErrorMessage(json_String, json_Index, "Expecting 'STRING', 'NUMBER', null, true, false, '{', or '['") This is the VBA I have: Sub Dog_Form() Dim...