+ Reply to Thread
Results 1 to 4 of 4

Comparing two Arrays

Hybrid View

  1. 02-24-2017, 09:01 AM #1
    hopner
    hopner is offline
    Registered User
    Join Date
    08-22-2014
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    1

    Comparing two Arrays

    Hi

    I have made some code that works perfect, but i was wondering how you guys would speed it up a bit and simplify it. I couldn't figure out how to compare the to arrays DataRange and DRReal. Hope you can help me!
    Sub Makro1()

Dim DataRange As Variant
Dim DRReal As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As String
DataRange = Range("A1:C20").Value ' read all the values at once from the Excel grid, put into an array
DRReal = Range("N1:Q18").Value


  For Icol = 1 To 3
  If Icol > 3 Then GoTo Hurtig
  For Irow = 1 To 20
  MyVar = DataRange(Irow, Icol)
  If MyVar = "" Then GoTo Videre
'Gender
  If MyVar = Range("Q2").Value Then
    DataRange(Irow, Icol) = Range("N2").Value
  ElseIf MyVar = Range("Q3").Value Then
    DataRange(Irow, Icol) = Range("N3").Value
    
'Region
ElseIf MyVar = Range("P2").Value Then
    DataRange(Irow, Icol) = Range("N2").Value
ElseIf MyVar = Range("P3").Value Then
    DataRange(Irow, Icol) = Range("N3").Value
ElseIf MyVar = Range("P4").Value Then
    DataRange(Irow, Icol) = Range("N4").Value
ElseIf MyVar = Range("P5").Value Then
    DataRange(Irow, Icol) = Range("N5").Value
ElseIf MyVar = Range("P6").Value Then
    DataRange(Irow, Icol) = Range("N6").Value

'Income
    ElseIf MyVar = Range("O2").Value Then
    DataRange(Irow, Icol) = Range("N2").Value
    ElseIf MyVar = Range("O3").Value Then
    DataRange(Irow, Icol) = Range("N3").Value
    ElseIf MyVar = Range("O4").Value Then
    DataRange(Irow, Icol) = Range("N4").Value
    ElseIf MyVar = Range("O5").Value Then
    DataRange(Irow, Icol) = Range("N5").Value
    ElseIf MyVar = Range("O6").Value Then
    DataRange(Irow, Icol) = Range("N6").Value
    ElseIf MyVar = Range("O7").Value Then
    DataRange(Irow, Icol) = Range("N7").Value
    ElseIf MyVar = Range("O8").Value Then
    DataRange(Irow, Icol) = Range("N8").Value
    ElseIf MyVar = Range("O9").Value Then
    DataRange(Irow, Icol) = Range("N9").Value
    ElseIf MyVar = Range("O10").Value Then
    DataRange(Irow, Icol) = Range("N10").Value
    ElseIf MyVar = Range("O11").Value Then
    DataRange(Irow, Icol) = Range("N11").Value
    ElseIf MyVar = Range("O12").Value Then
    DataRange(Irow, Icol) = Range("N12").Value
    ElseIf MyVar = Range("O13").Value Then
    DataRange(Irow, Icol) = Range("N13").Value
    ElseIf MyVar = Range("O14").Value Then
    DataRange(Irow, Icol) = Range("N14").Value
    ElseIf MyVar = Range("O15").Value Then
    DataRange(Irow, Icol) = Range("N15").Value
    ElseIf MyVar = Range("O16").Value Then
    DataRange(Irow, Icol) = Range("N16").Value
    ElseIf MyVar = Range("O17").Value Then
    DataRange(Irow, Icol) = Range("N17").Value
    ElseIf MyVar = Range("O18").Value Then
    DataRange(Irow, Icol) = Range("N18").Value
    End If
            
Next Irow

Videre:
Next Icol

Hurtig:
Range("A1:C20").Value = DataRange ' writes all the results back to the range at once

