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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
830
johnmpc
J
A
  • Question 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
545
kevin9999
K
J
  • Question Question
hide/unhide
Replies
1
Views
357
Jimmypop
Jimmypop
S
  • Question Question
Numbers to be black
2
Replies
10
Views
910
Fluff
Fluff

Forum statistics

Threads
1,223,227
Messages
6,170,857
Members
452,361
Latest member
d3ad3y3

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