Is there a better way to write this code

J

johnmpc

Board Regular
Joined
Oct 19, 2020
Messages
108
Office Version
  1. 365
Platform
  1. Windows
This code works but i'm guessing its probably not the most efficient way or fastest running way to write it.
It copies specific cells from a sheet into a 2nd sheet. putting them on the same row as the customer name.
I also have the similar code for retrieving the information back to the same cells.

VBA Code: 
Sub Customerdatapull()

Dim c As Range
    Dim CustomerREF As Range
     Set CustomerREF = Sheets("Customers").Range("A:A").Find(Sheets("order template").Range("B6").Value)
    If Not CustomerREF Is Nothing Then
    Sheets("Order Template").Range("A31").Value = CustomerREF.Offset(, 10)
    Sheets("Order Template").Range("D31").Value = CustomerREF.Offset(, 11)
    Sheets("Order Template").Range("F31").Value = CustomerREF.Offset(, 12)
    Sheets("Order Template").Range("I31").Value = CustomerREF.Offset(, 13)
    Sheets("Order Template").Range("AB31").Value = CustomerREF.Offset(, 14)
    Sheets("Order Template").Range("AC31").Value = CustomerREF.Offset(, 15)
    Sheets("Order Template").Range("AD31").Value = CustomerREF.Offset(, 16)
    Sheets("Order Template").Range("AE31").Value = CustomerREF.Offset(, 17)
    Sheets("Order Template").Range("AF31").Value = CustomerREF.Offset(, 18)
    Sheets("Order Template").Range("AG31").Value = CustomerREF.Offset(, 19)
    Sheets("Order Template").Range("A32").Value = CustomerREF.Offset(, 20)
    Sheets("Order Template").Range("D32").Value = CustomerREF.Offset(, 21)
    Sheets("Order Template").Range("F32").Value = CustomerREF.Offset(, 22)
    Sheets("Order Template").Range("I32").Value = CustomerREF.Offset(, 23)
    Sheets("Order Template").Range("AB32").Value = CustomerREF.Offset(, 24)
    Sheets("Order Template").Range("AC32").Value = CustomerREF.Offset(, 25)
    Sheets("Order Template").Range("AD32").Value = CustomerREF.Offset(, 26)
    Sheets("Order Template").Range("AE32").Value = CustomerREF.Offset(, 27)
    Sheets("Order Template").Range("AF32").Value = CustomerREF.Offset(, 28)
    Sheets("Order Template").Range("AG32").Value = CustomerREF.Offset(, 29)
    Sheets("Order Template").Range("A33").Value = CustomerREF.Offset(, 30)
    Sheets("Order Template").Range("D33").Value = CustomerREF.Offset(, 31)
    Sheets("Order Template").Range("F33").Value = CustomerREF.Offset(, 32)
    Sheets("Order Template").Range("I33").Value = CustomerREF.Offset(, 33)
    Sheets("Order Template").Range("AB33").Value = CustomerREF.Offset(, 34)
    Sheets("Order Template").Range("AC33").Value = CustomerREF.Offset(, 35)
    Sheets("Order Template").Range("AD33").Value = CustomerREF.Offset(, 36)
    Sheets("Order Template").Range("AE33").Value = CustomerREF.Offset(, 37)
    Sheets("Order Template").Range("AF33").Value = CustomerREF.Offset(, 38)
    Sheets("Order Template").Range("AG33").Value = CustomerREF.Offset(, 39)
    Sheets("Order Template").Range("A34").Value = CustomerREF.Offset(, 40)
    Sheets("Order Template").Range("D34").Value = CustomerREF.Offset(, 41)
    Sheets("Order Template").Range("F34").Value = CustomerREF.Offset(, 42)
    Sheets("Order Template").Range("I34").Value = CustomerREF.Offset(, 43)
    Sheets("Order Template").Range("AB34").Value = CustomerREF.Offset(, 44)
    Sheets("Order Template").Range("AC34").Value = CustomerREF.Offset(, 45)
    Sheets("Order Template").Range("AD34").Value = CustomerREF.Offset(, 46)
    Sheets("Order Template").Range("AE34").Value = CustomerREF.Offset(, 47)
    Sheets("Order Template").Range("AF34").Value = CustomerREF.Offset(, 48)
    Sheets("Order Template").Range("AG34").Value = CustomerREF.Offset(, 49)
    Sheets("Order Template").Range("A35").Value = CustomerREF.Offset(, 50)
    Sheets("Order Template").Range("D35").Value = CustomerREF.Offset(, 51)
    Sheets("Order Template").Range("F35").Value = CustomerREF.Offset(, 52)
    Sheets("Order Template").Range("I35").Value = CustomerREF.Offset(, 53)
    Sheets("Order Template").Range("AB35").Value = CustomerREF.Offset(, 54)
    Sheets("Order Template").Range("AC35").Value = CustomerREF.Offset(, 55)
    Sheets("Order Template").Range("AD35").Value = CustomerREF.Offset(, 56)
    Sheets("Order Template").Range("AE35").Value = CustomerREF.Offset(, 57)
    Sheets("Order Template").Range("AF35").Value = CustomerREF.Offset(, 58)
    Sheets("Order Template").Range("AG35").Value = CustomerREF.Offset(, 59)
    Sheets("Order Template").Range("A36").Value = CustomerREF.Offset(, 60)
    Sheets("Order Template").Range("D36").Value = CustomerREF.Offset(, 61)
    Sheets("Order Template").Range("F36").Value = CustomerREF.Offset(, 62)
    Sheets("Order Template").Range("I36").Value = CustomerREF.Offset(, 63)
    Sheets("Order Template").Range("AB36").Value = CustomerREF.Offset(, 64)
    Sheets("Order Template").Range("AC36").Value = CustomerREF.Offset(, 65)
    Sheets("Order Template").Range("AD36").Value = CustomerREF.Offset(, 66)
    Sheets("Order Template").Range("AE36").Value = CustomerREF.Offset(, 67)
    Sheets("Order Template").Range("AF36").Value = CustomerREF.Offset(, 68)
    Sheets("Order Template").Range("AG36").Value = CustomerREF.Offset(, 69)
    Sheets("Order Template").Range("A37").Value = CustomerREF.Offset(, 70)
    Sheets("Order Template").Range("D37").Value = CustomerREF.Offset(, 71)
    Sheets("Order Template").Range("F37").Value = CustomerREF.Offset(, 72)
    Sheets("Order Template").Range("I37").Value = CustomerREF.Offset(, 73)
    Sheets("Order Template").Range("AB37").Value = CustomerREF.Offset(, 74)
    Sheets("Order Template").Range("AC37").Value = CustomerREF.Offset(, 75)
    Sheets("Order Template").Range("AD37").Value = CustomerREF.Offset(, 76)
    Sheets("Order Template").Range("AE37").Value = CustomerREF.Offset(, 77)
    Sheets("Order Template").Range("AF37").Value = CustomerREF.Offset(, 78)
    Sheets("Order Template").Range("AG37").Value = CustomerREF.Offset(, 79)
    Sheets("Order Template").Range("A38").Value = CustomerREF.Offset(, 80)
    Sheets("Order Template").Range("D38").Value = CustomerREF.Offset(, 81)
    Sheets("Order Template").Range("F38").Value = CustomerREF.Offset(, 82)
    Sheets("Order Template").Range("I38").Value = CustomerREF.Offset(, 83)
    Sheets("Order Template").Range("AB38").Value = CustomerREF.Offset(, 84)
    Sheets("Order Template").Range("AC38").Value = CustomerREF.Offset(, 85)
    Sheets("Order Template").Range("AD38").Value = CustomerREF.Offset(, 86)
    Sheets("Order Template").Range("AE38").Value = CustomerREF.Offset(, 87)
    Sheets("Order Template").Range("AF38").Value = CustomerREF.Offset(, 88)
    Sheets("Order Template").Range("AG38").Value = CustomerREF.Offset(, 89)
    Sheets("Order Template").Range("A39").Value = CustomerREF.Offset(, 90)
    Sheets("Order Template").Range("D39").Value = CustomerREF.Offset(, 91)
    Sheets("Order Template").Range("F39").Value = CustomerREF.Offset(, 92)
    Sheets("Order Template").Range("I39").Value = CustomerREF.Offset(, 93)
    Sheets("Order Template").Range("AB39").Value = CustomerREF.Offset(, 94)
    Sheets("Order Template").Range("AC39").Value = CustomerREF.Offset(, 95)
    Sheets("Order Template").Range("AD39").Value = CustomerREF.Offset(, 96)
    Sheets("Order Template").Range("AE39").Value = CustomerREF.Offset(, 97)
    Sheets("Order Template").Range("AF39").Value = CustomerREF.Offset(, 98)
    Sheets("Order Template").Range("AG39").Value = CustomerREF.Offset(, 99)
    Sheets("Order Template").Range("A40").Value = CustomerREF.Offset(, 100)
    Sheets("Order Template").Range("D40").Value = CustomerREF.Offset(, 101)
    Sheets("Order Template").Range("F40").Value = CustomerREF.Offset(, 102)
    Sheets("Order Template").Range("I40").Value = CustomerREF.Offset(, 103)
    Sheets("Order Template").Range("AB40").Value = CustomerREF.Offset(, 104)
    Sheets("Order Template").Range("AC40").Value = CustomerREF.Offset(, 105)
    Sheets("Order Template").Range("AD40").Value = CustomerREF.Offset(, 106)
    Sheets("Order Template").Range("AE40").Value = CustomerREF.Offset(, 107)
    Sheets("Order Template").Range("AF40").Value = CustomerREF.Offset(, 108)
    Sheets("Order Template").Range("AG40").Value = CustomerREF.Offset(, 109)
    Sheets("Order Template").Range("A41").Value = CustomerREF.Offset(, 110)
    Sheets("Order Template").Range("D41").Value = CustomerREF.Offset(, 111)
    Sheets("Order Template").Range("F41").Value = CustomerREF.Offset(, 112)
    Sheets("Order Template").Range("I41").Value = CustomerREF.Offset(, 113)
    Sheets("Order Template").Range("AB41").Value = CustomerREF.Offset(, 114)
    Sheets("Order Template").Range("AC41").Value = CustomerREF.Offset(, 115)
    Sheets("Order Template").Range("AD41").Value = CustomerREF.Offset(, 116)
    Sheets("Order Template").Range("AE41").Value = CustomerREF.Offset(, 117)
    Sheets("Order Template").Range("AF41").Value = CustomerREF.Offset(, 118)
    Sheets("Order Template").Range("AG41").Value = CustomerREF.Offset(, 119)
    Sheets("Order Template").Range("A42").Value = CustomerREF.Offset(, 120)
    Sheets("Order Template").Range("D42").Value = CustomerREF.Offset(, 121)
    Sheets("Order Template").Range("F42").Value = CustomerREF.Offset(, 122)
    Sheets("Order Template").Range("I42").Value = CustomerREF.Offset(, 123)
    Sheets("Order Template").Range("AB42").Value = CustomerREF.Offset(, 124)
    Sheets("Order Template").Range("AC42").Value = CustomerREF.Offset(, 125)
    Sheets("Order Template").Range("AD42").Value = CustomerREF.Offset(, 126)
    Sheets("Order Template").Range("AE42").Value = CustomerREF.Offset(, 127)
    Sheets("Order Template").Range("AF42").Value = CustomerREF.Offset(, 128)
    Sheets("Order Template").Range("AG42").Value = CustomerREF.Offset(, 129)
    Sheets("Order Template").Range("A43").Value = CustomerREF.Offset(, 130)
    Sheets("Order Template").Range("D43").Value = CustomerREF.Offset(, 131)
    Sheets("Order Template").Range("F43").Value = CustomerREF.Offset(, 132)
    Sheets("Order Template").Range("I43").Value = CustomerREF.Offset(, 133)
    Sheets("Order Template").Range("AB43").Value = CustomerREF.Offset(, 134)
    Sheets("Order Template").Range("AC43").Value = CustomerREF.Offset(, 135)
    Sheets("Order Template").Range("AD43").Value = CustomerREF.Offset(, 136)
    Sheets("Order Template").Range("AE43").Value = CustomerREF.Offset(, 137)
    Sheets("Order Template").Range("AF43").Value = CustomerREF.Offset(, 138)
    Sheets("Order Template").Range("AG43").Value = CustomerREF.Offset(, 139)
    Sheets("Order Template").Range("A44").Value = CustomerREF.Offset(, 140)
    Sheets("Order Template").Range("D44").Value = CustomerREF.Offset(, 141)
    Sheets("Order Template").Range("F44").Value = CustomerREF.Offset(, 142)
    Sheets("Order Template").Range("I44").Value = CustomerREF.Offset(, 143)
    Sheets("Order Template").Range("AB44").Value = CustomerREF.Offset(, 144)
    Sheets("Order Template").Range("AC44").Value = CustomerREF.Offset(, 145)
    Sheets("Order Template").Range("AD44").Value = CustomerREF.Offset(, 146)
    Sheets("Order Template").Range("AE44").Value = CustomerREF.Offset(, 147)
    Sheets("Order Template").Range("AF44").Value = CustomerREF.Offset(, 148)
    Sheets("Order Template").Range("AG44").Value = CustomerREF.Offset(, 149)
    Sheets("Order Template").Range("A45").Value = CustomerREF.Offset(, 150)
    Sheets("Order Template").Range("D45").Value = CustomerREF.Offset(, 151)
    Sheets("Order Template").Range("F45").Value = CustomerREF.Offset(, 152)
    Sheets("Order Template").Range("I45").Value = CustomerREF.Offset(, 153)
    Sheets("Order Template").Range("AB45").Value = CustomerREF.Offset(, 154)
    Sheets("Order Template").Range("AC45").Value = CustomerREF.Offset(, 155)
    Sheets("Order Template").Range("AD45").Value = CustomerREF.Offset(, 156)
    Sheets("Order Template").Range("AE45").Value = CustomerREF.Offset(, 157)
    Sheets("Order Template").Range("AF45").Value = CustomerREF.Offset(, 158)
    Sheets("Order Template").Range("AG45").Value = CustomerREF.Offset(, 159)
    Sheets("Order Template").Range("A46").Value = CustomerREF.Offset(, 160)
    Sheets("Order Template").Range("D46").Value = CustomerREF.Offset(, 161)
    Sheets("Order Template").Range("F46").Value = CustomerREF.Offset(, 162)
    Sheets("Order Template").Range("I46").Value = CustomerREF.Offset(, 163)
    Sheets("Order Template").Range("AB46").Value = CustomerREF.Offset(, 164)
    Sheets("Order Template").Range("AC46").Value = CustomerREF.Offset(, 165)
    Sheets("Order Template").Range("AD46").Value = CustomerREF.Offset(, 166)
    Sheets("Order Template").Range("AE46").Value = CustomerREF.Offset(, 167)
    Sheets("Order Template").Range("AF46").Value = CustomerREF.Offset(, 168)
    Sheets("Order Template").Range("AG46").Value = CustomerREF.Offset(, 169)
    
    Else
