To add the excel formulas into VBA Macro with condition-CodePudding

I have recorded some formulas into Macros and they are functioning properly, however I am not able to update them so that they should select the range themselves where the data ends in the last end in Column C. These 3 formulas extracts Date, File Name and Status of Files from Column A. As you see for now the range is e.g. "F3 to F313" where next time if the Data in Column C is up to C500 Range than I have to manually copy and paste the formulas. Is there anyway these 3 formulas should automatically detect the last text cell from Column C and ends there. That would be much helpful.

To Extract Date Sub Macro13() 'To Extract Date

ActiveCell.FormulaR1C1 = "=extractDate(RC[-1])"
Range("D2").Select
Selection.Copy
Range("D3:D313").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

To Find Status of File

Sub Macro15() 'To Find Status of File

ActiveCell.FormulaR1C1 = _
    "=IFERROR(LOOKUP(2^15,SEARCH({""Feed"",""Feed 1"",""Feed 2""},RC[-3]),{""Feed"",""Feed 1"",""Feed 2""}),""Combine"")"

Range("F2").Select

Selection.Copy

Range("F3:F313").Select

ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

To extract File Name

Sub Macro17() 'To extract File Name

ActiveCell.FormulaR1C1 = _
    "=IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ABCD - GAMA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ALPHA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9," & _
    "0},RC[-2]&""1234567890""))-1))=""ABCD - BETA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""DBETA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""A"",LEFT(RC[-2]," & _
    "MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 6),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))="""",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ABETA"",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 6),LEF" & _
    "T(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2] & ""1234567890""))-1))))))))"
Range("E2").Select
Selection.Copy
Range("E3:E313").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

CodePudding user response：

Try this:

Sub TestThis()
    Dim LastRow As Long, ws As Worksheet
    Set ws = ActiveSheet

    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("D2:D" & LastRow).FormulaR1C1 = "=extractDate(RC[-1])"
    ws.Range("F2:F" & LastRow).FormulaR1C1 = "=IFERROR(LOOKUP(2^15,SEARCH({""Feed"",""Feed 1"",""Feed 2""},RC[-3]),{""Feed"",""Feed 1"",""Feed 2""}),""Combine"")"
    ws.Range("E2:E" & LastRow).FormulaR1C1 = _
    "=IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ABCD - GAMA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ALPHA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9," & _
    "0},RC[-2]&""1234567890""))-1))=""ABCD - BETA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""DBETA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""A"",LEFT(RC[-2]," & _
    "MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 6),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))="""",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ABETA"",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890"")) 6),LEF" & _
    "T(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2] & ""1234567890""))-1))))))))"
End Sub