CODEWITHSUNDEEP
excelvba
Hakon
Hakon

Reputation: 99

Checking If A Cell In A Different Worksheet Is A Formula

My RngTo and RngFrom Variables are both single cell ranges. I am trying to check if the RngFrom Variable Range has a formula in it. I've seen a large amount of information about the IsFormula() built in function in excel, but have no idea if I am using it correctly here. Any help or tips would be greatly appreciated. Other WB is the other workbook, and WS is the specified sheet that I am looking at the range inside. If the cell has a formula I would like to copy and paste the formula over, and if it does not I will just take the values. 

Sub ImportRange(OtherWB As String, WS As String, RngTo As String, Optional RngFrom As String)

    If RngFrom = "" Then
        RngFrom = RngTo
    End If

        If(StrComp((WorksheetFunction.IsFormula(Workbooks(OtherWB).Worksheets(WS).Range(RngFrom)),"FALSE")=0)Then 

        ActiveWorkbook.Worksheets(WS).Range(RngTo).Value = 
        Workbooks(OtherWB).Worksheets(WS).Range(RngFrom).Value
        ElseIf(StrComp((WorksheetFunction.IsFormula(Workbooks(OtherWB).Worksheets(WS).Range(RngFrom)),"TRUE")=0)Then

        Workbooks(OtherWB).Worksheets(WS).Range(RngFrom).Copy
        ActiveWorkbook.Worksheets(WS).Range(RngTo).PasteSpecial xlPasteFormulas

    End If

End Sub

Upvotes: 0

Views: 57

Answers (1)

BigBen
BigBen

Reputation: 49998

Use Range.HasFormula to check whether a cell has a formula or not.

Upvotes: 5

Related Questions