MsgBox ("customer not found")
End If

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
It's quicker to write blocks of contiguous cells where possible, so for example, you could use:

Code: 
    Sheets("Order Template").Range("AB31").Resize(, 6).Value = CustomerREF.Offset(, 14).Resize(, 6).value

rather than:

Code: 
    Sheets("Order Template").Range("AB31").Value = CustomerREF.Offset(, 14)
    Sheets("Order Template").Range("AC31").Value = CustomerREF.Offset(, 15)
    Sheets("Order Template").Range("AD31").Value = CustomerREF.Offset(, 16)
    Sheets("Order Template").Range("AE31").Value = CustomerREF.Offset(, 17)
    Sheets("Order Template").Range("AF31").Value = CustomerREF.Offset(, 18)
    Sheets("Order Template").Range("AG31").Value = CustomerREF.Offset(, 19)

I'd also use a variable/With block for the reference to Sheets("Order Template").
 
Upvote 0
Here is one more compact way to write it

VBA Code: 
Sub Customerdatapull_v2()
  Dim CustomerREF As Range
  Dim Cols As Variant
  Dim i As Long, j As Long, k As Long
  
  Cols = Split("1 4 6 9 28 29 30 31 32 33")
  Set CustomerREF = Sheets("Customers").Range("A:A").Find(Sheets("order template").Range("B6").Value)
  If Not CustomerREF Is Nothing Then
    Application.ScreenUpdating = False
    With Sheets("order template").Range("A31")
      k = 1
      For i = 10 To 169
        .Cells(k, Val(Cols(j))).Value = CustomerREF.Offset(, i).Value
        j = j + 1
        If j > UBound(Cols) Then
          j = 0
          k = k + 1
        End If
      Next i
    End With
    Application.ScreenUpdating = True
  Else
    MsgBox ("customer not found")
  End If
