L
Legacy 436997
Guest
I have a macro that Im 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">
Thats it.</code>
Thanks in advance for your help.
<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>
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">
Thats 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></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>