CODEWITHSUNDEEP
vbaexcel
spaindc
spaindc

Reputation: 361

vba: add unique id if cell is blank

I am trying to add a unique ID only when a cell in a range is blank. This way it will keep old IDs but will create unique IDs for new lines. For the code below I am trying to create and ID using the number 14 (for 2014), and using two other variables in my dataset that are numeric (ADPNumber and PRJNumber). Then the id would vary by the integer "i", which has to be 4 digits. I get runtime error 13 for the code below on the line that says "Set myCell..."

        For Each myCell In ActiveSheet.Range("a9:a89").Cells
            Dim i
            i = 1
            'i.NumberFormat = "0000"
            If myCell.Value = "" Then
               Set myCell = "14" & ADPNumber & PRJNumber & Format(i, "0000")
               i = i + 1
            End If

        Next myCell

Upvotes: 1

Views: 361

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Try: 

myCell.Value = "14" & ADPNumber & PRJNumber & Format(i, "0000")

Upvotes: 1

Related Questions