Vba Len with If statement

[excel01noob]

Hi

I am trying to setup a Vba code for a big excel file which has accounts in a column A (formatted as number, length either 5, 6 or 7 digits)

I want to copy that account number, add a character at the end (depending on the length, for 5 digits account add character A, for 6 digits account add character K, for 7 digits account add character Z)

I was trying the first part but I am already getting a type mismatch, could you guide me on this?
thank you

Sub LENGHT()

'fills the different colums added

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

If Len(Range("A2:A" & LastRow)) = 5 Then
Range("K2:K" & LastRow) = Range("A2:A" & LastRow) & "A"

End If

End Sub

 

[Fluff]

How about

Sub excel01noob()
   With Range("K2:K" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("if(@="""","""",@&choose(len(@),,,,,""A"",""K"",""Z""))", "@", .Offset(, -10).Address))
   End With
End Sub

 

[sykes]

... and, as usual, much more long-winded than Fluff's solution (and it took a lot longer!)...

Sub append_account()
Dim rng As Range
Dim cl As Range
Dim intgr As Integer
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

With Me

    Set rng = .Range("A2:A" & LastRow)
    
    For Each cl In rng
        Select Case Len(cl.Value)
            Case 5
                cl.Offset(0, 10).Value = cl.Value & "A"
            Case 6
                cl.Offset(0, 10).Value = cl.Value & "K"
            Case 7
                cl.Offset(0, 10).Value = cl.Value & "Z"
                
             Case Else
                MsgBox "A/C number of digits not in the range 5 to 7!"
                
        End Select
    
    Next

End With

End Sub

 

[excel01noob]

it works with Fluff's code, thank you!

sykes, your's seem to not work, it stops here "With Me".
I see added value with the msgbox warning though!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sykes]

excel01noob​

I realise that Fluff's solution has done the trick, but just to answer your point (and for the benefit of anyone else referring to this post in the future)...

... I used the Me keyword, thinking you'd realise that the code needed to go into the sheet module, of the sheet in question - because if you fire the code when the required sheet isn't the active one, it'll try and run on whatever's active at the time, and will probably error-out. Unfortunately you've not stated how you're calling this procedure, so I had to guess.

If you needed a solution which could be called from anywhere, then refer to the sheet, specifically, thus (replace "Test" with the actual name of your worksheet):

Sub append_account_nos()
Dim rng As Range
Dim cl As Range
Dim LastRow As Long

    With Sheets("Test")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LastRow)
        
            For Each cl In rng
                Select Case Len(cl.Value)
                    Case 5
                        cl.Offset(0, 10).Value = cl.Value & "A"
                    Case 6
                        cl.Offset(0, 10).Value = cl.Value & "K"
                    Case 7
                        cl.Offset(0, 10).Value = cl.Value & "Z"
        
                     Case Else
                        MsgBox "A/C number of digits not in the range 5 to 7!"
                End Select
            Next
    End With

End Sub

Hope that's of interest.