Find the last row value and increment the value in next cell

[xlnc]

Hello Experts,

I am a VBA beginner. I have been trying to write a code which checks for cell value in the last empty row. If the last cell is empty then the code should increment with the previous cell value. If cell A1 has value 10 then code should check if A2 is empty and then increment A1 value by 1 in A2 and move on to A3, A4 etc. Can any one help please?

sub test_1()
Dim LR As Long
Dim r As Range, cel As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A" & LR)
For Each cel In r
    If r.Value = "" Then
    r.Value = 1
    Else
    r.Value = r.Value + 1
    End If
   Next cel
End Sub

 

[Hui]

Do you mean like:

Sub test_1()
Dim LR As Long
Dim r As Range, cel As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A" & LR)
For Each cel In r
  If r.Value = "" Then
  r.Value = 1
  Else
  r.Offset(1).Value = r.Value + 1
  End If
  Next cel
End Sub

 

[xlnc]

Do you mean like:

Sub test_1()
Dim LR As Long
Dim r As Range, cel As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A" & LR)
For Each cel In r
  If r.Value = "" Then
  r.Value = 1
  Else
  r.Offset(1).Value = r.Value + 1
  End If
  Next cel
End Sub

Thank you so much Hui It works perfect.

 

[NARAYANK991]

Hi ,

You may have got your solution , but I am not clear on quite a few points.

LR is a number , which will point to the row with data in column A.

The statement :

Set r = Range("A" & LR)

will set r to a single cell.

A loop construct such as :

For Each cel In r

is redundant , since this loop will be executed exactly once.

By definition , the construct :

Range("A" & Rows.Count).End(xlUp).Row

will find the last row with data. Thus to check whether this is blank is illogical , unless the worksheet is a blank worksheet.

I really do not know what your real objective is ; if it is to fill the cell next to the last cell with data , with the next number , then the following code should do the job :

Sub test_1()
     Dim LR As Long
     Dim r As Range
     LR = Range("A" & Rows.Count).End(xlUp).Row
     Set r = Range("A" & LR)
     r.Offset(1).Value = r.Value + 1
End Sub

Narayan

 

[Hui]

or even

Sub test_1()
  Dim r As Range
  Set r = Range("A" & Range("A" & Rows.Count).End(xlUp).Row)
  r.Offset(1).Value = r.Value + 1
End Sub

But going to:

Sub test_1()
Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1).Value = _
  Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Value + 1
End Sub

too me is silly, even though it works well

 

[Marcych]

Hello Guys,

Could you please advise how to indicate 'starting' valuse for LastRow? (if that's possible) ?

Please see below:

 Private Sub btwNewTicket_Click()
ClearFields
Dim LR As Long
Dim r As Range, cel As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A" & LR)
For Each cel In r
If r.Value = "" Then
r.Value = 1
Else

r.Offset(1).Value = r.Value + 1
r.Value = "[BHD#" & Format(r.Value, ["00000"]) & "]"

    Me.tbTicketNo.Value = r.Value
    Me.tbTicketNo.Locked = True
    Me.tbDateOpen = Date
    Me.btnAccept.Caption = "Create"
    Me.btnAccept.Enabled = True

End If
Next cel
End Sub

The code is working well but I would like to start counting from specific value e.g. 10000 = [BHD#10000]. The case is that I don't want UserForm to write [BHD#10000] into cell A10000 but have this results starting from A1,A2,A3...

 

[Hui]

Marcych

Firstly, Welcome to the Chandoo.org Forums

Please read:
https://chandoo.org/forum/threads/new-users-please-read.294/

Can you please start a new thread at
https://chandoo.org/forum/
Select the appropriate forum
Then start a New Thread