Small change to my macro

L

Legacy 436997

Guest
I have a macro that I’m looking to make a small change to what cells get deleted after a copy and paste takes place. If someone could kindly assist.

What I need to happen is as follows:

Once a found number is entered in cell A1 (8 in this case) and is found in the cell range A20:A34 (cell B34) it gets copied and pasted to cell range J1:J12. It also gets copied and pasted to cell C17 and cell B17.
In only this cell range do I need the contents of cell B34 to be deleted after posted.

When the found number is found in cell ranges D20:D34,H20:H34 the copy and paste function would be the same as above except
I need cells <code class="western">E20/F20/G20 AND I/J/K to be deleted after the copy and paste function.</code>
<code class="western">
That’s it.</code>

Thanks in advance for your help.



Code: 
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Sub do_it()[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Dim sht As Worksheet, n As String, cell, num, tmp, rngDest As Range, i As Integer[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Set sht = ActiveSheet[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]n = sht.Range("A1").Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]i = 0[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]For Each cell In sht.Range("A20:A34,D20:D34,H20:H34").Cells[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]tmp = cell.Offset(0, 1).Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]If cell.Value = n And tmp Like "*#-#*" Then[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]'get the first number[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]num = CLng(Trim(Split(tmp, "-")(0)))[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]'find the next empty cell in the appropriate row[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]'make sure not to add before col J[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]If rngDest.Column < 12 Then Set rngDest = sht.Cells(num, 12)[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]cell.Offset(0, 1).Copy rngDest[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]' This is getting the next number in A/D/H----[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Set tmp = cell.Offset(1, 0)[/SIZE][/FONT]</code>

<code class="western">[FONT=Liberation Serif, serif][SIZE=3]' This is filling up B17 - F18 in order until filled[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]If sht.Range("B17").Value = "" Then[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("C17").Value = cell.Offset(0, 1).Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("B17").Value = tmp.Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]ElseIf sht.Range("C18").Value = "" Then[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("C18").Value = cell.Offset(0, 1).Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("B18").Value = tmp.Value[/SIZE][/FONT]</code>
<code class="western">ElseIf sht.Range("E17").Value = "" Then</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("E17").Value = cell.Offset(0, 1).Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("D17").Value = tmp.Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]ElseIf sht.Range("E18").Value = "" Then[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("E18").Value = cell.Offset(0, 1).Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]sht.Range("D18").Value = tmp.Value[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]End If[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]'---- This clears the B columns after using the value ----[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Dim rg As Range, rg1 As Range[/SIZE][/FONT]</code>
<code class="western"> </code><code class="western">[FONT=Liberation Serif, serif][SIZE=3]If cell.Column = 1 Then Set rg = cell.Offset(, 1).Resize(, 1)[/SIZE][/FONT]</code>
<code class="western"> </code><code class="western">[FONT=Liberation Serif, serif][SIZE=3]If cell.Column > 1 Then Set rg1 = cell.Offset(, 1).Resize(, 2)[/SIZE][/FONT]</code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]End If[/SIZE][/FONT]</code>
<code class="western"> </code>
<code class="western">[FONT=Liberation Serif, serif][SIZE=3]Next cell[/SIZE][/FONT]</code> <code class="western">[FONT=Liberation Serif, serif][SIZE=3]If Not rg Is Nothing Then rg.ClearContents 'will be delete column b,c '[/SIZE][/FONT]</code>

 <code class="western">        </code><code class="western">[FONT=Liberation Serif, serif][SIZE=3]If Not rg1 Is Nothing Then rg1.ClearContents 'will be delete column e,f,g,i,j 
 [/SIZE][/FONT]</code> <code class="western">[FONT=Liberation Serif, serif][SIZE=3]
End Sub[/SIZE][/FONT]</code>
<style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }code.western { font-family: "Liberation Mono", serif; }code.cjk { font-family: "WenQuanYi Micro Hei Mono"; }code.ctl { font-family: "Liberation Mono"; }</style>
<style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }code.western { font-family: "Liberation Mono", serif; }code.cjk { font-family: "WenQuanYi Micro Hei Mono"; }code.ctl { font-family: "Liberation Mono"; }</style></pre> <style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }code.western { font-family: "Liberation Mono", serif; }code.cjk { font-family: "WenQuanYi Micro Hei Mono"; }code.ctl { font-family: "Liberation Mono"; }</style>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with Clear Contents
Replies
3
Views
714
Rick Rothstein
Rick Rothstein
M
  • Question Question
Compile error
Replies
4
Views
730
davesexcel
davesexcel
N
  • Question Question
Macro to Move Data
2
Replies
12
Views
2K
DanteAmor
DanteAmor
L
  • Question Question
A working macro that I need to change the search range and write location
Replies
1
Views
534
Legacy 436997
L
S
  • Question Question
Change to code offsets
Replies
8
Views
866
JLGWhiz
JLGWhiz

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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