For Part 1 of my request, the following code helped me populate cells G2:G10
Link to Part 1: Prompt for value, then copy but add text
Sub Set_Tag()
Dim TagName As String
Dim x As Long, TagNum As Long, i As Long, k As Long
TagName = InputBox("What is the product tag name? Ex. Apple", "Tag Name")
TagNum = InputBox("What is the first product tag #?", "1st Tag #")
x = Application.WorksheetFunction.CountIf(Range("J:J"), " ")
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
End Sub
For Part 2, I need the macro to continue populating values in Column G with different values. The starting cell would be at row x+2, or G11 in the above example. I have code that works for what I need, the only thing is G11 is hardcoded as the starting cell. I'd like to replace that with something more logical, as it may not be the starting cell for all files.
Sub Set_Tag2()
Dim TagName As String
Dim x As Long, TagNum2 As Long, i As Long, k As Long
TagName = InputBox("What is the product tag name? Ex. Apple", "Tag Name")
TagNum2 = InputBox("What is the second PDW tag #?", "2nd Tag #")
x = Application.WorksheetFunction.CountIf(Range("J:J"), " ")
With ActiveSheet
LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
End With
With ActiveSheet.Range("G11")
For i = 1 To LastRow Step 1
.Item(i + 0) = TagName & "_RED_" & TagNum2 + k
k = k + 10
Next
End With
End Sub
So if the user inputs Apple as TagName and 1060 as TagNum2, the remaining cells would populate as follows:
Apple_RED_1060
Apple_RED_1070
Apple_RED_1080
...continued until LastRow. Can someone help me replace With ActiveSheet.Range("G11") with something more logical and dynamic?