End Sub
 
Upvote 0
Solution
VBA Code: 
Sub Final_Acc_Cert()
Dim CustomerREF As Range
Dim RngIndex As Variant
Set CustomerREF = Sheets("Customers").Range("A:A").Find(Sheets("order template").Range("B6").Value)
RngIndex = Array(1, 4, 6, 7, 28, 29, 30, 31, 32, 33) ' columnIndex of column A,D,F,G,...,AG
For i = 31 To 46 ' row index from row 31 to row 46
    For j = 0 To 9
        Cells(i, RngIndex(j)).Value = CustomerREF.Offset(, k + 10) ' write down A31,D31,...,A32,D32,...A46,D46...
        k = k + 1
    Next
Next
End Sub
 
Upvote 0
Peter_SSs said:
Here is one more compact way to write it

VBA Code: 
Sub Customerdatapull_v2()
  Dim CustomerREF As Range
  Dim Cols As Variant
  Dim i As Long, j As Long, k As Long
 
  Cols = Split("1 4 6 9 28 29 30 31 32 33")
  Set CustomerREF = Sheets("Customers").Range("A:A").Find(Sheets("order template").Range("B6").Value)
  If Not CustomerREF Is Nothing Then
    Application.ScreenUpdating = False
    With Sheets("order template").Range("A31")
      k = 1
      For i = 10 To 169
        .Cells(k, Val(Cols(j))).Value = CustomerREF.Offset(, i).Value
        j = j + 1
        If j > UBound(Cols) Then
          j = 0
          k = k + 1
        End If
      Next i
    End With
    Application.ScreenUpdating = True
  Else
    MsgBox ("customer not found")
  End If
