+ Reply to Thread
Results 1 to 5 of 5

Trouble reading values from a two dimensional array

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    245

    Trouble reading values from a two dimensional array

    Good afternoon -

    Spending some time today working on my VBA skills and struggling with two diensional arrays. Thanks in advance for any help.

    Simply put, I'm trying to compare values in column A with values in a 2D array. When there is a match, I want to turn the value in column A to a bold font. Here's my code and attached is my practice workbook.

    
    Sub Practice8()
    
    'THE PURPOSE IS TO COMPARE THE VALUES IN COLUMN A WITH THE _
    VALUES IN THE ARRAY (i.e., Sheet2, Range C1:D3) AND WHENEVER A _
    MATCH IS FOUND, TURN THE VALUE IN COLUMN A TO BOLD
    
    Dim Arr() As Variant
    Arr = Range("C1:D3")
    Dim r As Long
    Dim c As Long
    
    With Sheet2
    
        For r = 1 To UBound(Arr, 1)
            For c = 1 To UBound(Arr, 2)
                  
             Next c
            
        Next r
        
    Dim i As Long
    Dim Rows As Long
    
        For i = 1 To 4
    
            If Cells(i, 1) = (r) & (c) Then
            Cells.Font.Bold = True
                             
            End If
    
        Next i
    
    End With
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,964

    Re: Trouble reading values from a two dimensional array

    Option Explicit
    
    Sub Practice8()
    
    'THE PURPOSE IS TO COMPARE THE VALUES IN COLUMN A WITH THE _
    VALUES IN THE ARRAY (i.e., Sheet2, Range C1:D3) AND WHENEVER A _
    MATCH IS FOUND, TURN THE VALUE IN COLUMN A TO BOLD
    
        Dim Arr() As Variant
        Dim r As Long
        Dim c As Long
        Dim i As Long
        Dim Rows As Long
        
        Arr = Range("C1:D3")
        
        With Sheet2
            For i = 1 To 4                      '   rows of column A
                For r = 1 To UBound(Arr, 1)     '   rows of array
                        
                        If .Cells(i, 1).Value = Arr(r, 1) Or .Cells(i, 1).Value = Arr(r, 2) Then
                            .Cells(i, 1).Font.Bold = True
                        End If
                Next r
            Next i
        End With
    End Sub
    ** Notice the dots prefixing the Cells property
    Last edited by protonLeah; 03-07-2019 at 08:54 PM.
    Ben Van Johnson

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,279

    Re: Trouble reading values from a two dimensional array

    A different way:
    Option Explicit
    
    Sub array_compare()
        Dim c As Integer, r As Long, rws As Long, what, fnd
        With Sheet2
            r = 1: rws = .Range("C1").CurrentRegion.Rows.Count
            Do Until .Cells(r, "A").Value = ""
                c = 3: what = .Cells(r, "A").Value
                Do Until .Cells(1, c).Value = ""
                    fnd = Application.Match(what, .Range(.Cells(1, c), .Cells(rws, c)), 0)
                    If Not IsError(fnd) Then .Cells(r, "A").Font.Bold = True: Exit Do
                    c = c + 1
                Loop
                r = r + 1
            Loop
        End With
    End Sub

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Trouble reading values from a two dimensional array

    Not a good example to practice...

    You can do For Each Loop within an array when you only need to READ the elements, not EDIT.
    And it moves by dimension, so 1st C1:C3 and then D1:D3 unlike For Each Loop in a Range.

    Sub Practice8()
        Dim Arr() As Variant, e, r As Range
        Arr = Range("C1:D3")
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            For Each e In Arr
                If r.Value = e Then
                    r.Font.Bold = True
                    Exit For
                End If
            Next
        Next
    End Sub

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,359

    Re: Trouble reading values from a two dimensional array

    Maybe a bit of overkill for short lists but here's annother one.

    Turn 2D-array to 1D-array and use Application.Match on newly created array for matches.
    Sub test()
        Dim myarr, sn, i As Long, ii As Long, j As Long
        Dim cl As Range
        With Sheet2
            sn = Cells(1, 3).CurrentRegion.Value
            ReDim myarr(1 To UBound(sn) * 2): j = 1
            For i = 1 To UBound(sn)
                For ii = 1 To UBound(sn, 2)
                    If sn(i, ii) <> vbNullString Then myarr(j) = sn(i, ii): j = j + 1
                Next
            Next
            For Each cl In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
                If Not IsError(Application.Match(cl, myarr, 0)) Then cl.Font.Bold = True
            Next
        End With
    End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help converting one-dimensional array to multi-dimensional array
    By puzzlelover22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2016, 06:48 AM
  2. Parse Data from one dimensional array into a 2 dimensional array.
    By JapanDave in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-30-2016, 07:29 AM
  3. [SOLVED] Array formula: Automatically create a series of values for a one-dimensional matrix
    By bbozze in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2014, 07:48 AM
  4. [SOLVED] List most common text values from a two-dimensional array
    By alienss in forum Excel General
    Replies: 2
    Last Post: 10-11-2012, 09:53 AM
  5. Extract unique values and their count from a two dimensional Array
    By shahirslmn in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-17-2012, 09:37 PM
  6. Creating a 2-dimensional array from a 1-dimensional list
    By guywithcamera in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2008, 06:34 PM
  7. permutation of values in 2 dimensional array
    By bashir.zain@googlemail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2006, 03:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1