Macro to Copy and Paste 1 Row Down

[Alex O]

Can someone help me with a macro that will check H and I and if there's a value present, insert 1 row down, copy and paste all of the data from the current row and change the value in E from a 2 to a 3?

Many Thanks,


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 95px"><COL style="WIDTH: 118px"><COL style="WIDTH: 68px"><COL style="WIDTH: 79px"><COL style="WIDTH: 216px"><COL style="WIDTH: 96px"><COL style="WIDTH: 89px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 67px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Account</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Original Assignment Batch (mo/yr)</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Report Date </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Code *as of report date </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Description </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Amount </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Commission </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff; FONT-WEIGHT: bold">Legal Fees </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">690152</TD><TD style="TEXT-ALIGN: center">7/2011</TD><TD style="TEXT-ALIGN: center">9/15/2011</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Assigned to agency by AGI </TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">- </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ccffff">691674</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">7/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">9/15/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">2</TD><TD style="BACKGROUND-COLOR: #ccffff">Collected from debtor </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffff">150.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffff">(54.00)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffff">- </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">702057</TD><TD style="TEXT-ALIGN: center">7/2011</TD><TD style="TEXT-ALIGN: center">9/15/2011</TD><TD style="TEXT-ALIGN: center">2</TD><TD>Collected from debtor </TD><TD style="TEXT-ALIGN: right">500.00 </TD><TD style="TEXT-ALIGN: right">(90.00)</TD><TD style="TEXT-ALIGN: right">- </TD></TR></TBODY></TABLE>

 

[texasalynn]

ok so from you example if H & I have a value, does that mean even if the value is 0? Also changing the 2 to a 3, is that always the case, or would it be better if the value was changed to 1+ what the value is?

 

[Alex O]

No. If the value is 0 it can be ignored. I'm basically updating status codes, so if there's a value in H the status in E should be 3. If there's a value in I the value should be 4 (I realize I left this absent from the original post - I was going to edit the macro, assuming I could get help with one, myself). There will never be a value in both H and I.

Thanks

 

[texasalynn]

try this

Sub InsertRow_Commission()
Dim SR As Long
SR = ActiveCell.Row
If Cells(SR, "I") <> "" And Cells(SR, "H") <> "" Then
    Rows(SR + 1).EntireRow.Insert
    Rows(SR).Copy Destination:=Rows(SR + 1)
    If Cells(SR, "H") <> 0 Then
        Cells(SR + 1, "E") = 3
    End If
    If Cells(SR, "I") <> 0 And Cells(SR, "I") <> "-" Then
        Cells(SR + 1, "E") = 4
    End If
End If
End Sub

 

[Alex O]

I’m not sure why, but the code doesn’t seem to be doing anything. Would it help if I sent you the actual file?

Thanks

 

[texasalynn]

so the macro works with you having your cursor on the row that you are wanting to insert the row for

 

[Alex O]

OK - I see how it's functioning. I was expecting it to loop through the range (I'm relatively new to the world of macros!). Is it possible to have it loop through rather than going row by row? If not, it's fine...I appreciate your help!

Thanks

 

[texasalynn]

ok so this will loop

Sub InsertRow_Commission()
Dim LR As Long
Application.DisplayAlerts = False
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Cells(LR, "I") <> "" And Cells(LR, "H") <> "" Then
    Rows(LR + 1).EntireRow.Insert
    Rows(LR).Copy Destination:=Rows(LR + 1)
    If Cells(LR, "H") <> 0 Then
        Cells(LR + 1, "E") = 3
    End If
    If Cells(LR, "I") <> 0 And Cells(LR, "I") <> "-" Then
        Cells(LR + 1, "E") = 4
    End If
End If
Next i
MsgBox "Done"
Application.DisplayAlerts = True
End Sub

note: untested

be sure to make a copy of you worksheet before trying

 

[Alex O]

Perfect...thanks for all your help!