[RESOLVED] [Excel] Search within formula as conditional statement

Guys,

More help please...

The function of this sub routine is to run through all the sheets in the workbook and copy and paste special the formulas to values ->except formulas that contain the "subtotal" or "sum" function.:ehh:

The paste special prt works, but I am unable to make it conditional so that it skips over the subtotal and sum finctions. I have been searching the forum for hours trying to find a similar thread and tried all different codes of my own, but I am stumped.

I would like help with the following:

• Insert "if" statement to skip formulas containing "subtototal" and "sum" functions
• Simplify the code and prevent screen from "flashing" as it executes
• Show a progress bar with a message to be patient as it takes some time to complete?

Here is my code so far..

Code:

---

Sub FormulasToValues()


Dim dataSheet As Worksheet
Dim lstRow As Integer
Dim Row As Integer

frmExport.Show
For Each dataSheet In ActiveWorkbook.Sheets
    lstRow = dataSheet.UsedRange.Row + dataSheet.UsedRange.Rows.Count - 1
        For Row = lstRow To 1 Step -1
            'selects the entire row and then search for rows containing subtotals
            With dataSheet.Rows(Row).EntireRow
           
' here is where I need a if statement that is something to the extend of:
' "if any cell in the row contains a formula with the subtotal function
' in it, then skip the following steps else continue"
               
                .Copy
                'Paste the copied cells as values instead of formulas
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            End With
frmExport.Hide
        Next Row
Next dataSheet

End Sub

---

Help will be appreciated.

Sorry to be a bug. Can anyone tell me if it is at all possible to perform the function I am trying to get the code to do?

Still struggling with same problem. Anybody still online that can help me? It is dark here in Africa.

Quote:

---

I would like help with the following:
Insert "if" statement to skip formulas containing "subtototal" and "sum" functions
Simplify the code and prevent screen from "flashing" as it executes
Show a progress bar with a message to be patient as it takes some time to complete?

---

1) This is an example on how you need to check for a string in a formula

Code:

---

Sub Test()
Dim SearchString, SearchChar, MyPos
'Lets check if the formula in Cell C5 has
'the word SUM in it
SearchString = Range("C5").Formula
SearchChar = "SUM" '<=Case sensitive

MyPos = InStr(1, SearchString, SearchChar)

If MyPos = 0 Then
    MsgBox "not found"
Else
    MsgBox "found"
End If
End Sub

---

2) To prevent screen from flashing use this

Code:

---

Application.ScreenUpdating = False
'Your Code
Application.ScreenUpdating = True

---

3) This has been covered many times in the forum. Do a search. Probably you will find one example by me. If you still have any problem do ask and I will be glad to help you out...

This will help:

Code:

---

Option Explicit

Sub FormulasToValues()
  Dim ws As Worksheet
  Dim aCell As Range
 
  Application.ScreenUpdating = False
  For Each ws In ActiveWorkbook.Sheets
      For Each aCell In ws.UsedRange.Cells
        If Left$(aCell.Formula, 1) = "=" Then '-- cell contains formula
            If Left$(aCell.Formula, 5) = "=SUM(" Then
            ElseIf Left$(aCell.Formula, 10) = "=SUBTOTAL(" Then
            Else
              aCell.Copy
              aCell.PasteSpecial Paste:=xlPasteValues
            End If
            '-- Select Case block below can be used instead of If block above
            'Select Case Left$(aCell.Formula, 5)
            '  Case "=SUM(", "=SUBT"
            '  Case Else
            '      aCell.Copy
            '      aCell.PasteSpecial Paste:=xlPasteValues
            'End Select
        End If
      Next
  Next
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

---

Thanks, it works very good. I appreciate the help.

Regards

Ossewa