EDIT:
Regex
Enable Regex in Excel
RegEx needs to be enabled, Enable Developer mode
In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.
Go to 'Tools' - > 'References ...' and a window will open.
Look for 'Microsoft VBScript Regular Expressions 5.5', as in the image below. And enable this option.
Standard
AsimpleRegexthatidentifieswhetherithas\d+numbersandwhetherithasletterswithoutspecialcharacters[a-zA-Z]+
ExcelVBAcode
DimcodigoAsString,xAsLongDimobjMatchesAsObject,objRegExpAsObjectSetobjRegExp=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 - Declarations
In this way 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 correct 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 Integer, errors will occur if you pass 32767 Lines. Long can go up to 2147483647
2 - Last Line
You are not specifying the worksheet correctly to find the last line, you can accomplish this in several ways. Here is an 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 sum 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 sum the value of the cell and it's like text, try converting to Double first: codigo = CDbl(Sheets(2).Cells(x, 1)) + 1
To add letters, add 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
