CODEWITHSUNDEEP
vbaexcelexcel-2010
wongnog
wongnog

Reputation: 111

Repeat or change cell value based on other cell

I have a macro which fills values in Column G (starting at cell G2) based on user prompts for the start value, then repeats to the end of the column by adding 10 to the value each time. 

Sub Macro1()

Dim TagName As String
Dim TagNum As Long, k As Long

' Prompts user for tag names/numbers
TagName = InputBox("What is the product tag name? Ex. APPLE")
TagNum = InputBox("What is the starting tag #? Ex. 10")


' Set values in column G, up to last row in column J
k = 0
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
End With

With ActiveSheet.Range("G2")
    For i = 1 To LastRow Step 1
        .Item(i + 0) = TagName & "_" & TagNum2 + k
        k = k + 10
    Next
End With

End Sub

This works perfectly if Column C has unique values in each line, like so: Before

But if the value in Column C repeats, I want the same in Column G, like this: enter image description here

Upvotes: 1

Views: 518

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

I think there are couple of errors. Is this what you are trying?

Sub Sample()
    Dim TagName As String, sTag As String
    Dim TagNum As Long, k As Long

    ' Prompts user for tag names/numbers
    TagName = InputBox("What is the product tag name? Ex. APPLE")
    TagNum = InputBox("What is the starting tag #? Ex. 10")

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
        k = TagNum
        .Range("G2").Value = TagName & "_" & k
        For i = 3 To LastRow
            '~~> Increment k only if the values do not match
            If .Range("C" & i).Value <> .Range("C" & i - 1).Value _
            Then k = k + 10

            .Range("G" & i).Value = TagName & "_" & k
        Next
    End With
End Sub

I selected "Apple" and "10" in the Input Boxes

enter image description here

Upvotes: 1

Related Questions