End Sub
Click to expand...
This works great, tiny bit smoother, and loads less space. thanks.
 
Upvote 0
Peter_SSs said:
Here is one more compact way to write it

VBA Code: 
Sub Customerdatapull_v2()
  Dim CustomerREF As Range
  Dim Cols As Variant
  Dim i As Long, j As Long, k As Long
 
  Cols = Split("1 4 6 9 28 29 30 31 32 33")
  Set CustomerREF = Sheets("Customers").Range("A:A").Find(Sheets("order template").Range("B6").Value)
  If Not CustomerREF Is Nothing Then
    Application.ScreenUpdating = False
    With Sheets("order template").Range("A31")
      k = 1
      For i = 10 To 169
        .Cells(k, Val(Cols(j))).Value = CustomerREF.Offset(, i).Value
        j = j + 1
        If j > UBound(Cols) Then
          j = 0
          k = k + 1
        End If
      Next i
    End With
    Application.ScreenUpdating = True
  Else
    MsgBox ("customer not found")
  End If
End Sub
Click to expand...
Sorry to pull up a completed thread. But figured it would be easier.
How would i reverse this to send it the opposite direction!
I am using a button to run a similar style macro to the original but to send the data the other way.
 
Upvote 0
johnmpc said:
.. send the data the other way.
Click to expand...
Is this what you mean?

VBA Code: 
Sub Customerdatapush()
  Dim CustomerREF As Range
  
  Set CustomerREF = Sheets("Customers").Range("A:A").Find(Sheets("order template").Range("B6").Value)
  If Not CustomerREF Is Nothing Then
    CustomerREF.Offset(, 9).Resize(, 160).Value = Split(Application.TextJoin(" ", True, Application.Index(Sheets("order template").Cells, Evaluate("row(31:46)"), Array(1, 4, 6, 9, 28, 29, 30, 31, 32, 33))))
  Else
    MsgBox ("customer not found")
  End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Simplify code
2
Replies
19
Views
658
johnmpc
J
A
  • Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A
J
  • Solved
variable vba per ws
Replies
5
Views
328
kevin9999
K
J
  • Question
hide/unhide
Replies
1
Views
249
Jimmypop
Jimmypop
S
  • Question
Numbers to be black
2
Replies
10
Views
670
Fluff
Fluff

Forum statistics

Threads
1,214,655
Messages
6,120,760
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top