VBA to add new record - Declaring variable

[SanjayGMusafir]

Hi Experts
I was writing following VBA code to add a new record. But it's constantly giving 'Type Mismatch' Error with variable x4

The new record has to be "A 1111" (A + space + 4 digits next to last used)

I must be missing some trick.

Also, I'm thinking of VBA to ask how many records to add
And it should repeat the task to add that number of records

Please help
Thanks in Advance
Regards
Sanjay Gulati Musafir

Sub ClientsAdd()
'
' ClientsAdd Macro
'

'
    Call ClientsBottomSr
    ActiveCell.Offset(-1, 0).Select
   
    Dim x1 As Range, x2 As Variant, x3 As Variant, x4 As String
    
    Set x1 = ActiveCell
    x2 = x1.Value
    x3 = Evaluate("--RIGHT(x2,4)+1")
    x4 = "A " & x3
   
    'To add new record row
    Call ClientsBottomSr
    ActiveCell.Value = x4
   
    'To add Current Date to the new created record
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Date
   
    'To go to Tl
    Call GoClientsTl
   
End Sub

 

[SanjayGMusafir]

There is one challenge remaining that is how to make it repeat the number of times I mention -

Sub ClientsAdd()
'
' ClientsAdd Macro
'

'
    Dim ACell As Range, x1 As Long, x2 As String
     
    x1 = Application.InputBox("", "No. of Records to be added", Type:=1)
    
    Call ClientsBottomSr
    
    Set ACell = ActiveCell.Offset(-1, 0).Select
    x2 = "A " & Right(ACell, 4) + 1
    'MsgBox x2
        
    'To add new record row
    Call ClientsBottomSr
    ActiveCell.Value = x2
    
    'To add Current Date to the new created record
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Date
    
    'To go to Tl
    Call GoClientsTl
    
End Sub

 

[Tom.Jones]

I have never seen dashes in front of any string function: --Right(x2,4) so it doesn't look right to me.

Result of Right(x2,4) is string say "1234" with double minus, in front of function Right, make that string, a number 1234
So Right(x2,4) return "1234" and --Right(x2,4) return 1234.
It is the same for Right(x2,4)+0 or 1*Right(x2,4) or Right(x2,4)/1

 

[Cubist]

There is one challenge remaining that is how to make it repeat the number of times I mention -

Sub ClientsAdd()
'
' ClientsAdd Macro
'

'
    Dim ACell As Range, x1 As Long, x2 As String
    
    x1 = Application.InputBox("", "No. of Records to be added", Type:=1)
   
    Call ClientsBottomSr
   
    Set ACell = ActiveCell.Offset(-1, 0).Select
    x2 = "A " & Right(ACell, 4) + 1
    'MsgBox x2
       
    'To add new record row
    Call ClientsBottomSr
    ActiveCell.Value = x2
   
    'To add Current Date to the new created record
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Date
   
    'To go to Tl
    Call GoClientsTl
   
End Sub

It would be clearer if you showed what the spreadsheet looks like and the desired output.

 

[SanjayGMusafir]

@Cubist I managed that. I was afraid of working with loop. Any better way of doing it Always Welcomed.

I would have shared my data but the only challenge is for some reason when I use XL2BB on my data it hangs. even for replying here I shut down my excel and work with blank worksheet else it hangs.

Sub ClientsAdd()
'
' ClientsAdd Macro
'

'
    Dim ACell As Range, nRows As Long, nSr As String, i As Integer
     
    nRows = Application.InputBox("", "No. of Records to be added", Type:=1)
     
    i = 1
    Do While i <= nRows
    
    Call ClientsBottomSr
    
    Set ACell = ActiveCell.Offset(-1, 0)
    nSr = "A " & Right(ACell, 4) + 1
    'MsgBox nSr
        
    'To add new record row
    Call ClientsBottomSr
    ActiveCell.Value = nSr
    
    'To add Current Date to the new created record
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Date
    
    i = i + 1
    Loop

    'To go to Tl
    Call GoClientsTl
   
End Sub

 

[Micron]

So Right(x2,4) return "1234" and --Right(x2,4) return 1234.
It is the same for Right(x2,4)+0 or 1*Right(x2,4) or Right(x2,4)/1

Thanks. I'm familiar with type conversion using arithmetic. I tend to use conversion functions but that's just out of habit. Also, I'm coming from Access vba so I'll have to test if -- works in a db. So usually I'd use Cint (integer) or Cdbl (double) or Csng (single) so that I don't just end up with what I might get by using arithmetic operators on strings.