VBA autofil formula in Collumn D if data is inputted into column B

[ljkennedy]

Hi all,
I am in need of a VBA code to be able to autofill this formula "=IF(ISNUMBER(--MID($B:$B,SEARCH("241",$B:$B),3)),MID($B:$B,18,10),0)" into column D anytime datat is inputted, this is an attempt to limit the amount of formula's are in the sheet as i dont want to have to copy the formula each time or have a long amount of cells with formula that arent needed, i tried this formula that i found on another thread but it doesnt work
Sub Ratio_BC()
Dim Lastrow As Long

Application.ScreenUpdating = False

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=IF(ISNUMBER(--MID($B,SEARCH("241",$B),3)),MID($B,18,10),0)"
Range("D2").AutoFill Destination:=Range("D2:D" & Lastrow)
ActiveSheet.AutoFilterMode = False

Application.ScreenUpdating = True

End Sub
any help would be appreciated

 

[vcoolio]

Hello LjKennedy,

Try the code amended as follows:-

Sub Ratio_BC()

Dim Lastrow As Long

Application.ScreenUpdating = False

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("D2:D" & Lastrow).Formula = "=IF(ISNUMBER(--MID($B,SEARCH(""241"",$B),3)),MID($B,18,10),0)"

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[ljkennedy]

Hi vcoolio,
Thanks for replying,
I tried running that code but it comes up with "run time error '1004' application defined or object defined error

 

[ljkennedy]

Fixed it thanks heaps vcoolio!!

 

[vcoolio]

You're welcome Ljk. I'm glad to have been able to assist.

For the sake of anyone else who may come by this way looking for a similar solution, could you please post what the 'fix' was.
Thanks Ljk.

Cheerio,
vcoolio.