VBA Code for Transcribing Data

[knick]

I am trying to write a Macro that will autofill another spreadsheet based on the weight of product A that I have.

For example, I have 6000 lbs of Product A with order number 456ABC.

I want to write code that will look at the weight (6000) and knowing that each bag weighs 100 pounds, autofill 60 rows with order number 456ABC.

Raw Data

A B C

1 456ABC 6000

2

3


"Run Macro"

Finished spreadsheet


A B C

1 456ABC

2 456ABC

3 456ABC

.
.
.
.
60 456ABC


I'm thinking I want to use a Do While Loop but I'm having trouble with the If statement, specifically with how to have a calculated value as your less than.

I have pasted the code I have so far below:

Sub Transcribe_Lot_Numbers_Do_While()


Dim i As Integer
Dim a As Integer


i = 0
a = 1


Do While i < 5000


If a < Range("B1"+i)/400


Cells(i + 1, 6).Value = Range("A1" + i)


a = a + 1


Next a


i = i + 1


Next i






End Sub


Thank you for your help!

 

[Joe4]

Welcome to the Board!

I am not sure I follow what your data looks like and what you are trying to do, so I cannot evaluate the logic of your code, but I do see some definite compile errors.

These references are wrong:

[COLOR=#333333]Range("B1"+i)
[/COLOR][COLOR=#333333]Range("A1" + i)[/COLOR]

You could do things like this:

Range("A1").Offset(i,0)

or

Range("A" & i+1)

 

[BarryL]

try

Sub test()
Dim i As Long
For i = 2 To Range("B1").Value / 100
Range("A" & i).Value = Range("A1").Value
Next
End Sub

 

[knick]

try

Sub test()
Dim i As Long
For i = 2 To Range("B1").Value / 100
Range("A" & i).Value = Range("A1").Value
Next
End Sub

This worked well, thank you for your help!

Do you have a recommendation for how to do this for a list of this information, i.e. this spreadsheet is constantly updated with new order numbers and weights. How would I do this so that it would do this for all of the different order numbers? Would it be a nested loop? Thank you!

 

[BarryL]

What's the layout of the sheet/list?

 

[hiker95]

Do you have a recommendation for how to do this for a list of this information, i.e. this spreadsheet is constantly updated with new order numbers and weights. How would I do this so that it would do this for all of the different order numbers? Would it be a nested loop? Thank you!

knick,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com