Using Text Formulas in VBA

**bungeejumper2** · 03-01-2013, 01:27 PM

Hi All,

I've stumbled into an issue while trying to use a text formula in VBA to set the value of certain cells. Hypothetically I could insert a formula into a cell then paste as value to get the same result, however I am working with a huge document and trying to write the most efficient code possible.

Here is the code I've written, with the understanding that the "v = ..." part of the code isn't correct, but I wanted to show the formula I need to use there. In that formula, the cell "B2" should be dynamic such that it is "B"&i.

Please let me know if you have any solutions or thoughts.

Thanks in advance.

Sub Test()



Dim R As Long, v As String

R = Range("B2").End(xlDown).Row


For i = 2 To R

v = IF(COUNT(SEARCH({"ib",";iber","iiber","iber","ibir"},B2)),"Cat1",IF(ISNUMBER(SEARCH("Unavailable",B2)),"Cat2","Cat3"))


Range("G" & i).Value = v


Next i



End Sub

**tigeravatar** · 03-01-2013, 03:39 PM

bungeejumper2,

Using 1,004,287 rows as a test sample size, this code completed in roughly 15 seconds:

Sub tgr_v1()
    
    Dim lCalc As XlCalculation
    
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    With Intersect(ActiveSheet.UsedRange.EntireRow, Columns("G"))
        .Formula = "=IF(COUNT(SEARCH({""ib"","";iber"",""iiber"",""iber"",""ibir""},B" & .Row & ")),""Cat1"",IF(ISNUMBER(SEARCH(""Unavailable"",B" & .Row & ")),""Cat2"",""Cat3""))"
        .Value = .Value
    End With
    
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

**Norie** · 03-01-2013, 03:48 PM

You need quotes around the string and you need to double up the quotes in the formula.

Oh, and you need an = in front of the formula.

**Hawkeye16** · 03-01-2013, 03:53 PM

Tiger - That .UsedRange function is awesome. Can't believe I had not seen that yet!

**bungeejumper2** · 03-01-2013, 04:03 PM

Thank you Tiger. That works perfect!

Using Text Formulas in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using Text Formulas in VBA

Re: Using Text Formulas in VBA

Re: Using Text Formulas in VBA

Re: Using Text Formulas in VBA

Re: Using Text Formulas in VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions