Excel - VBA sum +1 to line

Question

I have a question .

I’ve been testing the macros to fill out a database but I’m having a problem.

ID - Name

ZZ1 - A

ZZ2 - B

How do I add this automatically? (now ZZ3) I have the following code that works with only numbers. I have tried several ways but it always gives error because of the variables.

Sub novo()
 Dim codigo, x As Double

'Descobre a ultima linha
 x = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

 If x = 1 Then
    codigo = x
 Else
    codigo = Sheets(2).Cells(x, 1) + 1
End If
'Adiciona o novo Código
Sheets(1).Range("F5") = codigo
'Limpar campos
End Sub

Answer 1

EDIT:

Regex

Enable Regex in Excel

1. Regex needs to be enabled, Enable the Developer mode
2. In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.
3. Go to 'Tools' -> 'References...' and a window will open.
4. Search for 'Microsoft Vbscript Regular Expressions 5.5', as in the image below. And enable this option.

Standard

A simple Regex that identifies if it has numbers \d+ and has letters without special characters [a-zA-Z]+

Code in Excel VBA

Dim codigo As String, x As Long
Dim objMatches As Object, objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")
'Regex
objRegExp.Pattern = "\d+"
objRegExp.Global = True

'Descobre a ultima linha
x = Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).Row

If x = 1 Then
    codigo_novo = x
Else
    codigo = Sheets(2).Cells(x, 1)
    Set objMatches = objRegExp.Execute(codigo)
    If objMatches.Count <> 0 Then
        For Each m In objMatches
            numero = CLng(m) + 1
        Next m
    End If
    objRegExp.Pattern = "[a-zA-Z]+"
     Set objMatches = objRegExp.Execute(codigo)
    If objMatches.Count <> 0 Then
        For Each m In objMatches
            letras = m
        Next m
    End If
End If

codigo_novo = letras & numero
'Adiciona o novo Código
Sheets(1).Range("F5") = codigo_novo

Original Response

1 - Statements

Thus Dim codigo, x As Double you are declaring only x as Double and code is as Variant, that Excel tries to find the data type (data type) of the variable. The right thing would be: Dim codigo As Double, x As Long

Use the Long variable to get the last line, because lines are integers, but if you declare as Integer, errors occur if you pass 32767 lines. Long can go up to 2147483647

2 - Last Line

You are not specifying the spreadsheet correctly to find the last line, you can accomplish this in several ways. Follow the example of two:

x = Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).Row

Or

Dim ws2 As Worksheets
Set ws2 = ThisWorkbook.Sheets(2)
With ws2
    x = .Cells(.Rows.Count, 1).End(xlUp).Row
    'Outros códigos com a Planilha2 podem ser usados com .Cells, sem especificar a planilha em questão
End With

3 - Sum

You are performing an operation to add the cell value to 1, so if you want to add the line number: codigo = Sheets(2).Cells(x, 1).Row + 1

But if you want to add the value of the cell and it is as text, try to convert to Double first: codigo = CDbl(Sheets(2).Cells(x, 1)) + 1

To add Letters, join two Strings: codigo = CStr(Sheets(2).Cells(x, 1)) & "1"

Or if there are also Letters in this column, you can check if the cell is a number:

 Dim codigo As Double, x As Long

'Descobre a ultima linha
 x = Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).Row

 If x = 1 Then
    codigo = x
 Else
    If IsNumeric(CDbl(Sheets(2).Cells(x, 1).Value)) Then
        codigo = CDbl(Sheets(2).Cells(x, 1).Value) + 1
    Else
        codigo = xlErrNA
    End If
End If
'Adiciona o novo Código
Sheets(1).Range("F5") = codigo
'Limpar campos