我经营一家小型零售店，我的任务是为我们的商品创建库存标签。 我将库存管理软件（AMan Pro）中的项目列表导出到Excel中，包括项目的描述，数量，条件，SKU＃和平台（视频游戏平台）。 我目前有两个宏。 一个人将SKU修剪为可用的格式（去除前导字母），另一个将多个数量项分开到单独的行。 （即数量为5的项目＃1将被复制到5行，所有数量都为1）这些东西可以工作，但我想我正在做一些可以用宏处理的不必要的步骤。
我的AMan程序用项目吐出Excel工作簿。 然后我将这些项目复制到第一页上的“启用宏”的工作簿中，然后运行我的宏。 第二张表格具有格式正确的标签数据。 例如; 它具有修剪Description字段的公式，仅使用前60个字符。 （有些描述很长）
我觉得第二张不是必需的。 我想将这些项目复制到Sheet 1中并运行一个宏， 在表1中为我完成所有格式化。
将具有多个量的项目分隔到单独的行上（我确实有一个工作宏。） 从SKU（工作宏）中删除前导字母，然后将SKU以七位数字格式（#######）放入 将描述简化为前60个字符。 将条件减少到只有前2个字符。 将平台减少到前15个字符。这是我当前的宏代码和我的电子表格的链接。 先谢谢你，伙计们。 SKU_LABEL_FINAL.xlsm
Sub ExpandRows() Dim dat As Variant Dim i As Long Dim rw As Range Dim rng As Range Set rng = Sheets(1).UsedRange dat = rng ' Loop thru your data, starting at the last row For i = UBound(dat, 1) To 2 Step -1 ' If Quantity > 1 If dat(i, 2) > 1 Then ' Insert rows to make space Set rw = rng.Rows(i).EntireRow rw.Offset(1, 0).Resize(dat(i, 2) - 1).Insert ' copy row data down rw.Copy rw.Offset(1, 0).Resize(dat(i, 2) - 1) ' set Quantity to 1 rw.Cells(1, 2).Resize(dat(i, 2), 1) = 1 End If Next Columns("D:D").Select Selection.Replace What:="AManPro-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False结束子
I run a small retail store and I've been tasked with creating inventory labels for our items. I export a list of items from an inventory management software (AMan Pro) into Excel taking the items' Description, Quantity, Condition, SKU#, and Platform (video game platform). I've got two Macros currently. One will trim up the SKU to a usable format (gets rid of leading letters) and the other separates multiple quantity items out to separate rows. (i.e. item #1 with a Quantity of 5 will be copied to 5 rows all with a quantity of 1) That stuff works, but I think I'm doing some unnecessary steps that could be handled with a macro.
My AMan program spits out an Excel workbook with the items. I then copy those items into my 'macro-enabled' workbook on sheet one and then run my macros. Sheet two has the properly formatted data for the labels. For example; it has formulas that trim the Description field to only use the first 60 characters. (some of the descriptions are pretty long)
I'm feeling like that second sheet isn't really necessary. I would like to copy the items into Sheet 1 and run a macro that does all of that formatting for me on sheet 1.
Separate items with multiple quanities onto separate rows (I do have a working macro for this.) Remove leading letters from SKU (working macro) and then put SKU in seven digit number format (#######) Reduce the Description to only the first 60 characters. Reduce the Condition to only the first 2 characters. Reduce the Platform to only the first 15 characters.Here's my current macro code and a link to my spreadsheet. Thanks in advance, guys. SKU_LABEL_FINAL.xlsm
Sub ExpandRows() Dim dat As Variant Dim i As Long Dim rw As Range Dim rng As Range Set rng = Sheets(1).UsedRange dat = rng ' Loop thru your data, starting at the last row For i = UBound(dat, 1) To 2 Step -1 ' If Quantity > 1 If dat(i, 2) > 1 Then ' Insert rows to make space Set rw = rng.Rows(i).EntireRow rw.Offset(1, 0).Resize(dat(i, 2) - 1).Insert ' copy row data down rw.Copy rw.Offset(1, 0).Resize(dat(i, 2) - 1) ' set Quantity to 1 rw.Cells(1, 2).Resize(dat(i, 2), 1) = 1 End If Next Columns("D:D").Select Selection.Replace What:="AManPro-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseEnd Sub
最满意答案
将SKU格式化为7位数字格式非常简单。 您只需要在End Sub行之前插入这行代码
Selection.NumberFormat = "0000000"
解析数据时，可以修剪每个单元格的长度。
首先，我会使用一些Const语句来指出哪些列是什么，以及它们需要的长度。 您可以只使用代码中的数字，但这样可以在将来更新时更容易更新。 在最后的Dim下方添加
Const DESCRIPTIONLENGTH = 60 Const DESCRIPTIONCOLUMN = 2然后当你遍历每一行时，你会更新单元格的值 - 在For i =...下面添加For i =...
'Format each column's data dat(i, DESCRIPTIONCOLUMN).Value = Left(dat(i, DESCRIPTIONCOLUMN), DESCRIPTIONLENGTH)我希望描述列的代码可以帮助您构建其他列所需的内容。
To format your SKUs into a 7 digit number format is pretty simple with what you have. You would just need to insert this line of code before the End Sub line
Selection.NumberFormat = "0000000"
You could trim the length of each cell when you parse through the data.
First, I would use some Const statements to note which columns are what, and what length they need to be. You could just use the numbers in the code, but this makes it easier to update things in the future if they change. Add below the final Dim
Const DESCRIPTIONLENGTH = 60 Const DESCRIPTIONCOLUMN = 2Then as you loop through each row, you would then update the value of the cell - add below the For i =...
'Format each column's data dat(i, DESCRIPTIONCOLUMN).Value = Left(dat(i, DESCRIPTIONCOLUMN), DESCRIPTIONLENGTH)I'm hoping the code for the description column will help you to build what you need for the other columns as well.
使用宏格式化工作表上的数据(Using a Macro to format data on sheet)我经营一家小型零售店，我的任务是为我们的商品创建库存标签。 我将库存管理软件（AMan Pro）中的项目列表导出到Excel中，包括项目的描述，数量，条件，SKU＃和平台（视频游戏平台）。 我目前有两个宏。 一个人将SKU修剪为可用的格式（去除前导字母），另一个将多个数量项分开到单独的行。 （即数量为5的项目＃1将被复制到5行，所有数量都为1）这些东西可以工作，但我想我正在做一些可以用宏处理的不必要的步骤。
我的AMan程序用项目吐出Excel工作簿。 然后我将这些项目复制到第一页上的“启用宏”的工作簿中，然后运行我的宏。 第二张表格具有格式正确的标签数据。 例如; 它具有修剪Description字段的公式，仅使用前60个字符。 （有些描述很长）
我觉得第二张不是必需的。 我想将这些项目复制到Sheet 1中并运行一个宏， 在表1中为我完成所有格式化。
将具有多个量的项目分隔到单独的行上（我确实有一个工作宏。） 从SKU（工作宏）中删除前导字母，然后将SKU以七位数字格式（#######）放入 将描述简化为前60个字符。 将条件减少到只有前2个字符。 将平台减少到前15个字符。这是我当前的宏代码和我的电子表格的链接。 先谢谢你，伙计们。 SKU_LABEL_FINAL.xlsm
Sub ExpandRows() Dim dat As Variant Dim i As Long Dim rw As Range Dim rng As Range Set rng = Sheets(1).UsedRange dat = rng ' Loop thru your data, starting at the last row For i = UBound(dat, 1) To 2 Step -1 ' If Quantity > 1 If dat(i, 2) > 1 Then ' Insert rows to make space Set rw = rng.Rows(i).EntireRow rw.Offset(1, 0).Resize(dat(i, 2) - 1).Insert ' copy row data down rw.Copy rw.Offset(1, 0).Resize(dat(i, 2) - 1) ' set Quantity to 1 rw.Cells(1, 2).Resize(dat(i, 2), 1) = 1 End If Next Columns("D:D").Select Selection.Replace What:="AManPro-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False结束子
I run a small retail store and I've been tasked with creating inventory labels for our items. I export a list of items from an inventory management software (AMan Pro) into Excel taking the items' Description, Quantity, Condition, SKU#, and Platform (video game platform). I've got two Macros currently. One will trim up the SKU to a usable format (gets rid of leading letters) and the other separates multiple quantity items out to separate rows. (i.e. item #1 with a Quantity of 5 will be copied to 5 rows all with a quantity of 1) That stuff works, but I think I'm doing some unnecessary steps that could be handled with a macro.
My AMan program spits out an Excel workbook with the items. I then copy those items into my 'macro-enabled' workbook on sheet one and then run my macros. Sheet two has the properly formatted data for the labels. For example; it has formulas that trim the Description field to only use the first 60 characters. (some of the descriptions are pretty long)
I'm feeling like that second sheet isn't really necessary. I would like to copy the items into Sheet 1 and run a macro that does all of that formatting for me on sheet 1.
Separate items with multiple quanities onto separate rows (I do have a working macro for this.) Remove leading letters from SKU (working macro) and then put SKU in seven digit number format (#######) Reduce the Description to only the first 60 characters. Reduce the Condition to only the first 2 characters. Reduce the Platform to only the first 15 characters.Here's my current macro code and a link to my spreadsheet. Thanks in advance, guys. SKU_LABEL_FINAL.xlsm
Sub ExpandRows() Dim dat As Variant Dim i As Long Dim rw As Range Dim rng As Range Set rng = Sheets(1).UsedRange dat = rng ' Loop thru your data, starting at the last row For i = UBound(dat, 1) To 2 Step -1 ' If Quantity > 1 If dat(i, 2) > 1 Then ' Insert rows to make space Set rw = rng.Rows(i).EntireRow rw.Offset(1, 0).Resize(dat(i, 2) - 1).Insert ' copy row data down rw.Copy rw.Offset(1, 0).Resize(dat(i, 2) - 1) ' set Quantity to 1 rw.Cells(1, 2).Resize(dat(i, 2), 1) = 1 End If Next Columns("D:D").Select Selection.Replace What:="AManPro-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseEnd Sub
最满意答案
将SKU格式化为7位数字格式非常简单。 您只需要在End Sub行之前插入这行代码
Selection.NumberFormat = "0000000"
解析数据时，可以修剪每个单元格的长度。
首先，我会使用一些Const语句来指出哪些列是什么，以及它们需要的长度。 您可以只使用代码中的数字，但这样可以在将来更新时更容易更新。 在最后的Dim下方添加
Const DESCRIPTIONLENGTH = 60 Const DESCRIPTIONCOLUMN = 2然后当你遍历每一行时，你会更新单元格的值 - 在For i =...下面添加For i =...
'Format each column's data dat(i, DESCRIPTIONCOLUMN).Value = Left(dat(i, DESCRIPTIONCOLUMN), DESCRIPTIONLENGTH)我希望描述列的代码可以帮助您构建其他列所需的内容。
To format your SKUs into a 7 digit number format is pretty simple with what you have. You would just need to insert this line of code before the End Sub line
Selection.NumberFormat = "0000000"
You could trim the length of each cell when you parse through the data.
First, I would use some Const statements to note which columns are what, and what length they need to be. You could just use the numbers in the code, but this makes it easier to update things in the future if they change. Add below the final Dim
Const DESCRIPTIONLENGTH = 60 Const DESCRIPTIONCOLUMN = 2Then as you loop through each row, you would then update the value of the cell - add below the For i =...
'Format each column's data dat(i, DESCRIPTIONCOLUMN).Value = Left(dat(i, DESCRIPTIONCOLUMN), DESCRIPTIONLENGTH)I'm hoping the code for the description column will help you to build what you need for the other columns as well.
发布评论