Working in first 5 rows then doesn't complete the Last column loop

J

johnmpc

Board Regular
Joined
Oct 19, 2020
Messages
108
Office Version
  1. 365
Platform
  1. Windows
I have working code if i change the Last Column find for a number above my last bit of data.
The last column code (lc) works up to row 5 of the Quotes sheet but if a run quote 6 or higher it thinks the last column with data is the last one in the section of data before the loop.
Still runs the first bit though.
Basically this code copies data from a row on Sheet "Quotes" and places it back onto a Sheet "order Template" in the right places. But should stop at the last piece of data (lc) in the Row QuoteREF.
Any ideas? Thanks

VBA Code: 
Sub Quotedatapull_v2()
  Dim QuoteREF As Range
  Dim Cols As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  
  Dim lc As Long
  
  Cols = Split("1 4 6 9 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33")
  Set QuoteREF = Sheets("Quotes").Range("A:A").Find(Sheets("order template").Range("F8").Value)
  
  If Not QuoteREF Is Nothing Then
    Application.ScreenUpdating = False
    ThisWorkbook.ClearForm
    
    Sheets("Order Template").Range("A2").Value = QuoteREF.Offset(, 0)
    Sheets("Order Template").Range("F12").Value = QuoteREF.Offset(, 1)
    Sheets("Order Template").Range("C2").Value = QuoteREF.Offset(, 2)
    Sheets("Order Template").Range("B26").Value = QuoteREF.Offset(, 3)
    Sheets("Order Template").Range("B4").Value = QuoteREF.Offset(, 4)
    Sheets("Order Template").Range("B6").Value = QuoteREF.Offset(, 5)
    Sheets("Order Template").Range("B8").Value = QuoteREF.Offset(, 6)
    Sheets("Order Template").Range("B10").Value = QuoteREF.Offset(, 7)
    Sheets("Order Template").Range("B12").Value = QuoteREF.Offset(, 8)
    Sheets("Order Template").Range("B14").Value = QuoteREF.Offset(, 9)
    Sheets("Order Template").Range("B15").Value = QuoteREF.Offset(, 10)
    Sheets("Order Template").Range("B16").Value = QuoteREF.Offset(, 11)
    Sheets("Order Template").Range("B17").Value = QuoteREF.Offset(, 12)
    Sheets("Order Template").Range("B18").Value = QuoteREF.Offset(, 13)
    Sheets("Order Template").Range("B20").Value = QuoteREF.Offset(, 14)
    Sheets("Order Template").Range("B21").Value = QuoteREF.Offset(, 15)
    Sheets("Order Template").Range("B22").Value = QuoteREF.Offset(, 16)
    Sheets("Order Template").Range("B23").Value = QuoteREF.Offset(, 17)
    Sheets("Order Template").Range("B24").Value = QuoteREF.Offset(, 18)
    Sheets("Order Template").Range("E2").Value = QuoteREF.Offset(, 19)
    Sheets("Order Template").Range("F2").Value = QuoteREF.Offset(, 20)
    With Sheets("order template").Range("A31")
      k = 1
      lc = Cells(QuoteREF.Rows, Columns.Count).End(xlToLeft).Column
      For i = 21 To lc
        .Cells(k, Val(Cols(j))).Value = QuoteREF.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 ("Quote Not Found")
  End If
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
I have having a hard time envisioning what you are trying to describe (and perhaps others are too). It is not quite clear to me.

In these situations, it is often very helpful if we can see a sample of what the data you are working with looks like, and what you would like your expected result to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Ok So hopefully this works.

Template sheet where the information is displayed.

John's Quote Generator.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Quote NumberQuote Date:Paid Date:Due Date:
2
3
4Customer Name
5
6CompanyTestRetreive order
7
8Customer Phone No.Number =
9
10Customer Mobile No.SupplierPrefixPrice Updated
11PencarriePEN08/11/2021Carton
12Customer EmailOrder StatusUneek04/11/2021Carton
13ORNORN30/10/2021Our Price
14Customer AddressBTCBTC08/11/2021Carton
15Make SureContinentalCON12/07/2021Our Price
16you areBrook TavernerBT24/10/2021Our price
17updatingCottonridgeCOT11-OctOur price
18theRalawiseRAL09/11/2021Carton
19correct
20Delivery AddressQuote
21
22
23
24
25
26SO / DescriptionOtherOther        
27If its Red Don’t Change itCHOOSE SIZING CHART FOR EACH LINELadies6810121416182022
28CalculationsTrousers283032343638404244
29Shoe Size5678910111213
30Code Supplier Product Description ColourCost (Min)Decoration Del/garment from supplierSuggested Sell Price 50%Sell Total COGSProfit GP%QTYCost TotalSale Total Profit Total SizingOne SizeXSSMLXL2XL3XL4XLOTHERDecoration 1Position 1Decoration 2Position 2Decoration 3Position 3
31            
32            
33            
34            
35            
36            
37            
38            
39            
40            
41            
42            
43            
44            
45            
46            
47            
48Total
49 #DIV/0!
Order Template
 
