Using F2 to Convert a Formula to a VBA Function

[JP2R]

I have a bunch of data that I'm trying to format, combine data, and strip out unnecessary items.

I created several functions that I have been copying/pasting across these spreadsheets which got me to thinking that perhaps I could convert the function into VBA

-


I saw somewhere that I could use "F2" key while recording a macro that would add the function.

The test result looks something like this:


Range("C2").Select

ActiveCell.FormulaR1C1 = "=IFERROR(MID(RC1,FIND(""/"",RC[-2],22)-1,1),"""")"

Range("C2").Select


Which was cool, but I would like this to fill down the column, again that would mean more copy/paste, so I wondered about how to find the last row.


I searched and found this suggestion:


Dim LastRow As Long

With ActiveSheet.UsedRange

LastRow = .Rows(.Rows.Count).Row

End With

MsgBox LastRow


but I can't figure out how to write the two together to find the last used row and then fill the function all the way down.


Might someone be able to help?

-- Jae

 

[Hui]

I think this is what your after

[pre]

Sub Formula()
Dim c As Range
For Each c In Range("F5:F20")
c.FormulaR1C1 = "=IFERROR(MID(RC1,FIND(""/"",RC[-2],22)-1,1),"""")"
Next
End Sub

[/pre]

 

[Hui]

JP2R

Do you want to apply this formula to a Column in which the current cell is selected?

or

to a large block in which the current cell sits

Can you be more specific about describing which area you want to apply this to?

 

[JP2R]

I apologize Hui - I've been trying to work something out - this is as far as I've gotten - and yes, I do want for it to copy all the way down the columns.


I think this may need a bit of tweaking - but I feel like it copies too far down [maybe there is something in the cell that I can't see - which would explain that problem]


===== Code =====


Sub FifthFloor()

'

' FifthFloor Macro

' This is a test to learn about filling down ranges


'

'Declare a variable to use for the last row of the table

Dim Rw As Long


'Find the last row and pass that value to Rw

Rw = Range("A65536").End(xlUp).Row


'=======

'Write the formulas in Row 2 [this is for the first sheet of the worksbook]

Range("B1").FormulaR1C1 = _

"=IFERROR(MID(RC1,FIND(""interface FastEthernet"",RC1,1),30),"""")"

Range("C1").FormulaR1C1 = _

"=IFERROR(MID(RC1,FIND(""description"",RC1,1),30),"""")"


'Fill the formulas down to the end of the table

Range("B1:C1").AutoFill Destination:=Range("B1:C" & Rw)


Columns("B:C").EntireColumn.AutoFit

End Sub

===== End Code =====


-- Jae