-
Oct 8th, 2008, 07:38 AM
#1
Thread Starter
Member
[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.
-
Oct 8th, 2008, 09:14 AM
#2
Thread Starter
Member
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.
-
Oct 8th, 2008, 01:01 PM
#3
Thread Starter
Member
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.
-
Oct 8th, 2008, 05:12 PM
#4
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
-
Oct 8th, 2008, 06:20 PM
#5
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
-
Oct 9th, 2008, 12:43 AM
#6
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|