Heres what i want the macro to do.

I have a spreadsheet with 2 tabs. one named commisions and the other named
summary.

In the commisions tab there are 4 columns (Lets call them 1,2,3, and 4)
Columns 1 and 2 are non number values and 3 and 4 are columns with number
values.

At every change in Column 2 i want it to put the value next to it in column
1 and the sum of the values in columns 3 and 4 that correlate with the value
in Column2.

And i want it to display the information in the Summary Tab.

If anyone can help that would be amazing.

Thanks!
Alex

can't you just have the cells linked on the summary sheet from the
commissions sheet??

a1 on commissions = Sally
a1 on summary = "=commissions!a1"

would automatically change when the commissions sheet changes.

then, c1 = 15, d1 = 10 on commissions
b1 on summary = "=commissions!c1+commissions!d1"

again, would automatically update when things on the commissions sheet
change.

just a non-macro idea.
susan


On Jun 23, 2:29*pm, Ewing25 wrote:
Heres what i want the macro to do.

I have a spreadsheet with 2 tabs. one named commisions and the other named
summary.

In the commisions tab there are 4 columns (Lets call them 1,2,3, and 4)
Columns 1 and 2 are non number values and 3 and 4 are columns with number
values.

At every change in Column 2 i want it to put the value next to it in column
1 and the sum of the values in columns 3 and 4 that correlate with the value
in Column2.

And i want it to display the information in the Summary Tab.

If anyone can help that would be amazing.

Thanks!
Alex

What do you mean by non-number values (letters)? Can you give me two lines of
what the spreadsheet would look like?

Commissions Tab:
Col1 Col2 Col3 Col4

Mike B.

"Ewing25" wrote:

Heres what i want the macro to do.

I have a spreadsheet with 2 tabs. one named commisions and the other named
summary.

In the commisions tab there are 4 columns (Lets call them 1,2,3, and 4)
Columns 1 and 2 are non number values and 3 and 4 are columns with number
values.

At every change in Column 2 i want it to put the value next to it in column
1 and the sum of the values in columns 3 and 4 that correlate with the value
in Column2.

And i want it to display the information in the Summary Tab.

If anyone can help that would be amazing.

Thanks!
Alex

The first two columns have Letters such as AHB or soemthing similar. The next
two columns have numbers.

Sorry im not very good at explaining this.

Im basically trying to make a Summary of data that looks like this.

Col 1 Col 2 Col 3 Col4
AHB BNY 240 200
AHB BNY 455 150
AHB NOT 500 200
AHB NOT 300 275


Into something that looks like this.

AHB
BNY 695 350
NOT 800 475


"Mike B." wrote:

What do you mean by non-number values (letters)? Can you give me two lines of
what the spreadsheet would look like?

Commissions Tab:
Col1 Col2 Col3 Col4

Mike B.

"Ewing25" wrote:

Heres what i want the macro to do.

I have a spreadsheet with 2 tabs. one named commisions and the other named
summary.

In the commisions tab there are 4 columns (Lets call them 1,2,3, and 4)
Columns 1 and 2 are non number values and 3 and 4 are columns with number
values.

At every change in Column 2 i want it to put the value next to it in column
1 and the sum of the values in columns 3 and 4 that correlate with the value
in Column2.

And i want it to display the information in the Summary Tab.

If anyone can help that would be amazing.

Thanks!
Alex

This should work I think.


Option Explicit

Sub SummarizeComm()

Dim wb As Workbook
Dim wsComm As Worksheet
Dim wsSum As Worksheet
Dim rColTwo As Range
Dim rCell As Range
Dim strColOne As String
Dim strColTwo As String
Dim lngColThree As Long
Dim lngColFour As Long
Dim intSumRow As Integer

Set wb = ActiveWorkbook
Set wsComm = wb.Worksheets(1)
Set wsSum = wb.Worksheets(2)
Set rColTwo = wsComm.Range(Cells(2, 2), Cells(2, 2).End(xlDown))


strColOne = Cells(2, 2).Offset(0, -1).Value
strColTwo = Cells(2, 2).Value

wsSum.Range("B1").Value = strColOne
intSumRow = 2

For Each rCell In rColTwo

If rCell.Value = strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
lngColThree = lngColThree + rCell.Offset(0, 1).Value
lngColFour = lngColFour + rCell.Offset(0, 2).Value

ElseIf rCell.Value < strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour
lngColThree = 0
lngColFour = 0
strColTwo = rCell.Value
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1

