AnnieS2Elise
New Member
- Joined
- Mar 17, 2018
- Messages
- 5
Hi all
Can anyone tell me what's wrong with my code?
I'm trying to get a range to start with a set number of rows when I click a button, but not delete the "Ad_Total" row.
It needs to work whether I'm starting with 6 or 20 rows and always end up with 5 rows in a range, which the "Ad_Date" cell above and "Ad_Total" cell below.
Currently if I click my button enough times it deleted the "Ad_Total" row.
Thanks in advance
Annie
Can anyone tell me what's wrong with my code?
I'm trying to get a range to start with a set number of rows when I click a button, but not delete the "Ad_Total" row.
It needs to work whether I'm starting with 6 or 20 rows and always end up with 5 rows in a range, which the "Ad_Date" cell above and "Ad_Total" cell below.
Currently if I click my button enough times it deleted the "Ad_Total" row.
Thanks in advance
Annie
Code:
[TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub New_Button_Click()[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Answer As String[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim MyNote As String[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim iStartRow1 As Integer[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim iEndRow1 As Integer[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]iStartRow1 = Range("Ad_Date").Row + 6[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]iEndRow1 = Range("Ad_Total").Row - 2[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]MyNote = "Changing to a new month will delete all current data. Have you saved a copy of your file?"[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "STOP!")[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If Answer = vbNo Then[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Unload Me[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim List As Variant[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim LR As String[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim r As Long[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]List = Array("Ad_Total")[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]LR = Range(iStartRow1 & ":" & iEndRow2)[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If IsError(Application.Match(Range("Ad_Total").Text, List, False)) Then[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Unload Me[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Rows(iStartRow1 & ":" & iEndRow1).Delete[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Range("Ad_Contents").ClearContents[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][/TD]
[/TR]
</tbody>[/TABLE]