Copy/Paste used range

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
708
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Looking to copy/pasteSV where formulas have a result, not a blank formula. I want to leave the empty formulas active.
The range is AE5:AL12. Only AE5:AE12 should be copy/pasted.
thank you.

NBA.xlsm
AEAFAGAHAIAJAKAL
5ATL       
6CHI       
7MIA       
8PHI       
9GSW       
10SAC       
11LAL       
12NOP       
Playoff Favs
Cell Formulas
RangeFormula
AE5:AE8AE5=$E28
AF5AF5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=1)/(Team=$AE$5),WL),"")
AG5AG5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=2)/(Team=$AE$5),WL),"")
AH5AH5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=3)/(Team=$AE$5),WL),"")
AI5AI5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=4)/(Team=$AE$5),WL),"")
AJ5AJ5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=5)/(Team=$AE$5),WL),"")
AK5AK5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=6)/(Team=$AE$5),WL),"")
AL5AL5=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=7)/(Team=$AE$5),WL),"")
AF6AF6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=1)/(Team=$AE$6),WL),"")
AG6AG6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=2)/(Team=$AE$6),WL),"")
AH6AH6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=3)/(Team=$AE$6),WL),"")
AI6AI6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=4)/(Team=$AE$6),WL),"")
AJ6AJ6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=5)/(Team=$AE$6),WL),"")
AK6AK6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=6)/(Team=$AE$6),WL),"")
AL6AL6=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=7)/(Team=$AE$6),WL),"")
AF7AF7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=1)/(Team=$AE$7),WL),"")
AG7AG7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=2)/(Team=$AE$7),WL),"")
AH7AH7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=3)/(Team=$AE$7),WL),"")
AI7AI7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=4)/(Team=$AE$7),WL),"")
AJ7AJ7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=5)/(Team=$AE$7),WL),"")
AK7AK7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=6)/(Team=$AE$7),WL),"")
AL7AL7=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=7)/(Team=$AE$7),WL),"")
AF8AF8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=1)/(Team=$AE$8),WL),"")
AG8AG8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=2)/(Team=$AE$8),WL),"")
AH8AH8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=3)/(Team=$AE$8),WL),"")
AI8AI8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=4)/(Team=$AE$8),WL),"")
AJ8AJ8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=5)/(Team=$AE$8),WL),"")
AK8AK8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=6)/(Team=$AE$8),WL),"")
AL8AL8=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="East")/(Game=7)/(Team=$AE$8),WL),"")
AE9:AE12AE9=$E36
AF9AF9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=1)/(Team=$AE$9),WL),"")
AG9AG9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=2)/(Team=$AE$9),WL),"")
AH9AH9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=3)/(Team=$AE$9),WL),"")
AI9AI9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=4)/(Team=$AE$9),WL),"")
AJ9AJ9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=5)/(Team=$AE$9),WL),"")
AK9AK9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=6)/(Team=$AE$9),WL),"")
AL9AL9=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=7)/(Team=$AE$9),WL),"")
AF10AF10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=1)/(Team=$AE$10),WL),"")
AG10AG10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=2)/(Team=$AE$10),WL),"")
AH10AH10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=3)/(Team=$AE$10),WL),"")
AI10AI10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=4)/(Team=$AE$10),WL),"")
AJ10AJ10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=5)/(Team=$AE$10),WL),"")
AK10AK10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=6)/(Team=$AE$10),WL),"")
AL10AL10=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=7)/(Team=$AE$10),WL),"")
AF11AF11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=1)/(Team=$AE$11),WL),"")
AG11AG11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=2)/(Team=$AE$11),WL),"")
AH11AH11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=3)/(Team=$AE$11),WL),"")
AI11AI11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=4)/(Team=$AE$11),WL),"")
AJ11AJ11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=5)/(Team=$AE$11),WL),"")
AK11AK11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=6)/(Team=$AE$11),WL),"")
AL11AL11=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=7)/(Team=$AE$11),WL),"")
AF12AF12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=1)/(Team=$AE$12),WL),"")
AG12AG12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=2)/(Team=$AE$12),WL),"")
AH12AH12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=3)/(Team=$AE$12),WL),"")
AI12AI12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=4)/(Team=$AE$12),WL),"")
AJ12AJ12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=5)/(Team=$AE$12),WL),"")
AK12AK12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=6)/(Team=$AE$12),WL),"")
AL12AL12=IFERROR(LOOKUP(2,1/(Series="Pre")/(Div="West")/(Game=7)/(Team=$AE$12),WL),"")
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Your title combined with your description is a little unclear. What result are you looking for?

Are you trying to say that you to convert all non-blank formula results to values?

Note that the formulas in AE that you want to convert just are direct references to cell in column E. If the cell in column E is empty (not blank, but empty) then the formula in AE will return a 0 and therefore not be blank. I think you need to be more specific about what your data looks like.

This VBA will do exactly what you described, but I am not sure that what you described is exactly what you need.
VBA Code: 
Private Sub ConvertToValues()

   Dim Cell As Range
   
   For Each Cell In [AE5:AE12]
      If Cell.Value <> "" Then Cell.Value = Cell.Value
   Next Cell

End Sub
 
Upvote 0
6StringJazzer said:
Your title combined with your description is a little unclear. What result are you looking for?

Are you trying to say that you to convert all non-blank formula results to values?

Note that the formulas in AE that you want to convert just are direct references to cell in column E. If the cell in column E is empty (not blank, but empty) then the formula in AE will return a 0 and therefore not be blank. I think you need to be more specific about what your data looks like.

This VBA will do exactly what you described, but I am not sure that what you described is exactly what you need.
VBA Code: 
Private Sub ConvertToValues()

   Dim Cell As Range
 
   For Each Cell In [AE5:AE12]
      If Cell.Value <> "" Then Cell.Value = Cell.Value
   Next Cell

