#1
|
||||
|
||||
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. |
#2
|
|||
|
|||
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? |
#3
|
||||
|
||||
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. |
#4
|
|||
|
|||
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 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 |
#5
|
||||
|
||||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 Special: Copy and Paste Formatting Only? | tinfanide | Word | 6 | 03-06-2013 12:21 AM |