VBA Code for Transcribing Data

knick

New Member
Joined
Jan 18, 2017
Messages
2
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,783
Office Version
365
Platform
Windows
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:
Code:
[COLOR=#333333]Range("B1"+i)
[/COLOR][COLOR=#333333]Range("A1" + i)[/COLOR]
You could do things like this:
Code:
Range("A1").Offset(i,0)
or
Code:
Range("A" & i+1)
 
Last edited:

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
try

Code:
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

New Member
Joined
Jan 18, 2017
Messages
2
try

Code:
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!
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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
 
Last edited:

Forum statistics

Threads
1,085,718
Messages
5,385,423
Members
401,946
Latest member
Jimmerjammer

Some videos you may like

This Week's Hot Topics

Top