myarray

Hi Folks, I'm looking at a variety of ways (using VBA) to colour cells in an address table whose values match values postal towns in another table. There are two tables: AddrTable (contains 10 columns) PostalTownTable (one column) As the address data in AddrTable can run into many thousand...

I'm trying to teach myself about arrays and can't seem to figure this one out. (many Google searches) I have the following code that I'm getting a 'subscript out of range' error: <subscript out="" of="" range="">Dim LastRow As Integer Dim MyArray As Variant Dim i As Integer With...

I have values in cells A1 to B2 on Sheet1. I want to read those values into an array and paste onto Sheet2. Why is it that this works: Dim MyArray() As Variant MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value Sheet2.Cells(1, 1).CurrentRegion.Delete...

In a vlookup, can the range be a collection? Say I have only two values in cells A1 and A2. If the range is an array, it works: Dim MyArray() As Variant MyArray() = Cells(1, 1).CurrentRegion.Value Dim g As Integer g = 2 Dim j As Variant j =...

Hi I have this lookup code and I need a little adjustments to do my bidding. I want to avoid loading the listbox when the data exceeds 10 rows. I have been starring at it for a while. I can't figure out what to place and where to place it. Sub Lookup() Me.lstView.ColumnCount = 6...

I have the following code that basically works fine. The purpose is to highlight any cells in the "Lookup" sheet that match any of the strings found in the specified range in the "Fails" sheet. The problem is that this method won't highlight a second or third etc. occurrence of the string in...

Hi Folks, I have a column of data (column H) headed [customer reference]. Customer reference data is populated by users when they book a courier online. They have to input a client customer number (3 digits), a sales order number and a check digit, each element separated by a hashtag (so...

If my data has two columns in columns A and B but I just want column A, I can write this: MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Resize(, 1).Value What can I do if I want to populate MyArray not from cell A1 but from cell A2? I don't want to have to loop. Thanks

This code will remove duplicates from column A, so if my data was: a a b c d it will return: a b c d Here is the code: Dim DIC As Object Set DIC = CreateObject("Scripting.Dictionary") Dim MyArray() As Variant MyArray() = Sheet1.Cells(1...

Can someone please explain why this is fast: Dim DIC As Object Set DIC = CreateObject("Scripting.Dictionary") Dim MyArray() As Variant MyArray() = wksOutputForecast.Cells(2, 1).CurrentRegion.Resize(, 1).Value Dim n As Long For n = 1 To UBound(MyArray...

I have a list box that I have on a userform that lists all the workbook sheets and then goes to the sheet that is selected. I'm trying to alphabetically sort the sheets in the sheet box. I found a code snippet on the internet but I'm not sure where to call or put the macro. Here are the code...

Normally I would use On Error but I want to find out why this doesn't work: In column A, put some values in cell A1 to A6, ensuring at least one value is zero. Dim MyArray() As Variant MyArray() = Cells(1, 1).CurrentRegion.Value Dim i, j For i = 1 to 6 j =...

Afternoon I've been attempting to understand arrays and in particular how to write an array (see below) to a range. This code does not include the last element "July" - not sure why? Thanks for any help/explanation Dim arr As Variantarr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun"...

The code below works if there is data in A1 and a neighbouring cell. Dim MyArray() As Variant MyArray() = Cells(1, 1).CurrentRegion.Value However, if there is only a value in A1, it crashes. How can I make it work when there is only one value? Thanks

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)...

Hello, I am trying to create a listbox that displays a table from a worksheet ('Liquid Charge Control Sheet') using the code below. The worksheet displays only one decimal place (i.e. 100.5) but when I reference that cell in the listbox it displays "100.50000000000000". Can someone show me how...

Is it better to use properties to pass parameters in a class, (as in Method 1 below) or not? Method 1: Sheet1: Private Sub UpdateTable() Dim MyUpdateTable As ClsUpdateTable Set MyUpdateTable = New ClsUpdateTable Set MyUpdateTable.Period = Me.Range("Now")...

I know below is possible - Sub test() Dim myArray As Variant myArray = Sheets("Data").Range("A1:Z1").Value End Sub is it possible to ReDim Preserve myArray and insert a new row in one go as done above. My requirement is to make a Multidimensional dynamic array but want to insert the rows in...

If I want to pass ONE argument to a SubRoutine, I can do this: Dim MyArray() as Variant MyArray() = Array("Apples", "Oranges") Dim i As Integer For i = LBound(MyArray(), 1) To UBound(MyArray(), 1) Call SomeSub(Arg:=i) Next i What if SomeSub requires TWO arguments? How can I...

Hi dear forum members. Consider the following: Const MyConst = "A" & "B" & "C" & "D" Sub Test() Dim Myarray() As Variant Myarray = Array(MyConst) Debug.Print Myarray(0); Myarray(1); Myarray(2); Myarray(3) End Sub Of course, the above code won't work but you get the idea ...