End Sub
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
    Last edited by 6StringJazzer; 02-24-2017 at 10:15 AM. Reason: code tags
    Reply With QuoteRegister To Reply
  2. 02-24-2017, 10:27 AM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2404
    Posts
    24,777

    Re: Comparing two Arrays

    I couldn't figure out how to compare the to arrays DataRange and DRReal.
    What is it you want to compare about them, and what action do you want to take as a result of the comparison?

    I notice you have a couple of GoTo statements. I am a big opponent of those and will suggest an alternative once I understand your comparison issue.

    In one case you have

        For Icol = 1 To 3
      If Icol > 3 Then GoTo Hurtig
    But Icol will never be greater than 3. I don't know why you have that If/GoTo there.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 02-24-2017, 10:33 AM #3
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2404
    Posts
    24,777

    Re: Comparing two Arrays

    Here is a streamlined version of your code, although it won't be any faster. I suggest you test it to make sure it still does the same thing. I can provide more help when I get your explanation.

    Option Explicit

Sub Makro1()

   Dim DataRange As Variant
   Dim DRReal As Variant
   Dim Irow As Long
   Dim Icol As Integer
   Dim MyVar As String
   Dim C As Range ' cell
   DataRange = Range("A1:C20").Value ' read all the values at once from the Excel grid, put into an array
   DRReal = Range("N1:Q18").Value
   
   
   
   For Icol = 1 To 3
      For Irow = 1 To 20
      
         MyVar = DataRange(Irow, Icol)
         If MyVar = "" Then Exit For
         
         'Gender
         For Each C In Range("Q2:Q3")
            If MyVar = C.Value Then DataRange(Irow, Icol) = C.Offset(0, -3)
         Next C
        
         'Region
         For Each C In Range("P2:P6")
            If MyVar = C.Value Then DataRange(Irow, Icol) = C.Offset(0, -2)
         Next C
        
         'Income
         For Each C In Range("O2:O18")
            If MyVar = C.Value Then DataRange(Irow, Icol) = C.Offset(0, -1)
         Next C
      
      Next Irow
      

   Next Icol
   
   Range("A1:C20").Value = DataRange ' writes all the results back to the range at once

End Sub
    Reply With QuoteRegister To Reply
  4. 02-24-2017, 11:47 AM #4
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2404
    Posts
    24,777

    Re: Comparing two Arrays

    Here is an even more streamlined version but still need to resolve your array comparison issue.

    I have not tested this but would be happy to do so if I had your file.

    Option Explicit

Sub Makro1()

   Dim DataRange As Variant
   Dim DRReal As Variant
   Dim Irow As Long
   Dim Icol As Integer
   Dim MyVar As String
   Dim C As Range ' cell
   DataRange = Range("A1:C20").Value ' read all the values at once from the Excel grid, put into an array
   DRReal = Range("N1:Q18").Value
   
   For Icol = 1 To 3
      For Irow = 1 To 20
      
         MyVar = DataRange(Irow, Icol)
         If MyVar = "" Then Exit For
         
         'Gender, Region, Income
         For Each C In Range("Q2:Q3,P2:P6,O2:O18")
            If MyVar = C.Value Then DataRange(Irow, Icol) = Cells(C.Row, "N")
         Next C
        
      Next Irow
      

   Next Icol
   
   Range("A1:C20").Value = DataRange ' writes all the results back to the range at once

End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help comparing 2 arrays
    By jfoerch in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2015, 12:14 PM
  2. [SOLVED] Help Comparing Arrays (to Other Arrays)
    By Polite Master in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-20-2013, 01:03 PM
  3. Comparing arrays....
    By Bedlam in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-02-2011, 03:15 PM
  4. [SOLVED] Comparing two arrays what the best most efficient way
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2011, 08:19 AM
  5. Comparing arrays
    By JoshuaSQ in forum Excel General
    Replies: 8
    Last Post: 02-13-2010, 01:47 AM
  6. [SOLVED] Comparing 2 arrays
    By [email protected] in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2006, 10:35 PM
  7. Comparing Arrays
    By TangentMemory in forum Excel General
    Replies: 2
    Last Post: 05-13-2005, 01:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1