VBA Insert Formula and Find Text

[unknownymous]

Hi Guys,

Can someone help me how to simplify this code?

Sub Macro2()
ActiveCell.FormulaR1C1 = "=RC[3]&R1C2&R[1]C[3]"
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
Range("B1596").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
End Sub

--------------------------------------------------------------------------
In cell B1, there's a space which I use in formula. In B2, I will put a formula combining E2&E3 so I have this formula in B2=E3&$B1$&E3 which I need to drag the formula up to the last non blank cell and hardcode it

Also, I need to look for the word "DONE" in column C and if the value exist additional row will be created after it.

Any help will be much appreciated.


Thanks!

 

[Fluff]

How about

Sub Unknownymous()
   With Range("B2", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .FormulaR1C1 = "=rc[3]&"" ""&r[1]c[3]"
      .Value = .Value
   End With
End Sub

 

[unknownymous]

Thanks Fluff!

 

[Fluff]

You're welcome