asked on

Looping Problem - Read from one sheet and paste into another

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

Martin Liss

Change Sub GetData() to Sub GetData(r as Long) and Call GetData to Call GetData(r)

Shaun Phelan

ASKER

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

Shaun Phelan

ASKER

MacroTesting.xltm

Martin Liss

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

Shaun Phelan

ASKER

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.

Martin Liss

You forgot to attach your new workbook.

Shaun Phelan

ASKER

Here is the file

Shaun Phelan

ASKER

28964594.xlsm

Martin Liss

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

Shaun Phelan

ASKER

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

Martin Liss

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?

Shaun Phelan

ASKER

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

Martin Liss

You show "Thailand" as 16 in your most recent workbook. Can you explain?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial