Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Resolved [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.

    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.

  2. #2

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Unhappy Re: [Excel] Search within formula as conditional statement

    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?
    Last edited by Ossewa; Oct 8th, 2008 at 12:32 PM.

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: [Excel] Search within formula as conditional statement

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

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] Search within formula as conditional statement

    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...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  5. #5
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: [Excel] Search within formula as conditional statement

    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
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: [Excel] Search within formula as conditional statement

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

    Regards

    Ossewa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width