CODEWITHSUNDEEP
excelvba
Rupesh Ghosh
Rupesh Ghosh

Reputation: 57

Generate a alpha-numeric string based on the neighboring column value in Excel VBA

I have two columns 'Sample_No' and 'Result_Name'. Based on the different result name I want to generate a alphanumeric string 'NL-#' in the 'Sample_No' column.

For example.

Sample_No(To be generated)     Result_Name
NL-1                             ABC
NL-2                             ABC
NL-3                             ABC

NL-1                             XYZ
NL-2                             XYZ

NL-1                             PQR

NL-4                             ABC
NL-5                             ABC

Can this be done in Excel_VBA? Please help me with this! Any help will be appreciated! I tried finding a couple of solutions but couldn't reach anywhere.

Thank You!

Upvotes: 1

Views: 199

Answers (1)

Wizhi
Wizhi

Reputation: 6549

You could do this either by formula as suggested by BigBen or use VBA function "Countif".

I assume that the data looks like this:

enter image description here

VBA Code:

Sub GenerateAlphaNumber()

Dim lrow As Long

lrow = Cells(Rows.Count, "B").End(xlUp).Row 'find the lastrow in column B

For i = 2 To lrow 'Loop from row 2 until last row
'Check that cell in column B is not empty. If thats' true, then perform countif
If Cells(i, "B").Value <> "" Then Cells(i, 1).Value = "NL-" & WorksheetFunction.CountIf(Range(Cells(2, "B"), Cells(i, "B")), Cells(i, "B").Value)
Next i
End Sub

Upvotes: 1

Related Questions