Excel VBA assistance for a noobie
 

Hello all, I'm new here and I hope somebody can help me out. I'm tryin
to figure out how to compare and delete data using a macro in excel.
have a workbook with 2 sheets. One sheet contains disconnected rat
info for accounts. The other sheet contains install info for accounts


Here is the problem. This is a commission report. We have an issue wit
our billing system that in order to add services to an account that ha
a campaign associated with it, our agents have to take services off o
the account to "break" the campaign. They complete the work order the
start a new one putting the existing services back on and then addin
the new services. Unfortunately, the query pulls all of that data an
it looks like the agent sold more services then they actually did. Th
result would be the agent getting paid more than they should.

What i'm trying to do is create a macro that will compare th
disconnect and install sheets. If a row in the install sheet matche
the row in the disconnect sheet, delete the row and move onto the nex
row.

Here is a sample of the sheets.

Disconnect:
Acct:------ Rate CD-- Agent----- Quantity from-------- Quantity to
2967801- DIGCNV-- Agent----- 1------------------------0
2967801- DIGCNV-- Agent----- 1------------------------0
2967801- DIGTIER-- Agent----- 1------------------------0
2967801- DIGTIER-- Agent----- 1------------------------0
2967801- EXPD----- Agent----- 1------------------------0
2967801- EXPD----- Agent----- 1------------------------0
2967801- HBOMAX-- Agent----- 1------------------------0
2967801- HBOMAX-- Agent----- 1------------------------0
2967801- PLDIGPK- Agent----- 1------------------------0

Install:
Acct:------ Rate CD-- Agent----- Quantity from-------- Quantity to
2967801- DIGCNV-- Agent----- 0------------------------1
2967801- DIGCNV-- Agent----- 0------------------------1
2967801- EXPD----- Agent----- 0------------------------1
2967801- HBOMAX-- Agent----- 0------------------------1
2967801- PLBGRFH- Agent----- 0------------------------1
2967801- TIERFAM-- Agent----- 0------------------------1
2967801- TIERFAM-- Agent----- 0------------------------1

In the above example we would only pay on the TIERFAM.

I've tried this compare macro to compare the data but and very new t
this so I don't know how to add the syntax to delete the duplicates.

Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC
"0 %") & "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 < cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " < " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!",
vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub

I hope this was a thorough enough explanation of my dilema. I'm going
to keep searching for an answer but if someone can give me any kind of
guidance I would be extremely appreciative!

Thanks in advance

:confused:


---
Message posted from http://www.ExcelForum.com/

Excel VBA assistance for a noobie
 

Shaun: This is not as simple to do as it is to say, but it can be done.
However, I would like a copy of one or more reports to see how it is laid
out. I also work for a cable company and have written many Excel programs
that help us out with AS400 reports. I have an idea that may work for your
problem, but seeing the data in the raw would help immensely.

Mike F

"shaun nieves " wrote in
message ...
Hello all, I'm new here and I hope somebody can help me out. I'm trying
to figure out how to compare and delete data using a macro in excel. I
have a workbook with 2 sheets. One sheet contains disconnected rate
info for accounts. The other sheet contains install info for accounts.


Here is the problem. This is a commission report. We have an issue with
our billing system that in order to add services to an account that has
a campaign associated with it, our agents have to take services off of
the account to "break" the campaign. They complete the work order then
start a new one putting the existing services back on and then adding
the new services. Unfortunately, the query pulls all of that data and
it looks like the agent sold more services then they actually did. The
result would be the agent getting paid more than they should.

What i'm trying to do is create a macro that will compare the
disconnect and install sheets. If a row in the install sheet matches
the row in the disconnect sheet, delete the row and move onto the next
row.

Here is a sample of the sheets.

Disconnect:
Acct:------ Rate CD-- Agent----- Quantity from-------- Quantity to
2967801- DIGCNV-- Agent----- 1------------------------0
2967801- DIGCNV-- Agent----- 1------------------------0
2967801- DIGTIER-- Agent----- 1------------------------0
2967801- DIGTIER-- Agent----- 1------------------------0
2967801- EXPD----- Agent----- 1------------------------0
2967801- EXPD----- Agent----- 1------------------------0
2967801- HBOMAX-- Agent----- 1------------------------0
2967801- HBOMAX-- Agent----- 1------------------------0
2967801- PLDIGPK- Agent----- 1------------------------0

Install:
Acct:------ Rate CD-- Agent----- Quantity from-------- Quantity to
2967801- DIGCNV-- Agent----- 0------------------------1
2967801- DIGCNV-- Agent----- 0------------------------1
2967801- EXPD----- Agent----- 0------------------------1
2967801- HBOMAX-- Agent----- 0------------------------1
2967801- PLBGRFH- Agent----- 0------------------------1
2967801- TIERFAM-- Agent----- 0------------------------1
2967801- TIERFAM-- Agent----- 0------------------------1

In the above example we would only pay on the TIERFAM.

