fixing convert formula to code to autonumber

[Hasson]

hi

I have this formula to autonumbering in COL B when fill in COL C

=IF(C4<>"";" serial    " &  SUBTOTAL(3;$C$4:C4);"")

I try doing with this macro but it gives error syntax

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(3)) Is Nothing Then
   LR = Range("c" & Rows.Count).End(xlUp).Row
   Range("b4:b" & LR).Formula = "=IF(C4<>""; "serials" &  SUBTOTAL(3;$C$4:C4);"")"
End If
End Sub

can any one guide me how fix it A or alternative macro
and if doesn't show the formula in column B will be great
thanks for this forum for what provide from the solutions

 

[Marc L]

Hi.​

Range("B4:B" & LR).Formula = "=IF(C4<>"""",""serials""&SUBTOTAL(3,$C$4:C4),"""")"

 

[Joe4]

Double-quotes always present issues for formulas, as they are also used for text qualifiers in VBA.

The easiest way to get the VBA syntax you need is to turn on the Macro Recorder, and record yourself entering the formula in a cell on your worksheet.
Then stop the Macro Recorder, and view the code you just recorded, and you will have the syntax you require.

 

[Marc L]

​

Or just displaying the native english cell formula in the VBE Immediate window …​

 

[Hasson]

thanks guys for correcting but I have the problem when start from row 4 it begins like this

serial 11

serial 12

serial 13

it should

serial 1

serial 2

and shouldn't show the formula in the column B it should show like value

 

[Joe4]

You can convert all the formulas to hard-coded values in VBA like this:

Range("B4:B" & LR).Formula = "=IF(C4<>"""",""serials""&SUBTOTAL(3,$C$4:C4),"""")"
Range("B4:B" & LR).Value = Range("B4:B" & LR).Value

 

[Hasson]

@Joe4 now the code works very well
thanks for provide me the solution

 

[Joe4]

You are welcome.
Glad we could help.