Else
intSumRow = intSumRow + 2
wsSum.Cells(intSumRow, 2) = rCell.Offset(0, -1).Value
strColOne = rCell.Offset(0, -1).Value
strColTwo = rCell.Value
lngColThree = 0
lngColFour = 0
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1
End If

Next rCell

wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour

End Sub

On Jun 24, 10:00*am, Aviashn wrote:
This should work I think.

Option Explicit

Sub SummarizeComm()

Dim wb As Workbook
Dim wsComm As Worksheet
Dim wsSum As Worksheet
Dim rColTwo As Range
Dim rCell As Range
Dim strColOne As String
Dim strColTwo As String
Dim lngColThree As Long
Dim lngColFour As Long
Dim intSumRow As Integer

Set wb = ActiveWorkbook
Set wsComm = wb.Worksheets(1)
Set wsSum = wb.Worksheets(2)
Set rColTwo = wsComm.Range(Cells(2, 2), Cells(2, 2).End(xlDown))

strColOne = Cells(2, 2).Offset(0, -1).Value
strColTwo = Cells(2, 2).Value

wsSum.Range("B1").Value = strColOne
intSumRow = 2

For Each rCell In rColTwo

* *If rCell.Value = strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
* * * lngColThree = lngColThree + rCell.Offset(0, 1).Value
* * * lngColFour = lngColFour + rCell.Offset(0, 2).Value

* *ElseIf rCell.Value < strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
* * * wsSum.Cells(intSumRow, 1).Value = strColTwo
* * * wsSum.Cells(intSumRow, 3).Value = lngColThree
* * * wsSum.Cells(intSumRow, 4).Value = lngColFour
* * * lngColThree = 0
* * * lngColFour = 0
* * * strColTwo = rCell.Value
* * * lngColThree = rCell.Offset(0, 1).Value
* * * lngColFour = rCell.Offset(0, 2).Value
* * * intSumRow = intSumRow + 1

* *Else
* * * intSumRow = intSumRow + 2
* * * wsSum.Cells(intSumRow, 2) = rCell.Offset(0, -1).Value
* * * strColOne = rCell.Offset(0, -1).Value
* * * strColTwo = rCell.Value
* * * lngColThree = 0
* * * lngColFour = 0
* * * lngColThree = rCell.Offset(0, 1).Value
* * * lngColFour = rCell.Offset(0, 2).Value
* * * intSumRow = intSumRow + 1
* *End If

Next rCell

wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour

End Sub

By the way, you'll need to change some of the Cells() references to
suit the layout of your spreadsheet.

I keep getting a range failed error. Is this because i need to change the
cell values? Im not sure what i should change them too.

"Aviashn" wrote:

On Jun 24, 10:00 am, Aviashn wrote:
This should work I think.

Option Explicit

Sub SummarizeComm()

Dim wb As Workbook
Dim wsComm As Worksheet
Dim wsSum As Worksheet
Dim rColTwo As Range
Dim rCell As Range
Dim strColOne As String
Dim strColTwo As String
Dim lngColThree As Long
Dim lngColFour As Long
Dim intSumRow As Integer

Set wb = ActiveWorkbook
Set wsComm = wb.Worksheets(1)
Set wsSum = wb.Worksheets(2)
Set rColTwo = wsComm.Range(Cells(2, 2), Cells(2, 2).End(xlDown))

strColOne = Cells(2, 2).Offset(0, -1).Value
strColTwo = Cells(2, 2).Value

wsSum.Range("B1").Value = strColOne
intSumRow = 2

For Each rCell In rColTwo

If rCell.Value = strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
lngColThree = lngColThree + rCell.Offset(0, 1).Value
lngColFour = lngColFour + rCell.Offset(0, 2).Value

ElseIf rCell.Value < strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour
lngColThree = 0
lngColFour = 0
strColTwo = rCell.Value
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1

Else
intSumRow = intSumRow + 2
wsSum.Cells(intSumRow, 2) = rCell.Offset(0, -1).Value
strColOne = rCell.Offset(0, -1).Value
strColTwo = rCell.Value
lngColThree = 0
lngColFour = 0
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1
End If

Next rCell

wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour

End Sub

By the way, you'll need to change some of the Cells() references to
suit the layout of your spreadsheet.

Possibly, I'll repost the code with some comments inserted. If after
making the changes below the code still does not run, post back with
the line that isn't working and if possible something that will give
an idea of where your data begins on the Commissions sheet.

