Excel - VBA sum +1 line



  • 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



  • EDIT:

    Regex

    Enable Regex in Excel

    1. RegEx needs to be enabled, Janela Referências

      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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2