0

I am working on a vba small code which extract date from Column A into Column C. the current code puts the formulas to extract date from Cell C2 to C2500, However if the data in Column A ends at line A600 it still goes down till C2500. Is it possible if we amend the code to stop pasting the formula exactly at the last line of Column A. so that i do not need to manually delete those cells "#Value". e.g. see print shot.

Sub Formula_property()
   
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = Sheets("Sheet3")
    wb.Activate
    ws.Select
    
Range("C2:C2500").Formula = "=extractDate(A2:A2)"

End Sub

Screenshot

Ike
  • 9,580
  • 4
  • 13
  • 29
Salman Shafi
  • 249
  • 9

1 Answers1

0

Assuming that all columns have the same number of rows (except column B which is empty) - we can use CurrentRegion to get the size of the target "for free"

Sub formula()

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet3")

Dim rg As Range

Set rg = ws.Range("A1").CurrentRegion
Set rg = rg.Columns(1)

With rg
    .Offset(1, 1).Resize(.Rows.Count - 1).formula = "=extractDate(A2:A2)"
End With
End Sub

BTW: activate/select is not necessary - I recommend reading How to avoid using select.

Ike
  • 9,580
  • 4
  • 13
  • 29
  • Dear @IKE the provided code is not extracting the date from Column A – Salman Shafi Jan 18 '23 at 13:29
  • This code doesn't extract the date - it only inserts the formula "extractDate". So the question is: is the formula inserted by the code? If yes - but the result is not as expected, then your UDF "extractDate" is somehow wrong. – Ike Jan 18 '23 at 13:43