Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

autonumber in column based on another filled column

0

hi

I  try  autonumber   in column A    based  on  filled  cells  in  column B   .  I   know   it  's  possible  by  formula.  but   I have  curiosity how  do  that  by  vba 

it  gives error  mismatch  in this  line 

If LR <> "" Then

this  is  the  code 

Sub autnn()

LR = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
If LR <> "" Then
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
           .Formula = "=ROW()-1"
           .Value = .Value
           End With
           End If
End Sub
Answer
Discuss

Answers

0
Selected Answer

Mussa

You code will work if there's a value in B2 only since otherwise your test If LR <> "" Then tries to test the value of a range of cells (which VBA won't do but you can set the value of a range to a single value)

This code loops through each cell (which I've called Cl) in the used range of column B and, if there's a value, puts a row number (less 1) in column A using the Offset function:

Sub autnn()

Dim Cl As Range

For Each Cl In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If Cl.Value <> "" Then
        With Cl.Offset(0, -1)
           .Formula = "=ROW()-1"
           .Value = .Value
        End With
    End If
Next Cl

End Sub

In fact you don't need the block If and With statement; you can use the single line:

If Cl.Value <> "" Then Cl.Offset(0, -1).Value =ROW()-1

If you don't need to test first if column B is filled then you could add numbers to the entire offset range using:

<<your defined range>>.Offset(0, -1).Value =ROW()-1

Hope this helps.

Discuss

Discussion

John
thanks   that  works. I    was  thinking  can  do  that   witout  loop .
Mussa (rep: 48) Oct 27, '21 at 2:48 pm
Mussa. On avoiding a loop, a single line can write a range of values if there's no condition to test- please see my revised Answer
John_Ru (rep: 6092) Oct 28, '21 at 1:46 am
thanks again for this clarification .
Mussa (rep: 48) Oct 28, '21 at 2:15 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login