Find in VBA

[datauser]

Hello! I am getting an error message when I use this find formula in vba. It highlights the comma in the red.

Range("B5").Formula2 = "=MID(F5,FIND(",",F5) + 1,FIND(" -", F5) - FIND(",",F5) -1)"
Range("B5").AutoFill Range("B5:B" & Range("C" & Rows.Count).End(xlUp).Row)

 

[Skyybot]

Try double quotes
Range("B5").Formula2 = "=MID(F5,FIND("","",F5) + 1,FIND("" -"", F5) - FIND("","",F5) -1)"

 

[datauser]

Try double quotes
Range("B5").Formula2 = "=MID(F5,FIND("","",F5) + 1,FIND("" -"", F5) - FIND("","",F5) -1)"

This worked! Thank you

 

[Peter_SSs]

A couple of comments.

• Since you have Microsoft 365 you have more functions available to you now so you could consider this simpler formula
  
  

  Range("B5").Formula2 = "=INDEX(TEXTSPLIT(F5,{"","","" -""}),2)"

  
  
• Whichever formula you use you can enter them all at once rather than entering one and then using AutoFill. For example
  
  

  Range("B5:B" & Range("C" & Rows.Count).End(xlUp).Row).Formula2 = "=INDEX(TEXTSPLIT(F5,{"","","" -""}),2)"