Solved

Looping Problem - Read from one sheet and paste into another

Posted on 2016-08-21
15
35 Views
Last Modified: 2016-09-10
Hi,


I am new to this so I am sure my code is not very efficient and I am sure there is probably something silly I am missing.I am working on converting a summary pricing sheet to a detailed sheet.  I will then load the detail sheet into our ERP system.  I can read the initial line of summary information and I can parse it out it into the detail layout.  However, I can not get my loop to work so I can read the next line of summary information.  It seems like it is reading the same initial line each time.


Basic Program Structure

'Click button to reformat summary into format needed for loading detail

Private Sub CommandButton1_Click()

" Get number of records to be read
Call GetRowCount


' I start reading on row 9
For r = 9 To GetRowCount

" get first row of data
Call GetData

'Get next row of data
Next r


   
End Sub






'Get Values from Summary Sheet Row

Sub GetData()

' r = row number
 mtrn = Sheets("Pricing").Cells(r, 1)
  zp00 = Sheets("Pricing").Cells(r, 4)

'Write detail  data into new sheet


    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 9) = zp00

End Sub


"Determine how many rows were submitted
Sub GetRowCount()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Pricing")

    Dim k As Long

    k = sh.Range("A9", sh.Range("A9").End(xlDown)).Rows.Count
   
 
End Sub
0
Comment
Question by:Shaun Phelan
  • 7
  • 7
15 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41764311
Change Sub GetData() to Sub GetData(r as Long) and Call GetData to Call GetData(r)
0
 

Author Comment

by:Shaun Phelan
ID: 41764563
Thanks for responding on a Sunday.  I implemented your suggestion but I still didn't get it to work.  I probably didn't give you enough info.  I am going to paste all of the code as it currently is.  Any additional help would be appreciated.

Private Sub CommandButton1_Click()

Dim r As Long
Dim i As Integer



Call GetRowCount



For i = 9 To 11  ' repalace 11 with k from get row count once i figure that out


Call GetData(r)

MsgBox ("loop: " & i)

Next i


   
End Sub


Sub GetRowCount()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Pricing")


   
    k = sh.Range("A9", sh.Range("A9").End(xlDown)).Rows.Count
   
   
   
End Sub




Sub GetData(r As Long)
Dim mtrn As String
Dim desc As String
Dim zp00 As Integer
Dim userInv As Currency
Dim dealInv As Currency
Dim restInv As Currency
Dim counter As Integer


'Get Values from Row

 'r = i  this doesn't see m to work.  Hard coding until i get the rest fixed

 r = 9 'i set the current row to read/write based on the loop counter

    mtrn = Sheets("Pricing").Cells(r, 1)
    desc = Sheets("Pricing").Cells(r, 2)
    zp00 = Sheets("Pricing").Cells(r, 4)
    userInv = Sheets("Pricing").Cells(r, 5)
    dealInv = Sheets("Pricing").Cells(r, 6)
    restInv = Sheets("Pricing").Cells(r, 7)

 
 
 

 
' i know i should be looping some of this

    Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'01"
    Worksheets("wsLoad").Cells(r, 3) = "Wholesale - Employee"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
    Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'02"
    Worksheets("wsLoad").Cells(r, 3) = "Sporting Goods Dealr"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = dealInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - dealInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
    Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'03"
    Worksheets("wsLoad").Cells(r, 3) = "End user"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = userInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - userInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1


    Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'04"
    Worksheets("wsLoad").Cells(r, 3) = "Buying Group"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
        Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'05"
    Worksheets("wsLoad").Cells(r, 3) = "Big Box"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1


        Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'06"
    Worksheets("wsLoad").Cells(r, 3) = "Training/Academy"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
   
            Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'07"
    Worksheets("wsLoad").Cells(r, 3) = "LE Reseller"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
                Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'08"
    Worksheets("wsLoad").Cells(r, 3) = "LE Wholesale"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
   
                    Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'09"
    Worksheets("wsLoad").Cells(r, 3) = "LE Agency"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
                        Worksheets("wsLoad").Cells(r, 1) = "1000"
    Worksheets("wsLoad").Cells(r, 2) = "'10"
    Worksheets("wsLoad").Cells(r, 3) = "Military"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
                            Worksheets("wsLoad").Cells(r, 1) = "2000"
    Worksheets("wsLoad").Cells(r, 2) = "'12"
    Worksheets("wsLoad").Cells(r, 3) = "Export Distributor"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
   
        r = r + 1
   
    Worksheets("wsLoad").Cells(r, 1) = "2000"
    Worksheets("wsLoad").Cells(r, 2) = "'13"
    Worksheets("wsLoad").Cells(r, 3) = "Liege without proof"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
        r = r + 1
       
   
    Worksheets("wsLoad").Cells(r, 1) = "2000"
    Worksheets("wsLoad").Cells(r, 2) = "'13"
    Worksheets("wsLoad").Cells(r, 3) = "Liege without proof"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
   
   
    Worksheets("wsLoad").Cells(r, 1) = "2000"
    Worksheets("wsLoad").Cells(r, 2) = "'14"
    Worksheets("wsLoad").Cells(r, 3) = "Liege with proof"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
        r = r + 1
       
   
     Worksheets("wsLoad").Cells(r, 1) = "2000"
    Worksheets("wsLoad").Cells(r, 2) = "'15"
    Worksheets("wsLoad").Cells(r, 3) = "Canadian Large Volume"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
    r = r + 1
   
   
         Worksheets("wsLoad").Cells(r, 1) = "2000"
    Worksheets("wsLoad").Cells(r, 2) = "'16"
    Worksheets("wsLoad").Cells(r, 3) = "Thailand"
    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
    Worksheets("wsLoad").Cells(r, 5) = desc
    Worksheets("wsLoad").Cells(r, 7) = zp00
    Worksheets("wsLoad").Cells(r, 8) = restInv
    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
    Worksheets("wsLoad").Cells(r, 10) = "USD"
    Worksheets("wsLoad").Cells(r, 11) = "EA"
    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
   
   

   

   
   
   
   