Upvote 0
And the QUOTES sheet where the information is stored.

John's Quote Generator.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEO
11StatusDateOrder DescriptionNameCompanyPhone MobileEmailAddress 1Address 2Address 3Address 4Address 5Delivery 1Delivery 2Delivery 3Delivery 4Delivery 5Paid DateDue Date
29Paid10/12/2021SO8093 Pangbourne CCF Hoodies13/12/202121/01/2022COTCR01Olive1.419Unisex11351181Pangbourne CCFLeft ChestdeliveryNONE12.66Other2
310Quote10/12/2021Intel Evo Mouse Grey L/Sleeve TsPEN02074Mouse Grey1.411.05Unisex80deliveryNONE12.66Other1
411Quote10/12/2021Pangbourne Staff Hoodies30/11/202117/12/2021PENRS160Navy/Navy339.5Unisex31PangbourneLeft ChestPENK703Navy519.5Ladies43PangbourneLeft ChestPangbourne PRINTBackPENK403Navy519.5Unisex3PangbourneLeft ChestPangbourne PRINTBackPENRG128Dark Navy329.95Unisex1PangbourneLeft ChestOrigination25NONE25Other1DeliveryNONE12.66Other1
512Paid13/12/2021SO8118 Bure Park BeaniesPENbb45Bright Royal1.44Unisex30Bure Park FCcentre frontPENBB45BBright Royal1.44Unisex2Bure Park FCcentre front
613Quote10/12/2021test30/11/202117/12/2021PENRS160Navy/Navy339.5Unisex31PANG05 Just PangLeft ChestPENK703Navy519.5Ladies4PANG04 ICT TechLeft ChestPENK703Navy519.5Ladies3PANG03 HousekeepingLeft ChestPENK403Navy519.5Unisex3PANG03 HousekeepingLeft ChestPENRG128Dark Navy329.95Unisex1PANG03 HousekeepingLeft ChestOrigination25NONE25Other11
714Quote01/01/1900TEST101112131401/01/190001/01/190012
815Quote10/12/2021SO8093 Pangbourne CCF Hoodies13/12/202121/01/2022COTCR01Olive1.419Unisex11351181Pangbourne CCFLeft ChestdeliveryNONE12.66Other2
916Quote13/12/2021SO8118 Bure Park BeaniesPENbb45Bright Royal1.44Unisex30Bure Park FCcentre frontPENBB45BBright Royal1.44Unisex2
10
Quotes
 
Upvote 0
I think this may be the problem part:
Rich (BB code): 
lc = Cells(QuoteREF.Rows, Columns.Count).End(xlToLeft).Column

I think maybe it should be:
QuoteREF.Row
(without the "s" on the end of "Rows")
 
Upvote 0
Joe4 said:
I think this may be the problem part:
Rich (BB code): 
lc = Cells(QuoteREF.Rows, Columns.Count).End(xlToLeft).Column

I think maybe it should be:
QuoteREF.Row
(without the "s" on the end of "Rows")
Click to expand...
Thats really odd. Yes it should be Row. but thats what it was before i posted it........