End Sub
Click to expand...
I want the Range (AE5:AL12) and copy/paste where the cell formula has a result (ie AE5:AE12). And leave the remaining cell formulas active (ie AF5:AL12).
I'm using to following code but its selecting the ROW. I want it to select the individual cells with formula result, not the entire row.
I would like to use the same Range (AE5:AL12) because certain formulas will have values, eventually.
Hope this makes sense.

VBA Code: 
Sub Copy_Playoff_Results()
  
    Dim UsdRws As Long
   
    UsdRws = Range("AE5:AE12").Find("*", , xlValues, , , xlPrevious, , , False).Row
    If UsdRws > 0 Then
   
        Range("AE5:AL" & UsdRws).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    Else
    End If
   
    Range("AE4").Select

End Sub
 
Upvote 0
copy/paste where the cell formula has a result (ie AE5:AE12)
Click to expand...
I'm going to have to be a stickler for terminology here. All formulas have a result (or an error). I asked:
Are you trying to say that you to convert all non-blank formula results to values?
Click to expand...
and I don't see an answer to that in your subsequent post, just a restatement of your first post.

Also your sample above does not show any blank cells. As I said, if a referenced cell in column E is blank, your formula is going to show 0. The best way to state your requirements is to show sample data for all the cases you are interested in, then also sample results.

I would like to use the same Range (AE5:AL12)
Click to expand...
This is part of what is confusing, because you said you just want to change AE5:AE12. If that's all you want to change, why are you referencing AE5:AL12 in so many places?

Bottom line is that I provided code to convert any non-blank cells in AE4:AE12 from formulas to values. Please try that and see if it does what you want. If it does not, please give a concrete description of the result you want.
 
Upvote 0
6StringJazzer said:
I'm going to have to be a stickler for terminology here. All formulas have a result (or an error). I asked:

and I don't see an answer to that in your subsequent post, just a restatement of your first post.

Also your sample above does not show any blank cells. As I said, if a referenced cell in column E is blank, your formula is going to show 0. The best way to state your requirements is to show sample data for all the cases you are interested in, then also sample results.


This is part of what is confusing, because you said you just want to change AE5:AE12. If that's all you want to change, why are you referencing AE5:AL12 in so many places?

Bottom line is that I provided code to convert any non-blank cells in AE4:AE12 from formulas to values. Please try that and see if it does what you want. If it does not, please give a concrete description of the result you want.
Click to expand...
I have this code that works.
This will go thru every cell in Range and convert only cells with viewable values, not just a formula.
I use IFERROR showing "".

VBA Code: 
Sub Test_NBA_Copy_Playoff_Results()

    Dim cell As Range

    For Each cell In Range("AE5:AL12")
            If cell.Value <> "" Then
                cell.Select
                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues
                Else
               
            End If
           
        Next cell
End Sub
 
Upvote 1
Try this

VBA Code: 
Sub Test_NBA_Copy_Playoff_Results_Advanced()

    Dim cell As Range
    Dim formulaResult As Variant

    ' Loop through the cells in the range AE5:AL12
    For Each cell In Range("AE5:AL12")
        On Error Resume Next ' Temporarily ignore errors

        ' Check if the cell contains a formula
        If cell.HasFormula Then
            ' Extract the result of the formula
            formulaResult = cell.Value

            ' Check if the result is not an error, not empty, and has a valid value
            If Not IsError(formulaResult) And formulaResult <> "" Then
                ' If the result is valid, copy only the value (remove the formula and keep the result)
                cell.Value = formulaResult
            End If
        End If

        On Error GoTo 0 ' Turn off error ignoring after processing the cell
    Next cell

End Sub
 
Last edited:
Upvote 0
gtd526 said:
Hello,
Looking to copy/pasteSV where formulas have a result, not a blank formula. I want to leave the empty formulas active.
The range is AE5:AL12. Only AE5:AE12 should be copy/pasted.
thank you.
Click to expand...
Do you mean like this?
Code: 
Sub test()
    [AE5:AL12].Formula = [if(AE5:AL12="",formulatext(AE5:AL12),AE5:AL12)]
End Sub
 
Upvote 0
Solution
sofas said:
Try this

VBA Code: 
Sub Test_NBA_Copy_Playoff_Results_Advanced()

    Dim cell As Range
    Dim formulaResult As Variant

    ' Loop through the cells in the range AE5:AL12
    For Each cell In Range("AE5:AL12")
        On Error Resume Next ' Temporarily ignore errors

        ' Check if the cell contains a formula
        If cell.HasFormula Then
            ' Extract the result of the formula
            formulaResult = cell.Value

            ' Check if the result is not an error, not empty, and has a valid value
            If Not IsError(formulaResult) And formulaResult <> "" Then
                ' If the result is valid, copy only the value (remove the formula and keep the result)
                cell.Value = formulaResult
            End If
        End If

        On Error GoTo 0 ' Turn off error ignoring after processing the cell
    Next cell

End Sub
Click to expand...
Works. Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Solved
Get the Correct Month
2
Replies
10
Views
227
Peter_SSs
Peter_SSs
S
  • Solved
Embedding IF in single cell formula
Replies
3
Views
162
sharshra
S
A
  • Solved
Comparing Text in a Cell to a Range of Text
Replies
4
Views
152
ajwooden32
A
S
  • Solved
Find Value in Table
2
Replies
17
Views
565
Peter_SSs
Peter_SSs
H
  • Solved
Establishing a streak of non-numerical values.
Replies
8
Views
483
HockeyBoi
H

Forum statistics

Threads
1,225,138
Messages
6,183,083
Members
453,146
Latest member
Lacey D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top