MACRO PROBLEM: How to HardCode SUMIF formula ONLY

[cgeorge4]

Hello,

I use a code to hard code vlookup - but is it possible to hard code cells with SUMIF formula's only?


1) I want to hard code any variety of SUMIF or IF formula on the active worksheet.

2) I want the code to only work on the sheet that is activated.

3) There are other basic formulas (sum, subtraction, multiplication, division...etc) that should be left intact.



I've searched many places for a solution. The closest one had the name of the worksheet included in the code - and that will not work for my project. The worksheet name or tab name should not be a factor.

The code below is the code that I mentioned above, but not using. It refers to a workbook name. I pasted it here in case it might be helpful to you.

[COLOR=#333333]Sub ReplaceSumIf()[/COLOR]
For Each sht In ActiveWorkbook.Sheets    For Each cll In sht.UsedRange        If cll.Formula Like "*SUMIF*Workbook2*" Then cll.Formula = cll.Value    NextNext [COLOR=#333333]End Sub[/COLOR]

thank you in advance Darlings,

Juicy

 

[Rick Rothstein]

Does this code do what you want...

Sub ConvertSUMIFtoCellValue()
  Dim Cell As Range
  For Each Cell In Cells.SpecialCells(xlFormulas)
    If InStr(Cell.Formula, "SUMIF(") Then Cell.Value = Cell.Value
  Next
End Sub

 

[cgeorge4]

Hi there, I'm sorry to say it didn't work. I have formulas starting with IF(OR(....... and SUM(IF(......... Could that be the issue since SUMIF is what is written into the code? Thanks

 

[Rick Rothstein]

Hi there, I'm sorry to say it didn't work. I have formulas starting with IF(OR(....... and SUM(IF(......... Could that be the issue since SUMIF is what is written into the code? Thanks

Yes, that is the problem. The reason I used SUMIF and not SUM(IF is because of what you wrote in the title to your thread... there is a SUMIF function which is what I thought you used. Anyway, give this code a try and see if it works for you...

Sub ConvertSUMIFtoCellValue()
  Dim Cell As Range
  For Each Cell In Cells.SpecialCells(xlFormulas)
    If InStr(Cell.Formula, "SUM(IF(") Then Cell.Value = Cell.Value
  Next
End Sub

 

[cgeorge4]

Hi, sorry i'm responding so late. I had to dig for my new password.

Thank you so much. I did add ( and it worked perfectly.