End Sub
0
 

Author Comment

by:Shaun Phelan
ID: 41764564
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 46

Expert Comment

by:Martin Liss
ID: 41764591
Take a look at the changes I made (find "Marty") and the question I asked in the workbook and then try it. If you have any questions about the coding in the workbook or coding in general please don't be afraid to ask.

This article of mine on debugging may help you.
28964594.xlsm
0
 

Author Comment

by:Shaun Phelan
ID: 41764609
Hi Martin,

Thanks for the help.  Lots of edits to review, but unfortunately it still isn't outputting correctly.  From the pricing tab you will see that a price is available to multiple customer groups, columns e through I.  When I load pricing I need to have a single record for each material and each customer group. So for each material I am going to load 16 records.

The current output has 6 records for one customer group.  I need the output to be 13 records (one for each customer group) 6 materials.  I should have 78 records when I am done.

All of the code you commented out was my cheap way for "looping" through each customer group.  I was stuck on advancing the loop for the next material from the pricing sheet.

I added some additional comments to code to make it easier to read.  I also added a 3rd sheet so you could see what my desired output would be.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41764677
You forgot to attach your new workbook.
0
 

Author Comment

by:Shaun Phelan
ID: 41764687
Here is the file
0
 

Author Comment

by:Shaun Phelan
ID: 41764688
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41764732
In your comment out data you have one customer group(?) duplicated. I assume that one of them should be changed and if so what should it look like.

'    Worksheets("wsLoad").Cells(r, 1) = "2000"
'    Worksheets("wsLoad").Cells(r, 2) = "'13"
'    Worksheets("wsLoad").Cells(r, 3) = "Liege without proof"
'    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
'    Worksheets("wsLoad").Cells(r, 5) = desc
'    Worksheets("wsLoad").Cells(r, 7) = zp00
'    Worksheets("wsLoad").Cells(r, 8) = restInv
'    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
'    Worksheets("wsLoad").Cells(r, 10) = "USD"
'    Worksheets("wsLoad").Cells(r, 11) = "EA"
'    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
'    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"
'        r = r + 1
'
'
'    Worksheets("wsLoad").Cells(r, 1) = "2000"
'    Worksheets("wsLoad").Cells(r, 2) = "'13"
'    Worksheets("wsLoad").Cells(r, 3) = "Liege without proof"
'    Worksheets("wsLoad").Cells(r, 4) = "'" & mtrn
'    Worksheets("wsLoad").Cells(r, 5) = desc
'    Worksheets("wsLoad").Cells(r, 7) = zp00
'    Worksheets("wsLoad").Cells(r, 8) = restInv
'    Worksheets("wsLoad").Cells(r, 9) = zp00 - restInv
'    Worksheets("wsLoad").Cells(r, 10) = "USD"
'    Worksheets("wsLoad").Cells(r, 11) = "EA"
'    Worksheets("wsLoad").Cells(r, 12) = "8/20/2016"
'    Worksheets("wsLoad").Cells(r, 13) = "12/31/9999"

Open in new window

0
 

Author Comment

by:Shaun Phelan
ID: 41764736
Hi Martin,

Thanks for all of your help.  I used your comments to finally get it the way I needed it.  I ultimately split the reading and the writing into two different subs.  I am wondering if you could take one last look and let me know where I could improve my logic.  For instance, I used a public variable "r" and I am not sure if that is recommended.  Anyways thanks again for your help.

Since there is really only 3 different pricing options, I was thinking that I could do a select case. Other thoughts?
28964594.xlsm
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41764742
Help me understand something. Previously you said that "So for each material I am going to load 16 records". However in your new sheet you have only 14 and you're missing "03" and "11".  I assume that that's a mistake so can you please list the 16 customer groups?
0
 

Author Comment

by:Shaun Phelan
ID: 41764760
Yup, that's a mistake.  14 groups... grp 11 is not currently used.


Cust. Grp      CG Descript
01      Wholesale - Employee
02      Sporting Goods Dealr
03      End user
04      Buying Group
05      Big Box
06      Training/Academy
07      LE Reseller
08      LE Wholesale
09      LE Agency
10      Military
12      Export Distributor
13      Liege without proof
14      Liege with proof
15      Canadian Large Volume
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41764770
You show "Thailand" as 16 in your most recent workbook. Can you explain?
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points (awarded by participants)
ID: 41765611
Your way is a reasonable way to do it but I've attached an updated version of my code that shows how I'd  do it. If you're interested, please see my A Guide to Writing Understandable and Maintainable VBA Code article.
28964594a.xlsm
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question