Put numbers in column B cells when Value Change in Column A cells

[K]

Hi all, I have data in column A (see below)

ROW A---------Column
1 Heading
2 BTC
3 BTC
4 BTC
5 GIS
6 GIS
7 VXZ
8 VXZ
9 BTC
10 BTC

I want macro on button which should produce result in column B
something like (see below)

ROW A B---------Columns
1 Heading Heading
2 BTC 1
3 BTC 1
4 BTC 1
5 GIS 2
6 GIS 2
7 VXZ 3
8 VXZ 3
9 BTC 4
10 BTC 4

In other words I want macro to check in column A cells and put numbers
in column B cells and when where value become different in column A
cells then it should also change the value in column B cells. Please
can any friend can help.

 

[Mike H]

Hi,

You don't need a macro for this you can use this in B2 dragged down

=IF(A2=A1,MAX($B$1:B1,1),MAX($B$1:B1)+1)

but if that's what you want then right click your sheet tab, view code and
paste this in and run it.

Sub marine()
Dim num As Long
Dim Myrange As Range
num = 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Range("A3:A" & lastrow)
Range("B2").Value = num
For Each c In Myrange
If c.Value = c.Offset(-1).Value Then
c.Offset(, 1).Value = num
Else
num = num + 1
c.Offset(, 1).Value = num
End If
Next
End Sub

 

[Arvi Laanemets]

Hi

Another formula for B2 is
=IF(A2="","",SUM(B1,--(A2<>A1)))
(Copy formula down - and you can have pre-prepared cells with formula there
too)

Arvi Laanemets

 

[K]

Hi

Another formula for B2 is
=IF(A2="","",SUM(B1,--(A2<>A1)))
(Copy formula down - and you can have pre-prepared cells with formula there
too)

Arvi Laanemets










- Show quoted text -

thanks guys