Macro to apply Serial No IF ISBLANK

user_16472554 0
Viewed 62

I have applied a macro to insert serial no IF ISBLANK Cell and Here I know we can apply this with formula also why I have applied a macro to insert serial no IF ISBLANK Cell because I want to insert serial no automatic with applying the macro that's why I have created a macro for this and here is a problem I have getting (Compile Error: Argument Not Optional) in this macro

here I want when the macro applied serial no and then the formula should be hidden in that cell range


Sub SerialNo()
  Dim LastRow As Long
  LastRow = Cells(Rows.count, "B").End(xlUp).Row
  With Range("A1:A" & LastRow).FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",COUNTA(R1C2:RC[1]))"
  Range.Value
End Sub
excel-formula vba excel
1 Answers

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim MyFormula As String
    Dim LastRow As Long
    
    '~~> This is your formula
    MyFormula = "=IF(ISBLANK(B1),"""",COUNTA($B$1:B1))"
    
    '~~> Change this to relevant sheet
    Set ws = Sheet1
  
    With ws
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("A1:A" & LastRow).Formula = MyFormula
    End With
End Sub

If you want to convert the formula to values later, you can use

.Range("A1:A" & LastRow).Value = .Range("A1:A" & LastRow).Value

Note:

  1. Use Objects. Much easier to handle and understand your code.
  2. While R1C1 notation is good, I prefer A1 notation. Makes it easier to see which cell(s) my formula is referring to. (This is just my personal opinion and is no way of implying that this is the best way to handle this situation. Ultimately it is a personal choice)
user_11405790
Related