Edit All

Pos macro

Last Edited By Krjb Donovan

Last Updated: Mar 05, 2014 09:50 PM GMT

QuestionEdit

I'm making an interim POS system to record data about what is bought by what sort of demographic in a shop. I have written this Macro so far, but think it can sped up a little. Can you help?

Sub Macro1()

Dim rw As Long With Worksheets("Records")

rw = .Cells(.Rows.Count, "A").End(xlUp).Row
If rw < 3 Then
  rw = 0
Else
  rw = rw - 1
End If

' VIP customer Item 1
If Worksheets("Sales Form").Range("TorV_Enter") = 1 Then
   
   .Range("A2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B7")
   .Range("B2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D7")
   .Range("C2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F7")
   .Range("J2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N7")
   .Range("K2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P7")
   .Range("L2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R7")
   .Range("M2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   
   'VIP customer item 2
   If Worksheets("Sales Form").Range("D9") = 0 Then
       .Range("A3").Offset(rw, 0).Value = ""
   Else
       .Range("A3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B9")
       .Range("B3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D9")
       .Range("C3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F9")
       .Range("J3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N9")
       .Range("K3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P9")
       .Range("L3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R9")
       .Range("M3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
       .Range("N3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
       .Range("P3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
       .Range("Q3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'VIP customer item 3
   If Worksheets("Sales Form").Range("D11") = 0 Then
       .Range("A4").Offset(rw, 0).Value = ""
   Else
   .Range("A4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B11")
   .Range("B4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D11")
   .Range("C4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F11")
   .Range("J4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N11")
   .Range("K4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P11")
   .Range("L4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R11")
   .Range("M4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'VIP customer item 4
   If Worksheets("Sales Form").Range("D13") = 0 Then
       .Range("A5").Offset(rw, 0).Value = ""
   Else
   .Range("A5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B13")
   .Range("B5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D13")
   .Range("C5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F13")
   .Range("J5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N13")
   .Range("K5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P13")
   .Range("L5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R13")
   .Range("M5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'VIP customer item 5
   If Worksheets("Sales Form").Range("D15") = 0 Then
       .Range("A6").Offset(rw, 0).Value = ""
   Else
   .Range("A6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B15")
   .Range("B6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D15")
   .Range("C6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F15")
   .Range("J6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N15")
   .Range("K6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P15")
   .Range("L6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R15")
   .Range("M6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'VIP customer item 6
   If Worksheets("Sales Form").Range("D17") = 0 Then
       .Range("A7").Offset(rw, 0).Value = ""
   Else
   .Range("A7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B17")
   .Range("B7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D17")
   .Range("C7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F17")
   .Range("J7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N17")
   .Range("K7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P17")
   .Range("L7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R17")
   .Range("M7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'VIP customer item 7
   If Worksheets("Sales Form").Range("D19") = 0 Then
       .Range("A8").Offset(rw, 0).Value = ""
   Else
   .Range("A8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B19")
   .Range("B8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D19")
   .Range("C8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F19")
   .Range("J8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N19")
   .Range("K8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P19")
   .Range("L8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R19")
   .Range("M8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'VIP customer item 8
   If Worksheets("Sales Form").Range("D21") = 0 Then
       .Range("A9").Offset(rw, 0).Value = ""
   Else
   .Range("A9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B21")
   .Range("B9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D21")
   .Range("C9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F21")
   .Range("J9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N21")
   .Range("K9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P21")
   .Range("L9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R21")
   .Range("M9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
   .Range("N9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
   .Range("P9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
   .Range("Q9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
   End If
   
   'Tourist Item 1

Else

   .Range("A2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B7")
   .Range("B2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("C7")
   .Range("C2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F7")
   .Range("J2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F7")
   .Range("K2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N7")
   .Range("L2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R7")
   .Range("M2").Offset(rw, 0).Value = "Tourist"
   .Range("N2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
   .Range("P2").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   
   'Tourist Item 2
   If Worksheets("Sales Form").Range("D9") = 0 Then
       .Range("A3").Offset(rw, 0).Value = ""
   Else
       .Range("A3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B9")
       .Range("B3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D9")
       .Range("C3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F9")
       .Range("J3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N9")
       .Range("K3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P9")
       .Range("L3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R9")
       .Range("M3").Offset(rw, 0).Value = "Tourist"
       .Range("N3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P3").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   End If
   
   'Tourist Item 3
   If Worksheets("Sales Form").Range("D11") = 0 Then
       .Range("A4").Offset(rw, 0).Value = ""
   Else
       .Range("A4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B11")
       .Range("B4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D11")
       .Range("C4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F11")
       .Range("J4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N11")
       .Range("K4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P11")
       .Range("L4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R11")
       .Range("M4").Offset(rw, 0).Value = "Tourist"
       .Range("N4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P4").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   End If
   
   'Tourist Item 4
   If Worksheets("Sales Form").Range("D13") = 0 Then
       .Range("A5").Offset(rw, 0).Value = ""
   Else
       .Range("A5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B13")
       .Range("B5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D13")
       .Range("C5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F13")
       .Range("J5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N13")
       .Range("K5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P13")
       .Range("L5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R13")
       .Range("M5").Offset(rw, 0).Value = "Tourist"
       .Range("N5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P5").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   End If
   
   'Tourist Item 5
   If Worksheets("Sales Form").Range("D15") = 0 Then
       .Range("A6").Offset(rw, 0).Value = ""
   Else
       .Range("A6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B15")
       .Range("B6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D15")
       .Range("C6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F15")
       .Range("J6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N15")
       .Range("K6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P15")
       .Range("L6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R15")
       .Range("M6").Offset(rw, 0).Value = "Tourist"
       .Range("N6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P6").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   End If
   
   'Tourist Item 6
   If Worksheets("Sales Form").Range("D17") = 0 Then
       .Range("A7").Offset(rw, 0).Value = ""
   Else
       .Range("A7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B17")
       .Range("B7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D17")
       .Range("C7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F17")
       .Range("J7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N17")
       .Range("K7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P17")
       .Range("L7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R17")
       .Range("M7").Offset(rw, 0).Value = "Tourist"
       .Range("N7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P7").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   End If
   
   'Tourist Item 7
   If Worksheets("Sales Form").Range("D19") = 0 Then
       .Range("A8").Offset(rw, 0).Value = ""
   Else
       .Range("A8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B19")
       .Range("B8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D19")
       .Range("C8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F19")
       .Range("J8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N19")
       .Range("K8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P19")
       .Range("L8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R19")
       .Range("M8").Offset(rw, 0).Value = "Tourist"
       .Range("N8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P8").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_ENter")
   End If
   
   'Tourist Item 8
   If Worksheets("Sales Form").Range("D21") = 0 Then
       .Range("A9").Offset(rw, 0).Value = ""
   Else
       .Range("A9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("B21")
       .Range("B9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("D21")
       .Range("C9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("F21")
       .Range("J9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("N21")
       .Range("K9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("P21")
       .Range("L9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("R21")
       .Range("M9").Offset(rw, 0).Value = "Tourist"
       .Range("N9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
       .Range("P9").Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
   End If

End If

End With

   Worksheets("Sales Form").Range("D2").ClearContents
   Worksheets("Sales Form").Range("D4").ClearContents
   Worksheets("Sales Form").Range("F4").ClearContents
   Worksheets("Sales Form").Range("D7").ClearContents
   Worksheets("Sales Form").Range("D9").ClearContents
   Worksheets("Sales Form").Range("D11").ClearContents
   Worksheets("Sales Form").Range("D13").ClearContents
   Worksheets("Sales Form").Range("D15").ClearContents
   Worksheets("Sales Form").Range("D17").ClearContents
   Worksheets("Sales Form").Range("D19").ClearContents
   Worksheets("Sales Form").Range("D21").ClearContents
   Worksheets("Sales Form").Range("R11").ClearContents
   Worksheets("Sales Form").Range("R13").ClearContents
   Worksheets("Sales Form").Range("R15").ClearContents
   Worksheets("Sales Form").Range("R17").ClearContents
   Worksheets("Sales Form").Range("R19").ClearContents
   Worksheets("Sales Form").Range("R21").ClearContents

End Sub

AnswerEdit

Looks like your code can be replaced with this.

Please run the code on a copy of your file so you can easily compare results AND you don't mess up any data should I have made a mistake.

Sub Macro1()

   Dim rw As Long
   Dim lCt As Long
   'Turn off calc and screen to speed things up
   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False
   With Worksheets("Records")
       rw = .Cells(.Rows.Count, "A").End(xlUp).Row
       If rw < 3 Then
           rw = 0
       Else
           rw = rw - 1
       End If

       ' VIP customer Items
       If Worksheets("Sales Form").Range("TorV_Enter") = 1 Then
           For lCt = 2 To 9
               If lCt <> 2 And Worksheets("Sales Form").Range("D" & 9 + 2 * (lCt - 1)) = 0 Then
                   .Range("A" & lCt).Offset(rw, 0).Value = ""
               Else
                   .Range("A" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("B" & 5 + 2 * (lCt - 1))
                   .Range("B" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("D" & 5 + 2 * (lCt - 1))
                   .Range("C" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("F" & 5 + 2 * (lCt - 1))
                   .Range("J" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("N" & 5 + 2 * (lCt - 1))
                   .Range("K" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("P" & 5 + 2 * (lCt - 1))
                   .Range("L" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("R" & 5 + 2 * (lCt - 1))
                   .Range("M" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Customer_Enter")
                   .Range("N" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Nationality_Find")
                   .Range("P" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Sex_Find")
                   .Range("Q" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("V_Discount_Find")
               End If
           Next

       Else
           'Tourist Items
           For lCt = 2 To 9
               If lCt <> 2 And Worksheets("Sales Form").Range("D" & 9 + 2 * (lCt - 1)) = 0 Then
                   .Range("A" & lCt).Offset(rw, 0).Value = ""
               Else
               .Range("A" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("B" & 5 + 2 * (lCt - 1))
               .Range("B" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("D" & 5 + 2 * (lCt - 1))
               .Range("C" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("F" & 5 + 2 * (lCt - 1))
               .Range("J" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("N" & 5 + 2 * (lCt - 1))
               .Range("K" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("P" & 5 + 2 * (lCt - 1))
               .Range("L" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("R" & 5 + 2 * (lCt - 1))
               .Range("M" & lCt).Offset(rw, 0).Value = "Tourist"
               .Range("N" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Nationality_Enter")
               .Range("P" & lCt).Offset(rw, 0).Value = Worksheets("Sales Form").Range("T_Sex_Enter")
           End If
           Next
       End If

   End With
   Worksheets("Sales Form").Range("D2").ClearContents
   Worksheets("Sales Form").Range("D4").ClearContents
   Worksheets("Sales Form").Range("F4").ClearContents
   Worksheets("Sales Form").Range("D7").ClearContents
   Worksheets("Sales Form").Range("D9").ClearContents
   Worksheets("Sales Form").Range("D11").ClearContents
   Worksheets("Sales Form").Range("D13").ClearContents
   Worksheets("Sales Form").Range("D15").ClearContents
   Worksheets("Sales Form").Range("D17").ClearContents
   Worksheets("Sales Form").Range("D19").ClearContents
   Worksheets("Sales Form").Range("D21").ClearContents
   Worksheets("Sales Form").Range("R11").ClearContents
   Worksheets("Sales Form").Range("R13").ClearContents
   Worksheets("Sales Form").Range("R15").ClearContents
   Worksheets("Sales Form").Range("R17").ClearContents
   Worksheets("Sales Form").Range("R19").ClearContents
   Worksheets("Sales Form").Range("R21").ClearContents
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True

End Sub

GETTING STARTED
Invite Others
Create