Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-02-2021, 12:32 AM
Marcia's Avatar
Marcia Marcia is offline Paste value of formula Windows 10 Paste value of formula Office 2019
Expert
Paste value of formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Paste value of formula

Hi. I would like to perform the following repetitive task through macro.
Formula in H1: =CONCATENATE(B2,": ",C2)
I would need a macro that pastes the value of H1 to B2, then delete C2.


When I change the cell references from B2 to B7 and C2 to C7, the macro pastes the result in B7, then delete the value in C7.
I tried recording the steps but the range was stuck at B2 and C2.
Thank you.
Attached Files
File Type: xlsx Copy value of formula.xlsx (9.0 KB, 9 views)
Reply With Quote
  #2  
Old 09-03-2021, 12:46 AM
Purfleet Purfleet is offline Paste value of formula Windows 10 Paste value of formula Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Sorry marcia i am a bit confused with the cell references - you want to copy the formula in H1 to b2? But H1 is already b2?

the attachement is only and .xlsx so doesnt show any code you recorded

can you please clarify for me, with maybe a couple of examples?
Reply With Quote
  #3  
Old 09-03-2021, 03:17 PM
Marcia's Avatar
Marcia Marcia is offline Paste value of formula Windows 10 Paste value of formula Office 2019
Expert
Paste value of formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Purfleet, internet is still down so I can't send the code that I initially got from macro recording.
I need to join cells from col B and C through the concatenate function in H1. After the join, I copy the result back to say, B2 by paste special value. Afterwhich I delete C2. I am hoping that a macro will do the job of pasting the result of H1 to the present cell reference in Col B and delete the cell to its right in column C.
If I change the cell references in H1 to B7 and C7, then run the macro, the value should in H1 should be pasted in B7, and C7 deleted.
Reply With Quote
  #4  
Old 09-03-2021, 08:12 PM
Purfleet Purfleet is offline Paste value of formula Windows 10 Paste value of formula Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Okay, if i understand correctly i think you were trying to do too much - the Macro should be able to do it all

Attached are 2 macros

Join Active

will join the active cell to the cell to the right with a ": " in between and then delete the cell to the right

Code:
Sub JoinActive()

    ActiveCell = ActiveCell & ": " & ActiveCell.Offset(0, 1)

    ActiveCell.Offset(0, 1).Cells.ClearContents

End Sub
JoinAll

Will look at every cell in column b and join it to the cell to the right with a ": " in between - this one takes a few seconds as it is looking at 1million rows.

Code:
Sub JoinAll()

    Dim Col1 As Range 'left column to join
    Dim Col2 As Range 'right column to join
    Dim r1 As Range ' for looping
    
    Set Col1 = ActiveSheet.Range("B:B")
    Set Col2 = ActiveSheet.Range("c:c")
    
    For Each r1 In Col1
        If r1.Value <> "" Then
            r1 = r1 & ": " & r1.Offset(0, 1)
        End If
        
    Next r1

    Col2.Cells.ClearContents

End Sub
Both of these could easily be made more efficent and flexible but hopefully its a start
Attached Files
File Type: xlsm Copy of Copy value of formula-1.xlsm (20.9 KB, 6 views)
Reply With Quote
  #5  
Old 09-04-2021, 12:40 AM
Marcia's Avatar
Marcia Marcia is offline Paste value of formula Windows 10 Paste value of formula Office 2019
Expert
Paste value of formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

The first set of code is perfect Purfleet. I will save the second for future use.
Thank you. You had gone the extra mile in sharing your skills.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste value of formula PASTE FORMULA from lookup table ChrisOK Excel Programming 19 04-22-2020 05:01 PM
cut/copy and paste won't paste DennisWG Word 1 03-13-2020 12:36 PM
Paste value of formula Formula Paste Issue rabend Excel 3 06-02-2014 12:06 PM
Paste to a series of boxes with one paste? BudVitoff Misc 0 11-27-2013 02:51 PM
Paste value of formula Paste Special: Copy and Paste Formatting Only? tinfanide Word 6 03-06-2013 12:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft