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

 

[Micron]

What is the value of x3 when it processes that line? IMO not a good idea to Dim as Variant unless you expect the value could be Null as well as string or number type. I have never seen dashes in front of any string function: --Right(x2,4) so it doesn't look right to me.
Try inserting a msgbox or debug.print to the immediate window to check what x3 is: Debug.Print x3

You should learn how to step through code and watch what it does and verify variables as you go (if you don't already know how).

 

[SanjayGMusafir]

--Right(x2,4) so it doesn't look right to me.

After series of hits and misses, I have realized

in code

x3 = Evaluate("RIGHT(x2,4)")

VBA evaluates value of cell X2 in excel sheet and not variable x2

How to make VBA evaluate the declared variable value is still a mystery!!! I tried changing the code as below but to no use than finding the above error

Sub ClientsAdd()
'
' ClientsAdd Macro
'

'
    Call ClientsBottomSr
    ActiveCell.Offset(-1, 0).Select
  
    Dim ACell As Range, cVal As String, nNum As String, nTxt As String
   
    Set ACell = ActiveCell
    cVal = ACell.Value
    nNum = Evaluate("Right(ACell,4)")
    'nTxt = Format(x3, "0000")
    MsgBox ("The Value is : " & nNum)
  
  
End Sub

Now it gives type mismatch for nNum that is because it doesn't find ACell to Evaluate...

Please help

 

[Micron]

So same suggestions now apply to nNum and ACell and any other variable you want to inspect. I don't see why you need Evaluate at all.

 

[SanjayGMusafir]

I don't see why you need Evaluate at all.

1. VBA shall find the last four digits number used in Sr
2. Add 1 to it and
3. Create a new row with new number

for eg
Last used Sr is 'A 2346'
then it shall do all the work and create a new row with Cell Value 'A 2347'

Hope I was able to explain it well.
Thanks

 

[Cubist]

I don't see a reason for Select. A lot of the variables seem unncessary. Try:

Sub ClientsAdd()
    Call ClientsBottomSr
    Dim ACell As Range
    Dim cVal As String
    Dim nNum As String
 
    Set ACell = ActiveCell.Offset(-1, 0)
    nNum = Right(ACell.Value, 4)
  
    MsgBox ("The Value is : " & nNum)
End Sub

 

[Micron]

You still don't need evaluate. If "A 2346" is in cell A7 then nNum = Right(Range("A7"),4) does that. You don't need the ACell variable either.

 

[SanjayGMusafir]

I realized my mistake. I was not sure that we could use RIGHT function in VBA or not

 

[SanjayGMusafir]

Thanks @Cubist and @Micron

My challenge is I can only choose one as Answer. But the help you extended was useful and timely.

 

[Micron]

My challenge is I can only choose one as Answer

If it ever made a difference to how much I got paid here then it might matter to me. The fact that you pick someone to acknowledge is good enough for me. Some people (many?) don't bother. Thanks.
P.S. - do learn to troubleshoot your code. It's not hard and can save you lots of time.

I tend to guide/push people to a solution so that they might learn as much as possible in the process. I usually (but not always) don't just write code and post it. It's like giving a man a fish instead of teaching how to fish.