VBA Code for Transcribing Data

knick

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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. 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
3
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,649
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top