Copy/Paste Value When Formula in Cell Results in Text

[VinceF]

Greeting Experts,
This code works perfectly when the result of the formula in the cell results in a number. However after a modification I have 1 cell where the formula results in text and I'd like to somehow modify this code so that it also copies/paste values for both text and numbers...as it is now it doesn't copy/past value to the cell with the text in it.
Hope this makes sense.

Thank You,
VinceF
Win10
Office 2019

Application.ScreenUpdating = False

    For Each myCell In Sheets("Win").Range("C6:CO194").SpecialCells(xlCellTypeFormulas, 1)
        myCell.Copy
        myCell.PasteSpecial xlValues

    Next
        
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

 

[Micron]

Try dropping the , 1) . . . SpecialCells(xlCellTypeFormulas)
AFAIK that would copy anything, including formats. If you don't want that, then combine the constants or their numeric values.

XlCellType enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

 

[VinceF]

Micron,
Thanks for your reply. However when I attempted your suggestion it sent it out to lunch...(spinning blue circle).
Below is the actual code showing all the commands, not sure if this has anything to do with it or not.


Sub VIN()

Dim myCell As Range

Application.ScreenUpdating = False

For Each myCell In Sheets("VinE Cup").Range("G47:CF82").SpecialCells(xlCellTypeFormulas, 1)
myCell.Copy
myCell.PasteSpecial xlValues
Next

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

Application.ScreenUpdating = False

For Each myCell In Sheets("Old Player Quota History").Range("F5:F300").SpecialCells(xlCellTypeFormulas, 1)
myCell.Copy
myCell.PasteSpecial xlValues
Next

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

Application.ScreenUpdating = False

For Each myCell In Sheets("New Player Quota History").Range("F5:F300").SpecialCells(xlCellTypeFormulas, 1)
myCell.Copy
myCell.PasteSpecial xlValues
Next

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With



Application.ScreenUpdating = False

For Each myCell In Sheets("Scoring History").Range("F6:CQ190").SpecialCells(xlCellTypeFormulas, 1)
myCell.Copy
myCell.PasteSpecial xlValues
Next

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With


Application.ScreenUpdating = False

For Each myCell In Sheets("Win").Range("C6:CN194").SpecialCells(xlCellTypeFormulas, 1)
myCell.Copy
myCell.PasteSpecial xlValues

Next

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With


Application.ScreenUpdating = True
Sheets("MAIN").Select


End Sub

 

[VinceF]

As a follow up...if I let it go long enough it actually works...I'll mark this as solved and thank you for solving the issue... Any suggestion as how to get rid of the "spinning circle".

Thaks again,
VinceF

 

[Micron]

You're looping over about 3,000 cells when you ought to be able to copy and paste the entire range all at once. However, I'm not certain it would copy only cells with formulas in one swoop but that's what I'd try. Untested:
Sheets("Scoring History").Range("F6:CQ190").SpecialCells(xlCellTypeFormulas, 1).Copy then repeat with paste, or

Dim rng As Range
Set rng = Sheets("Scoring History").Range("F6:CQ190").SpecialCells(xlCellTypeFormulas, 1)
rng.copy
rng.pastespecial xlvalues

I'm no expert in Excel vba for sure - just learning as I go and augmenting my Access vba knowledge.
PS - would have thought that with 200+ plus posts someone would have already requested that you post code, properly indented, between code tags as I just did. That's the vba button on the posting toolbar. I usually won't read most, if any, code posted like that in post 3.

 

[VinceF]

Thank you for your reply, much appreciated.

Sorry about not using the proper procedure when posting code, I'll make sure that I do so in the future.

VinceF

 

[Micron]

Can you say whether or not the suggested code approach in post 5 did what you need it to do?

 

[VinceF]

Micron... Ultimately I ended up modifying the data so that I no longer needed it to perform as originally sought. Thanks again for your assistance.

VinceF