Solved

Looping Problem - Read from one sheet and paste into another

Posted on 2016-08-21
15
26 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 45

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
 
LVL 45

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 45

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Shaun Phelan
ID: 41764688
0
 
LVL 45

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 45

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 45

Expert Comment

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now