Excel - VBA sum +1 line

[tahishae]

I have a question.

I've been testing the macros to fill a database, but I'm having a problem.

ID - Name

ZZ1 - A

ZZ2 - B

How can I add this automatically? (Now ZZ3) I have the following code that works with only numbers. I've tried various forms but it always makes a mistake because of the variable.

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

[Jolied]

EDIT:

Regex

Enable Regex in Excel

1. RegEx needs to be enabled,

   Standard

   A simple Regex that identifies if you have numbers \d+ and if you have 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 answer

   1 - Declarations

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

   Use the variable Long to get the last line, as rows are whole numbers, but if you declare as Integer, errors will occur if passing 32767 Lines. Long can go to 2147483647

   2 - Last Line

   You are not specifying the worksheet correctly to find the last line, you can perform this in various ways. Here’s 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 - Soma

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

   But if you want to sum the cell value 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, one 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