Copy/Paste Value Only If The Cell Contains A Number

[VinceF]

Greetings Experts,
Appreciate your assistance.
I have 2 column's with formulas...
Column B5:B141 containing this formula

=IFERROR(INDEX(Main!$AK$11:$AK$50,MATCH($D5,Main!$G$11:$G$50,0)),"")

Column F5:F141 containing this formula

=IFERROR(INDEX(Main!$J$11:$J$50,MATCH(D5,Main!$G$11:$G$50,0)),"")

Both of these formulas will generate a number if certain criteria is met, otherwise the cells remain blank.

I like to have a VBA code that would copy and paste values in each cell in the columns but ONLY if a cell contains a number, if the cell does not contain a number the formula would remain.

Thanks for your assistance,
VinceF
Excel 2016

 

[MARK858]

Deleted as doesn't work

 

[MARK858]

Try...

Sub VinceF2()
    Dim myCell As Range
    
    Application.ScreenUpdating = False

    For Each myCell In Range("B5:B141,F5:F141").SpecialCells(xlCellTypeFormulas, 1)
        myCell.Copy
        myCell.PasteSpecial xlValues
    Next
        
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[VinceF]

Try...

Sub VinceF2()
    Dim myCell As Range
   
    Application.ScreenUpdating = False

    For Each myCell In Range("B5:B141,F5:F141").SpecialCells(xlCellTypeFormulas, 1)
        myCell.Copy
        myCell.PasteSpecial xlValues
    Next
       
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Thank you Mark, it works perfectlymuch appreciated.

VinceF

 

[MARK858]

You're welcome

 

[VinceF]

Mark, or??

As indicated the copy/paste value works excellently when I'm on that page however I attempted to add it with another macro that is executed from the "Main" page (see below) and it doesn't work from there. I've attempted to try and tell it which page is being referenced in the code but the only thing that happens is I end up screwing it all up.

The name of the page where it copies and paste values is named "Player Quota History"

Any recommendations..?

VinceF
Excel 2016

Sub AdjustNumber()
    Application.ScreenUpdating = False
    Dim round As Range, points As Range, desWS As Worksheet
    Set desWS = Sheets("PLAYER QUOTA HISTORY")
    With desWS
        Set round = .Range("H4:K4").Find(.Range("E2").Value, LookIn:=xlValues, lookat:=xlWhole)
        For Each points In .Range("B5", .Range("B" & .Rows.Count).End(xlUp))
            If points <> "" Then
                Select Case True
                    Case points.Value <= -9
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 3
                    Case points.Value >= -8 And points.Value <= -6
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 2
                    Case points.Value >= -5 And points.Value <= -3
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 1
                    Case points.Value >= -2 And points.Value <= 2
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value
                    Case points.Value >= 3 And points.Value <= 5
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 1
                    Case points.Value >= 6 And points.Value <= 8
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 2
                    Case points.Value >= 9
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 3
                End Select
            End If
        Next points
    End With
    Application.ScreenUpdating = True


End Sub


    Sub VinceF2()
    Dim myCell As Range
   
    Application.ScreenUpdating = False

    For Each myCell In Range("F5:F141").SpecialCells(xlCellTypeFormulas, 1)
        myCell.Copy
        myCell.PasteSpecial xlValues
    Next
       
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[MARK858]

First of all in future please use code tags when posting code as it makes the code easier to read and to copy.

From what you have posted I can't see the relationship between the 2 codes and so how you make the code I posted specific to one sheet is below (Please note that I took the sheet name from the code you posted rather than your text).

Sub VinceF2()
    Dim myCell As Range
    
    Application.ScreenUpdating = False

    For Each myCell In Sheets("PLAYER QUOTA HISTORY").Range("B5:B141,F5:F141").SpecialCells(xlCellTypeFormulas, 1)
        myCell.Copy
        myCell.PasteSpecial xlValues
    Next
        
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[VinceF]

Thank you Mark....all is working now.
I appreciate you and this forum...

VinceF

 

[MARK858]

You're welcome

 

[smozgur]

Thank you Mark....all is working now.
I appreciate you and this forum...

VinceF

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.