Quotes sheet has up to a max of around 390 columns for each Quote. VBA retrieves the data for the quote and places it back in the correct places.
The code works if i simple put 390 instead of using the find last column code. But for some reason the find last column code stops working past row 5. Works perfectly for the first 5 rows.
VBA Code: 
Sub Quotedatapull_v2()
  Dim QuoteREF As Range
  Dim Cols As Variant
  Dim i As Long, j As Long, k As Long, lc As Long

  
  Cols = Split("1 4 6 9 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33")
  Set QuoteREF = Sheets("Quotes").Range("A1:A50").Find(Sheets("order template").Range("F8").Value)
  
  If Not QuoteREF Is Nothing Then
    Application.ScreenUpdating = False
    ThisWorkbook.ClearForm
    
    Sheets("Order Template").Range("A2").Value = QuoteREF.Offset(, 0)
    Sheets("Order Template").Range("F12").Value = QuoteREF.Offset(, 1)
    Sheets("Order Template").Range("C2").Value = QuoteREF.Offset(, 2)
    Sheets("Order Template").Range("B26").Value = QuoteREF.Offset(, 3)
    Sheets("Order Template").Range("B4").Value = QuoteREF.Offset(, 4)
    Sheets("Order Template").Range("B6").Value = QuoteREF.Offset(, 5)
    Sheets("Order Template").Range("B8").Value = QuoteREF.Offset(, 6)
    Sheets("Order Template").Range("B10").Value = QuoteREF.Offset(, 7)
    Sheets("Order Template").Range("B12").Value = QuoteREF.Offset(, 8)
    Sheets("Order Template").Range("B14").Value = QuoteREF.Offset(, 9)
    Sheets("Order Template").Range("B15").Value = QuoteREF.Offset(, 10)
    Sheets("Order Template").Range("B16").Value = QuoteREF.Offset(, 11)
    Sheets("Order Template").Range("B17").Value = QuoteREF.Offset(, 12)
    Sheets("Order Template").Range("B18").Value = QuoteREF.Offset(, 13)
    Sheets("Order Template").Range("B20").Value = QuoteREF.Offset(, 14)
    Sheets("Order Template").Range("B21").Value = QuoteREF.Offset(, 15)
    Sheets("Order Template").Range("B22").Value = QuoteREF.Offset(, 16)
    Sheets("Order Template").Range("B23").Value = QuoteREF.Offset(, 17)
    Sheets("Order Template").Range("B24").Value = QuoteREF.Offset(, 18)
    Sheets("Order Template").Range("E2").Value = QuoteREF.Offset(, 19)
    Sheets("Order Template").Range("F2").Value = QuoteREF.Offset(, 20)
    With Sheets("order template").Range("A31")
      
      lc = Cells(QuoteREF.Row, Columns.Count).End(xlToLeft).Column
      k = 1
      For i = 21 To lc
        .Cells(k, Val(Cols(j))).Value = QuoteREF.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 ("Quote Not Found")
  End If
End Sub
 
Upvote 0
Maybe you need to reference the sheet, i.e.
Rich (BB code): 
      lc = Sheets("Quotes").Cells(QuoteREF.Row, Columns.Count).End(xlToLeft).Column
Without a sheet reference, it will default to whatever the current sheet is when it hits that line of code.
Even though it is pulling the Row number from your "QuoteRef" named range, that doesn't mean it will automatically do the calculation on the "Quotes" sheet.
 
Upvote 0
Solution
the line that Joe highlighted is referencing the Active sheet NOT the ORder template is this correct
should
VBA Code: 
lc = Cells(QuoteREF.Rows, Columns.Count).End(xlToLeft).Column
be
VBA Code: 
lc = .Cells(QuoteREF.Rows, Columns.Count).End(xlToLeft).Column
 
Upvote 0
Joe4 said:
Maybe you need to reference the sheet, i.e.
Rich (BB code): 
      lc = Sheets("Quotes").Cells(QuoteREF.Row, Columns.Count).End(xlToLeft).Column
Without a sheet reference, it will default to whatever the current sheet is when it hits that line of code.
Even though it is pulling the Row number from your "QuoteRef" named range, that doesn't mean it will automatically do the calculation on the "Quotes" sheet.
Click to expand...
So strange. Don't know how it was working up until row 5.

Sure i tried this at somepoint but adding the sheet reference seems to have it solved.

Thanks all
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Worksheet from a template using a master list but protecting existing worksheets
Replies
0
Views
219
Alfalfa166
A
Guna13
  • Question
How to Efficient way for create Multiple Workbooks
Replies
1
Views
87
Guna13
Guna13
P
  • Question
Turning OFF the visibility of a horizontal (category) axis labels if certain conditions apply
Replies
1
Views
386
NdNoviceHlp
NdNoviceHlp
F
  • Question
VBA run code based off cell value
Replies
1
Views
308
igold
igold
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
337
Agnarr
Agnarr

Forum statistics

Threads
1,214,628
Messages
6,120,618
Members
448,973
Latest member
ChristineC

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