Paste special values pulled through sumif
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Paste special values pulled through sumif
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
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
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
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.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
What do the cells in between contain?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
There are multiple things - subtotals, some punched in numbers, vlookups, % calculation etc.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
And you want to keep the formulas in those cells?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
Yes, i want to retain all those.. just need to paste special the values in cells where there is sumif formula
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
You could run this macro:
Just for curiosity: why do you want to replace SUMIF with its result while keeping all other formulas?
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
Hey Hans, tried this but this isn't working.
To answer your question - it's just boss's demand !!!
To answer your question - it's just boss's demand !!!
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
Did you select the range containing the cells with the SUMIF formulas (and other formulas) before running the macro?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
Yes, i had selected those cells
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
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))
=SUMIF(...)
or is SUMIF part of a larger formula, for example
=IF(A1="","",SUMIF(D10:D100,A1,E10:E100))
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
=SUMIF(...)
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Paste special values pulled through sumif
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?
Could you attach a small sample workbook without sensitive data that demonstrates the problem?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Paste special values pulled through sumif
My bad.. my sheet had sumifs.. Apologies !!
I have updated the code and it worked perfectly, thanks a lot :)
I have updated the code and it worked perfectly, thanks a lot :)