Sub SummarizeComm()


Dim wb As Workbook
Dim wsComm As Worksheet
Dim wsSum As Worksheet
Dim rColTwo As Range
Dim rCell As Range
Dim strColOne As String
Dim strColTwo As String
Dim lngColThree As Long
Dim lngColFour As Long
Dim intSumRow As Integer


Set wb = ActiveWorkbook
Set wsComm = wb.Worksheets(1)
Set wsSum = wb.Worksheets(2)

'Change the following (2,2) reference to the location of the first
data cell in what you referred to as 'Col 2'.
' the (2,2) is (row#,column#). Also, I should note that the following
line of code depends on there being no gaps or empty cells
' in the 'Col 2' data
Set rColTwo = wsComm.Range(Cells(2, 2), Cells(2, 2).End(xlDown))

'Change the following (2,2) reference to the location of the first
data cell in what you referred to as 'Col 2'.
' the (2,2) is (row#,column#)
strColOne = Cells(2, 2).Offset(0, -1).Value
strColTwo = Cells(2, 2).Value


wsSum.Range("B1").Value = strColOne
intSumRow = 2


For Each rCell In rColTwo


If rCell.Value = strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
lngColThree = lngColThree + rCell.Offset(0, 1).Value
lngColFour = lngColFour + rCell.Offset(0, 2).Value


ElseIf rCell.Value < strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour
lngColThree = 0
lngColFour = 0
strColTwo = rCell.Value
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1


Else
intSumRow = intSumRow + 2
wsSum.Cells(intSumRow, 2) = rCell.Offset(0, -1).Value
strColOne = rCell.Offset(0, -1).Value
strColTwo = rCell.Value
lngColThree = 0
lngColFour = 0
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1
End If


Next rCell


wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour


End Sub

Ok its giving me the same error as i described before on the line

Set rColTwo = wsComm.Range(Cells(2, 2), Cells(2, 2).End(xlDown))

I tried naming the range as Custodian and putting that in place of the cells
but it still gave me the same error about failed range.



"Aviashn" wrote:

Possibly, I'll repost the code with some comments inserted. If after
making the changes below the code still does not run, post back with
the line that isn't working and if possible something that will give
an idea of where your data begins on the Commissions sheet.

Sub SummarizeComm()


Dim wb As Workbook
Dim wsComm As Worksheet
Dim wsSum As Worksheet
Dim rColTwo As Range
Dim rCell As Range
Dim strColOne As String
Dim strColTwo As String
Dim lngColThree As Long
Dim lngColFour As Long
Dim intSumRow As Integer


Set wb = ActiveWorkbook
Set wsComm = wb.Worksheets(1)
Set wsSum = wb.Worksheets(2)

'Change the following (2,2) reference to the location of the first
data cell in what you referred to as 'Col 2'.
' the (2,2) is (row#,column#). Also, I should note that the following
line of code depends on there being no gaps or empty cells
' in the 'Col 2' data
Set rColTwo = wsComm.Range(Cells(2, 2), Cells(2, 2).End(xlDown))

'Change the following (2,2) reference to the location of the first
data cell in what you referred to as 'Col 2'.
' the (2,2) is (row#,column#)
strColOne = Cells(2, 2).Offset(0, -1).Value
strColTwo = Cells(2, 2).Value


wsSum.Range("B1").Value = strColOne
intSumRow = 2


For Each rCell In rColTwo


If rCell.Value = strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
lngColThree = lngColThree + rCell.Offset(0, 1).Value
lngColFour = lngColFour + rCell.Offset(0, 2).Value


ElseIf rCell.Value < strColTwo And rCell.Offset(0, -1).Value =
strColOne Then
wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour
lngColThree = 0
lngColFour = 0
strColTwo = rCell.Value
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1


Else
intSumRow = intSumRow + 2
wsSum.Cells(intSumRow, 2) = rCell.Offset(0, -1).Value
strColOne = rCell.Offset(0, -1).Value
strColTwo = rCell.Value
lngColThree = 0
lngColFour = 0
lngColThree = rCell.Offset(0, 1).Value
lngColFour = rCell.Offset(0, 2).Value
intSumRow = intSumRow + 1
End If


Next rCell


wsSum.Cells(intSumRow, 1).Value = strColTwo
wsSum.Cells(intSumRow, 3).Value = lngColThree
wsSum.Cells(intSumRow, 4).Value = lngColFour


End Sub