1. D

    How to use VBA to color cell interiors yellow if cell.value in Address table matches cell.value in Postal Town table?

    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...
  2. C

    Help fixing 'Subscript out of range' error

    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...
  3. T

    With command

    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...
  4. T

    Vlookup range as a collection

    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 =...
  5. kelly mort

    Show alert when lookup finds more than 10 rows

    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...
  6. D

    Color cells in a Range if it matches strings in array found in another range

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

    Curious problem with a find and replace macro?

    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...
  8. T

    Omit first row of array

    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
  9. T

    Remove duplicates, return multiple columns

    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...
  10. T


    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...
  11. B

    Sortng Sheets in a List box Sheet navigator

    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...
  12. T


    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 =...
  13. B

    Writing Array to Range

    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"...
  14. T

    Array with a single value only

    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
  15. T

    Transfering dictionary to array

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

    Listbox displaying more decimal places than the worksheet it is referencing

    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...
  17. T

    Passing parameters in a class

    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")...
  18. H

    Multi Dimensional Dynamic Array query

    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...
  19. T

    Pass more than one argument

    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...
  20. Jaafar Tribak

    Question - Constant to Array function

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

Some videos you may like

This Week's Hot Topics

  • SUMPRODUCT active link formula
    Hi guys i have sumproduct formula for counting two range of number, i want count active cells of formula that linked to another sheet...
  • Block certain cells in condition met in cell A
    Hi there, trying to figure out step by step how to build macros and learn more. Now given that my other code was a mess, I figured I would...
    Hi, Below formula works well, =(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O28='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P28='PRICE...
  • Match data from 3 columns to return data from the correct 4th column
    Hi there! I'm trying to have a cell auto-populate the data in a cell based on the data entered in 3 other cells. I've pasted a copy of the...
  • VLookup
    Hi everyone, I need to find the value from one sheet to another. So in Sheet A Field N5 I have a value (Spark) I want to find Spark on the Sheet...
  • Defining a range
    Private Sub Worksheet_Calculate() Dim Xrg As Range Set Xrg = Range("K1") If Not Intersect(Xrg, Range("K1")) Is Nothing Then MsgBox...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back