No idea how to start this.

E

Ewing25

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
 
S

Susan

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
 
M

Mike B.

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.
 
E

Ewing25

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
 
A

Aviashn

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
 
A

Aviashn

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
Click to expand...

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

Ewing25

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.
 
A

Aviashn

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
 
E

Ewing25

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I have no idea how to start this. 3
Simple Macro help 5
Multiple SUM's in a column 15
fill in data that is not empty 1
I really need help 1
Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why? 1
VBAscript for data matching excels 0
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7

Top