Matching and Merging CPU info between worksheets

Question

Goal: Transfer Sheet Test1 2nd Column to Sheet TestS 2nd Column.

Where:

For each CPU in Test1. If CPU name in Sheet Test1 match CPU name in sheet TestS, copy 2nd column of that CPU in Test1 and paste in 2nd column of that CPU in TestS.

Else, create new row with the unfound CPU name and 2nd column after the last CPU of sheet TestS.

Final look in TestS looks like:

This is my current working code where variables needs to be declared (string, integer etc):

   Sub automateCpu()

   Dim noofComps As Long
   noOfComps = Worksheets("Test1").Range("A1").CurrentRegion.Rows.Count-1
   Dim x As Long
   x = 0
   Dim d As Integer
   d = 0
   Sheets("Test1").Select
   Range("A1").Select
   Do While x > noOfComps
      Selection.Offset(1, 0).Select
      Dim curComputer As String
      curComputer = Selection.Value
      Dim test1Row As Integer
      d = d + 1
      test1Row = ActiveCell.Row

      Sheets("TestS").Select
      Range("A1").Select
      Dim y As Integer
      y = 0
      Dim z As Integer
      z = 0
      Dim LastRowSummary As integer
      LastRowSummary = Worksheets("TestS").Range("A1").CurrentRegion.Rows.Count - 1
      Do While y < LastRowSummary
           Selection.Offset(1, 0).Select
           If Selection.Value = curComputer Then
              z = 1
              Dim SummaryRow As Integer
              SummaryRow = ActiveCell.Row

              Sheets("Test1").Select
              Range("B" & test1Row).Select
              Dim curLogValue As Integer
              curLogValue = Selection.Value

              Sheets("TestS").Select
              Range("B" & SummaryRow).Select
              Selection.Value = curLogValue

              Sheets("Test1").Select
              Range("A" & test1Row).Select
              Exit Do
         End If
         y = y + 1
      Loop

      If z = 0 Then
         d = d + 1
         Selection.Offset(1, 0).Select

         Selection.Value = curComputer
         SummaryRow = ActiveCell.Row


         Sheets("Test1").Select
         Range("B" & test1Row).Select
         Dim curLogValue As Integer
         curLogValue = Selection.Value

         Sheets("TestS").Select
         Range("B" & SummaryRow).Select
         Selection.Value = curLogValue

         Sheets("Test1").Select
         Range("A" & test1Row).Select
      End If
      z = 0
      d = d + 1
      x = x + 1
   Loop

   End Sub

Pseudo Code:

1) Loop CPU in Test1

2) Find CPU in TestS by looping each computer

3) If CPU Matches for both Test1 & TestS paste 2nd Column

4) Else, create new row for new computer and 2nd column.

My question is how can I make this code more efficient? Or any alternate method that can reduce the amount of codes etc.

Answer 1

Three initial points:

1. Always use Option Explicit at the top of the module. Always!
2. Properly indent your code to help with readability and exposure of some of the logic.
3. Avoid using .Activate and .Select in your code. It is not necessary unless you want to bring something to the user's attention.

You have d, x, y, z but no real understand what they mean. Particularly d, which is not used anywhere except to be incremented!

Taking these points into account, you end up with:

Sub automateCpu()
Dim noofComps As Long
Dim test as Range
Dim testS as Range
Dim test1 as Range
    set test1 = Worksheets("Test1").Range("A1")
    noOfComps = testRange.CurrentRegion.Rows.Count-1
Dim x As Long
    x = 0
Dim d As Integer
    d = 0
    Do While x > noOfComps
        Set test1 = test1.Offset(1,0)
        Dim curComputer As String
        curComputer = testRange.Value
        Dim test1Row As Integer
        d = d + 1
        test1Row = test1.Row
        Dim testS as Range 
        Set testS = Sheets("TestS").Range("A1") ' Why "Sheets" instead of "Worksheets" as in the other case? Consistency!
        Dim y As Integer
        y = 0
        Dim z As Integer
        z = 0
        Dim LastRowSummary As integer
        LastRowSummary = testS.CurrentRegion.Rows.Count - 1
        Do While y < LastRowSummary
            Set testS = testSRange.Offset(1, 0)
            If testS.Value = curComputer Then
                z = 1
                Dim SummaryRow As Integer
                SummaryRow = testS.Row
                Set test1 = Sheets("Test1").Range("B" & test1Row)
              Dim curLogValue As Integer
              curLogValue = test1.Value

              Set testS = Sheets("TestS").Range("B" & SummaryRow)
              testS.Value = curLogValue

              Set test1 = Sheets("Test1").Range("A" & test1Row)
              Exit Do
         End If
         y = y + 1
      Loop

      If z = 0 Then
         d = d + 1
         set test1 = test1.Offset(1, 0)
         test1.Value = curComputer
         SummaryRow = test1.Row


         set test1 = Sheets("Test1").Range("B" & test1Row)
         Dim curLogValue As Integer
         curLogValue = test1.Value

         Set testS = Sheets("TestS").Range("B" & SummaryRow)
         testS.Value = curLogValue

         Set test1 = Sheets("Test1").Range("A" & test1Row)
      End If
      z = 0
      d = d + 1
      x = x + 1
   Loop
   End Sub

But, looking at the code, you can see that there is a lot of shuffling of data into temporary variables. We can also clean this up with direct assignments.

Sub automateCpu()
Dim noofComps As Long
Dim test as Range
Dim testSA as Range
Dim test1A as Range
Dim testSB as Range
Dim test1B as Range
    set test1A = Worksheets("Test1").Range("A1")
    noOfComps = testRange.CurrentRegion.Rows.Count-1
Dim x As Long
    x = 0
    Do While x > noOfComps
        Set test1A = test1A.Offset(1,0)
        Dim curComputer As String
        curComputer = testRange.Value
        Dim test1Row As Integer
        test1Row = test1A.Row
        Set testSA = Sheets("TestS").Range("A1") ' Why "Sheets" instead of "Worksheets" as in the other case? Consistency!
        Dim y As Integer
        y = 0
        Dim z As Integer
        z = 0
        Dim LastRowSummary As integer
        LastRowSummary = testS.CurrentRegion.Rows.Count - 1
        Do While y < LastRowSummary
            Set testSA = testSRange.Offset(1, 0)
            If testSA.Value = curComputer Then
                z = 1
                Dim SummaryRow As Integer
                SummaryRow = testSA.Row
                Set test1B = Sheets("Test1").Range("B" & test1Row)
                Set testSB = Sheets("TestS").Range("B" & SummaryRow)
                testSB.Value = test1B.Value

                Set test1A = Sheets("Test1").Range("A" & test1Row)
                Exit Do
            End If
            y = y + 1
         Loop

         If z = 0 Then
             set test1A = test1A.Offset(1, 0)
             test1.Value = curComputer
             SummaryRow = test1A.Row

             set test1B = Sheets("Test1").Range("B" & test1Row)
             Set testSB = Sheets("TestS").Range("B" & SummaryRow)
             testSB.Value = test1B.Value

             Set test1A = Sheets("Test1").Range("A" & test1Row)
         End If
         z = 0
         x = x + 1
     Loop
End Sub

Probably still some tidying up to do - but this gives the idea.