I've tried this compare macro to compare the data but and very new to
this so I don't know how to add the syntax to delete the duplicates.

Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC,
"0 %") & "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 < cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " < " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
LineStyle = xlContinuous
Weight = xlHairline
End With
With .Borders(xlEdgeRight)
LineStyle = xlContinuous
Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
LineStyle = xlContinuous
Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
LineStyle = xlContinuous
Weight = xlHairline
End With
With .Borders(xlInsideVertical)
LineStyle = xlContinuous
Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!",
vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub

I hope this was a thorough enough explanation of my dilema. I'm going
to keep searching for an answer but if someone can give me any kind of
guidance I would be extremely appreciative!

Thanks in advance

:confused:


---
Message posted from http://www.ExcelForum.com/

Excel VBA assistance for a noobie
 

Thanks Mike, I've been pulling my hair out trying to figure this out
I've used a sample compare code like in the previous post. That worke
but I haven't figured out how to include the delete row syntax. I'v
also tried to use a if, and statement in a cell to return a true/fals
statement to see if the row exists in both sheets. The problem wit
that one is that it will only go from row to row and will not searc
the entire sheet before returning a value. As you can see in th
attachment, the first two rows match but after that it is jumbled s
that is why it's not working. I've also tried placing all of th
records on one sheet and using a remove duplicates sub. Am I on th
right track or am I lost in the sauce? I've attached a sample workboo
with information on one account. Thanks again for all your help

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55569
--
Message posted from http://www.ExcelForum.com

Excel VBA assistance for a noobie
 

cells(i,j).entirerow.delete will delete the row.

Keep in mind when you delete rows, you MUST loop from the bottom up as
the row numbers change up each time you delete a row. Something of the
form:

For i = lastrow To firstrow Step -1
If cells(i,j)= your criteria then
cells(i,j).entirerow.delete
Next i

shaun nieves < wrote:

Thanks Mike, I've been pulling my hair out trying to figure this out.
I've used a sample compare code like in the previous post. That worked
but I haven't figured out how to include the delete row syntax. I've
also tried to use a if, and statement in a cell to return a true/false
statement to see if the row exists in both sheets. The problem with
that one is that it will only go from row to row and will not search
the entire sheet before returning a value. As you can see in the
attachment, the first two rows match but after that it is jumbled so
that is why it's not working. I've also tried placing all of the
records on one sheet and using a remove duplicates sub. Am I on the
right track or am I lost in the sauce? I've attached a sample workbook
with information on one account. Thanks again for all your help.

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=555696
---
Message posted from http://www.ExcelForum.com/

Excel VBA assistance for a noobie
 

Should I nest this in the middle or end of the macro I am currentl
using or should I start from scratch with a new compare macro t
include the delete.row syntax?? Thanks

--
Message posted from http://www.ExcelForum.com

Excel VBA assistance for a noobie
 

Shaun: I have something going that looks like it may work. Are you concerned
with what the customer may drop in order to buy a new package? Or only what
was added. And do you have an email address that I can check with you on
things like this.
Mike F
"shaun nieves " wrote in
message ...
Thanks Mike, I've been pulling my hair out trying to figure this out.
I've used a sample compare code like in the previous post. That worked
but I haven't figured out how to include the delete row syntax. I've
also tried to use a if, and statement in a cell to return a true/false
statement to see if the row exists in both sheets. The problem with
that one is that it will only go from row to row and will not search
the entire sheet before returning a value. As you can see in the
attachment, the first two rows match but after that it is jumbled so
that is why it's not working. I've also tried placing all of the
records on one sheet and using a remove duplicates sub. Am I on the
right track or am I lost in the sauce? I've attached a sample workbook
with information on one account. Thanks again for all your help.

Attachment filename: example.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=555696
---
Message posted from http://www.ExcelForum.com/

Excel VBA assistance for a noobie
 

Thanks Mike, you can email me at

I've uploaded a sample of the workbook with the two sheets.

www.planetshaun.net/shaun.xls

what I'm concerned with is what was added. We are paying too muc
commission to our agents through no fault of their own. In order t
break campaigns, you have to remove services from the account. As yo
will see on the sheets, one shows services that went from quantity X t
0 and one sheet shows quantity 0 to X. We only want to pay what ne
services were added. I hope that's clear....

Thanks again for your help

--
Message posted from http://www.ExcelForum.com

Excel VBA assistance for a noobie
 

Shaun, check your email for the solution to your project named
'Attachment.xls'. If you cannot receive attachments, let me know.
"shaun nieves " wrote in
message ...
Thanks Mike, you can email me at

I've uploaded a sample of the workbook with the two sheets.

www.planetshaun.net/shaun.xls

what I'm concerned with is what was added. We are paying too much
commission to our agents through no fault of their own. In order to
break campaigns, you have to remove services from the account. As you
will see on the sheets, one shows services that went from quantity X to
0 and one sheet shows quantity 0 to X. We only want to pay what new
services were added. I hope that's clear....

Thanks again for your help!


---
Message posted from http://www.ExcelForum.com/