提示值，然后复制，但添加文本 [英] Prompt for value, then copy but add text

问题描述

我编写了一个宏，提示用户输入一个名字，然后是一个数字。这用于在标题行之后填充列G中的前3个单元格。我真正需要做的是对接下来的3行重复，但是添加10个用户输入的数字。

  Sub Set_Tag（）
 
 Dim Tag，TagName As String 
 Dim x，TagNum As Integer 
 
 TagName = InputBox（什么是产品标签名称？苹果，标签名称）
 TagNum = InputBox（什么是第一个产品标签＃？Ex。500，Tag＃）
 
 Tag = TagName& _& TagNum 
 x = Application.WorksheetFunction.CountIf（Range（J：J），）
 
 ActiveSheet.Range（G2）Value = TagName& _& TagNum 
 ActiveSheet.Range（G3）。Value = TagName& _& TagNum& _T
 ActiveSheet.Range（G4）。Value = TagName& _& TagNum& _NE
 
 
 End Sub 
  

所以如果用户在两个提示中输入示例 Apple 和 500 ，然后单元格G2-G4变为：

  Apple_100 
 Apple_100_T 
 Apple_100_NE 
  

下一步是重复这一步，直到它达到行x（总是3的倍数）。因此，如果x计数9行，单元格G2-10中的值应为：

  Apple_100 
 Apple_100_T 
 Apple_100_NE 
 Apple_110 
 Apple_110_T 
 Apple_110_NE 
 Apple_120 
 Apple_120_T 
 Apple_120_NE 
  

如何改善我的宏来实现这一点？谢谢！

解决方案

用下列三行代替：

 使用ActiveSheet.Range（G2）
对于i = 1至x步骤3 
 .Item（i + 0）= TagName& _& TagNum + k 
 .Item（i + 1）= TagName& _& TagNum + k& _T
 .Item（i + 2）= TagName& _& TagNum + k& _NE
k = k + 10 
下一个
结束
  

在顶部添加以下行：

  Dim i As Long，k As Long 
  

就是这样。

注意：除此之外，您应该更正您的Dim语句。例如， Dim Tag，TagName As String 指示VBA，标签是一个Variant类型，不是String。同样适用于 x 。您的代码使 x 一个Variant而不是整数。最后，这里没有引人注目的理由来使用整数。相反，你应该使用Longs。 VBA针对Longs进行了优化。

I've written a macro that prompts the user for a name and then a number. This is used to populate the first 3 cells in column G after the header row. What I really need the macro to do is then repeat this for the next 3 rows, but add 10 the number the user entered.

Sub Set_Tag()

Dim Tag, TagName As String
Dim x, TagNum As Integer

TagName = InputBox("What is the product tag name? Ex. Apple", "Tag Name")
TagNum = InputBox("What is the first product tag #? Ex. 500", "Tag #")

Tag = TagName & "_" & TagNum
x = Application.WorksheetFunction.CountIf(Range("J:J"), " ")

ActiveSheet.Range("G2").Value = TagName & "_" & TagNum
ActiveSheet.Range("G3").Value = TagName & "_" & TagNum & "_T"
ActiveSheet.Range("G4").Value = TagName & "_" & TagNum & "_NE"


End Sub

So if the user enters the example Apple and 500 in the two prompts, cells G2-G4 then become:

Apple_100
Apple_100_T
Apple_100_NE

Next step is to have this repeated until it reaches row x (which is always a multiple of 3). So if x counts 9 rows, the values in cells G2-10 should be:

Apple_100
Apple_100_T
Apple_100_NE
Apple_110
Apple_110_T
Apple_110_NE
Apple_120
Apple_120_T
Apple_120_NE

How can I improve my macro to achieve this? Thanks!

解决方案

Replace the bottom three lines with these:

With ActiveSheet.Range("G2")
    For i = 1 To x Step 3
        .Item(i + 0) = TagName & "_" & TagNum + k
        .Item(i + 1) = TagName & "_" & TagNum + k & "_T"
        .Item(i + 2) = TagName & "_" & TagNum + k & "_NE"
        k = k + 10
    Next
End With

And..

Add this line at the top:

Dim i As Long, k As Long

That's it.

Note: as an aside you should correct your Dim statements. For example, Dim Tag, TagName As String instructs VBA that Tag is a Variant type not a String. The same holds for x. Your code makes x a Variant, not an Integer. Finally, there is no compelling reason to use Integers here. Instead you should use Longs. VBA is optimized for Longs.

这篇关于提示值，然后复制，但添加文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！