vba help - Left formula not working, error type mismatch

[Mallesh23]

Hi Team,

Getting Type mismatch , unable to apply left function.
Whats wrong in my code.

Sub Test()
  
  Dim sht As Worksheet
  Set sht = ThisWorkbook.Worksheets("Sheet1")
  
  Dim lr As Long
  lr = sht.Range("a1").CurrentRegion.Rows.Count
  
  With sht
        With .Range(.Cells(2, 2), .Cells(lr, 2)).FormulaR1C1 = "=LEFT(RC1,3)" 'Error Type Mismatch.
            .Value = .Value
        End With
    End With
End Sub

Account NO	First 3 Digit character
26808​	268
35147​	351
36264​	362
33102​	331
36766​	367
25526​	255
25944​	259
25888​	258
36852​	368
35466​	354

Thanks
mg

 

[Fluff]

You need to move the .FormulaR1C1 part to a new line

 

[RoryA]

That should be split into 2 lines:

With .Range(.Cells(2, 2), .Cells(lr, 2))
    .FormulaR1C1 = "=LEFT(RC1,3)" 'Error Type Mismatch.
    .Value = .Value
End With

​

 

[VBE313]

What are you trying to do? Add =LEFT in Column B for the cells in Column A?

 

[Mallesh23]

Hi Fluff /Rory

Thanks for your help, understood.

Thanks
mg

 

[Fluff]

Glad we could help & thanks for the feedback.