3
\$\begingroup\$

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

Test1

TestS

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:

TestS

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.

\$\endgroup\$

migrated from superuser.com Jul 12 at 2:18

This question came from our site for computer enthusiasts and power users.

2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ That's some funky indenting on the Dim x As Long and x = 0 lines, but I kinda like it! Of course, x=0 immediately after the Dim is redundant in VBA since VBA initializes variables and doesn't leave them Null as C (among others) would. \$\endgroup\$ – FreeMan Jul 12 at 11:24
  • \$\begingroup\$ @FreeMan: - yes, leaving the implicit assignment (x=0) is fine and I did consider taking that approach, but personally, I like to be explicit wherever possible because it reduces the avenues for bugs in complex code. Plus, the OP is learning so being explicit in the first instance is better. \$\endgroup\$ – AJD Jul 13 at 4:03
  • \$\begingroup\$ @FreeMan: In addition, the implicit assignment works outside the loop, but inside the loop it may have a different effect. \$\endgroup\$ – AJD Jul 13 at 4:05
  • \$\begingroup\$ Both valid points. "Write what you mean. Mean what you write." \$\endgroup\$ – FreeMan Jul 15 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.