Paste special values pulled through sumif

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Paste special values pulled through sumif

Post by shreeram.maroo »

Hi,

Is there any way i that i can paste special the values which are currently pulled through a sumif value. I need to apply this only on selected range of cells.
break links would not be helpful as there are no external links in my file.


Regards
Shreeram

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

Select the range with the formulas.
Copy it (Ctrl+C).
Click the lower half of the Paste button on the Home tab of the ribbon and select Values or press V.

S0799.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

There are around 1000 rows, of which sumif would be only in 600 and these 600 are not in continuous range, i have to select them one by one and then do this. Hence was checking if there is any quick way.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

What do the cells in between contain?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

There are multiple things - subtotals, some punched in numbers, vlookups, % calculation etc.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

And you want to keep the formulas in those cells?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

Yes, i want to retain all those.. just need to paste special the values in cells where there is sumif formula

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

You could run this macro:

Code: Select all

Sub ReplaceSUMIF()
    Dim rng As Range
    With Selection ' or Cells or Range("A1:K50")
        Set rng = .Find(What:="=SUMIF(", LookIn:=xlFormulas, LookAt:=xlPart)
        Do While Not rng Is Nothing
            rng.Value = rng.Value
            Set rng = .FindNext(After:=rng)
        Loop
    End With
End Sub
Just for curiosity: why do you want to replace SUMIF with its result while keeping all other formulas?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

Hey Hans, tried this but this isn't working.

To answer your question - it's just boss's demand !!! :sad:

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

Did you select the range containing the cells with the SUMIF formulas (and other formulas) before running the macro?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

Yes, i had selected those cells

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

Do the cells from which you want to remove formulas contain formulas of the type

=SUMIF(...)

or is SUMIF part of a larger formula, for example

=IF(A1="","",SUMIF(D10:D100,A1,E10:E100))
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

=SUMIF(...)

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Paste special values pulled through sumif

Post by HansV »

I tested the code om a sheet with such formulas and it worked. I can't explain why it doesn't work for you.
Could you attach a small sample workbook without sensitive data that demonstrates the problem?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Paste special values pulled through sumif

Post by shreeram.maroo »

My bad.. my sheet had sumifs.. Apologies !!

I have updated the code and it worked perfectly, thanks a lot :)