Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro help
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim sh As Worksheet Dim NextRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Set sh = Worksheets("Sheet1") If sh.Range("A1").Value = "" Then NextRow = 1 ElseIf sh.Range("A2").Value = "" Then NextRow = 2 Else NextRow = sh.Range("a1").End(xlDown).Row + 1 End If .Offset(0, -1).Copy sh.Cells(NextRow, "A") sh.Cells(NextRow, "B").Value = .Offset(0, 1).Value + .Offset(0, 2).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro help
It keeps telling me to create a name and when i do it starts a new sub. in a
module. Not sure what to do. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim sh As Worksheet Dim NextRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Set sh = Worksheets("Sheet1") If sh.Range("A1").Value = "" Then NextRow = 1 ElseIf sh.Range("A2").Value = "" Then NextRow = 2 Else NextRow = sh.Range("a1").End(xlDown).Row + 1 End If .Offset(0, -1).Copy sh.Cells(NextRow, "A") sh.Cells(NextRow, "B").Value = .Offset(0, 1).Value + .Offset(0, 2).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro help
I have no idea what is going on, it didn't here.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... It keeps telling me to create a name and when i do it starts a new sub. in a module. Not sure what to do. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim sh As Worksheet Dim NextRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Set sh = Worksheets("Sheet1") If sh.Range("A1").Value = "" Then NextRow = 1 ElseIf sh.Range("A2").Value = "" Then NextRow = 2 Else NextRow = sh.Range("a1").End(xlDown).Row + 1 End If .Offset(0, -1).Copy sh.Cells(NextRow, "A") sh.Cells(NextRow, "B").Value = .Offset(0, 1).Value + .Offset(0, 2).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro help
Does it have anything to do with the heading? the Sub name. I have no idea
why its doing this. "Bob Phillips" wrote: I have no idea what is going on, it didn't here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... It keeps telling me to create a name and when i do it starts a new sub. in a module. Not sure what to do. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim sh As Worksheet Dim NextRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Set sh = Worksheets("Sheet1") If sh.Range("A1").Value = "" Then NextRow = 1 ElseIf sh.Range("A2").Value = "" Then NextRow = 2 Else NextRow = sh.Range("a1").End(xlDown).Row + 1 End If .Offset(0, -1).Copy sh.Cells(NextRow, "A") sh.Cells(NextRow, "B").Value = .Offset(0, 1).Value + .Offset(0, 2).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro help
What heading?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... Does it have anything to do with the heading? the Sub name. I have no idea why its doing this. "Bob Phillips" wrote: I have no idea what is going on, it didn't here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... It keeps telling me to create a name and when i do it starts a new sub. in a module. Not sure what to do. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim sh As Worksheet Dim NextRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Set sh = Worksheets("Sheet1") If sh.Range("A1").Value = "" Then NextRow = 1 ElseIf sh.Range("A2").Value = "" Then NextRow = 2 Else NextRow = sh.Range("a1").End(xlDown).Row + 1 End If .Offset(0, -1).Copy sh.Cells(NextRow, "A") sh.Cells(NextRow, "B").Value = .Offset(0, 1).Value + .Offset(0, 2).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ewing25" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Macro help? | Excel Discussion (Misc queries) | |||
Help with simple macro | Excel Discussion (Misc queries) | |||
Simple Macro? | Excel Programming | |||
Simple Macro - I Think? | Excel Programming | |||
Simple Macro - I Think? | Excel Programming |