Why does not this macro automatically number?

[Visor]

I have a userform and I want to load an automatic numbering in column A, starting in cell 10.

I do not find the logic
first I place in the range A10 the value of 1
then I ask him to look for the last occupied row of column A,
The answer is A10
and then I ask that the next empty row is to say A10 + 1 = A11
Add the value of the last row A10 to the value of 1
the result should be 2 in the range A11

In the next load of the userform it should add to the A11 the value of 1 and its result be 3
Unfortunately in A10 you will always have to load the value of 1 since at the beginning the cell would be empty and from there all part of a row without data

Take into account that in A9 I have titles (string) and these do not add up (they would give error)

sub num()
Hoja25.Range("A10").Value = 1

nfil = Hoja25.Range("A10:A" & Rows.Count).End(xlDown).Row

Hoja25.Range("A" & nfil + 1).Value = Hoja25.Range("A" & nfil).Value + 1
end sub

Apparently everything is fine, but it causes me error

I thank you in advance for your observations and suggestions

 

[Marc L]

Just check the value of the variable …​

 

[Visor]

ok Thanks
I reviewed and understand that the variable shown is the row and not the value.
I have changed to this mode and it does not work

Hoja25.Range("A10").Value = 1
n = Hoja25.Range("A10:A" & Rows.Count).End(xlDown).Value

nfil = Hoja25.Range("A10:A" & Rows.Count).End(xlDown).Row
'
Hoja25.Cells(nfil + 1, 1).Value = n + 1

 

[Marc L]

Now, the variables have expected values or not ?
'Cause when a code does not work its logic just fails …​

 

[Visor]

Thanks
I do not understand,
then n should be equal to 1 because it starts by placing the value of 1 in A10

nfil should be equal to the last row with data that is equal to 10
therefore nfil +1 should be equal to A11 and there should be added n +1

 

[Visor]

example

Hoja25.Range("A10").Value = 1
n = Hoja25.Range("A10:A" & Rows.Count).End(xlDown).Value
MsgBox ("valor ") & n

Result = valor

This not show the valor in the msgbox

This go to the 1048576 row

 

[Belleke]

I have a userform and I want to load an automatic numbering in column A, starting in cell 10.

Does this helps?
see attached

 

[Visor]

Thanks Belleke, actually I understand it configured in this way, could you check this archive file?

 

[p45cal]

Is this what you're trying to do?

 

[Visor]

Thanks p45cal, of course the difference Range ("A10" & .....
and End (xlDown) by Range ("A" ....
and End (xlUp)
However, it is not so simple, because if we analyze your macro, by completely deleting the numbers from the A10 range when you execute the macro, the number 1 and 2 are added at the same time, which should not happen.

But with your contribution I made some additions with codicionales, apparently this works well, and it would be a way that may not be the most appropriate
It's more or less what Marc L said

Private Sub UserForm_Initialize()
If Hoja1.Range("A10").Value = "" Then
Hoja1.Range("A10").Value = 1
Else
n = Hoja1.Range("A" & Rows.Count).End(xlUp).Value
nfil = Hoja1.Range("A" & Rows.Count).End(xlUp).Row
Hoja1.Cells(nfil + 1, 1).Value = n + 1
LbNum.Caption = n
End If
End Sub

Thanks for your support

 

[chirayu]

OK so all you want is each time UserForm_Initialize, Add a number in column A from A10 onwards? that's easy

Dim LstRow As Integer
With Sheet1
    If .Range("A10") = "" Then
        .Range("A10") = 1
    Else
        LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A" & LstRow + 1) = .Range("A" & LstRow) + 1
    End If
End With

 

[Visor]

Excelent!!
Is what I wanted, it starts inclusive when there is no number, and of course, the conditional was necessary
Thank you for your contribution has served me and is more simplified codification