| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten up som sheets-kode maby a loop !
Hi fellows,
is there a way to shorten this kode down,- maby a loop or som ? i havent found a solution yet,- so ned a little help pls.(im not even finish yet :-) ) Private Sub Worksheet_Change(ByVal Target As Range) If Range("E8").Value < "" Then If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(3, 5) Then Sheets(2).Cells(2, 3 + Month(Now())) = Sheets(2).Cells(2, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(4, 5) Then Sheets(2).Cells(3, 3 + Month(Now())) = Sheets(2).Cells(3, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(5, 5) Then Sheets(2).Cells(4, 3 + Month(Now())) = Sheets(2).Cells(4, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(6, 5) Then Sheets(2).Cells(5, 3 + Month(Now())) = Sheets(2).Cells(5, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(7, 5) Then Sheets(2).Cells(6, 3 + Month(Now())) = Sheets(2).Cells(6, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(8, 5) Then Sheets(2).Cells(7, 3 + Month(Now())) = Sheets(2).Cells(7, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(9, 5) Then Sheets(2).Cells(8, 3 + Month(Now())) = Sheets(2).Cells(8, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(10, 5) Then Sheets(2).Cells(9, 3 + Month(Now())) = Sheets(2).Cells(9, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(11, 5) Then Sheets(2).Cells(10, 3 + Month(Now())) = Sheets(2).Cells(10, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(12, 5) Then Sheets(2).Cells(11, 3 + Month(Now())) = Sheets(2).Cells(11, 3 + Month(Now())) + Sheets(1).Cells(8, 5) 's2 If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(3, 5) Then Sheets(2).Cells(14, 3 + Month(Now())) = Sheets(2).Cells(14, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(4, 5) Then Sheets(2).Cells(15, 3 + Month(Now())) = Sheets(2).Cells(15, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(5, 5) Then Sheets(2).Cells(16, 3 + Month(Now())) = Sheets(2).Cells(16, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(6, 5) Then Sheets(2).Cells(17, 3 + Month(Now())) = Sheets(2).Cells(17, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(7, 5) Then Sheets(2).Cells(18, 3 + Month(Now())) = Sheets(2).Cells(18, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(8, 5) Then Sheets(2).Cells(19, 3 + Month(Now())) = Sheets(2).Cells(19, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(9, 5) Then Sheets(2).Cells(20, 3 + Month(Now())) = Sheets(2).Cells(20, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(10, 5) Then Sheets(2).Cells(21, 3 + Month(Now())) = Sheets(2).Cells(21, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(11, 5) Then Sheets(2).Cells(22, 3 + Month(Now())) = Sheets(2).Cells(22, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(12, 5) Then Sheets(2).Cells(23, 3 + Month(Now())) = Sheets(2).Cells(23, 3 + Month(Now())) + Sheets(1).Cells(8, 5) 's3 's4 'and so on until 20 '20 If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(3, 5) Then Sheets(2).Cells(230, 3 + Month(Now())) = Sheets(2).Cells(230, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(4, 5) Then Sheets(2).Cells(231, 3 + Month(Now())) = Sheets(2).Cells(231, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(5, 5) Then Sheets(2).Cells(232, 3 + Month(Now())) = Sheets(2).Cells(232, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(6, 5) Then Sheets(2).Cells(233, 3 + Month(Now())) = Sheets(2).Cells(233, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(7, 5) Then Sheets(2).Cells(234, 3 + Month(Now())) = Sheets(2).Cells(234, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(8, 5) Then Sheets(2).Cells(235, 3 + Month(Now())) = Sheets(2).Cells(235, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(9, 5) Then Sheets(2).Cells(236, 3 + Month(Now())) = Sheets(2).Cells(236, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(10, 5) Then Sheets(2).Cells(237, 3 + Month(Now())) = Sheets(2).Cells(237, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(11, 5) Then Sheets(2).Cells(238, 3 + Month(Now())) = Sheets(2).Cells(238, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(12, 5) Then Sheets(2).Cells(239, 3 + Month(Now())) = Sheets(2).Cells(239, 3 + Month(Now())) + Sheets(1).Cells(8, 5) End If Sheets(1).Cells(8, 5) = "" End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nMonth As Long Dim i As Long, j As Long If Range("E8").Value < "" Then nMonth = Month(Now) For i = 0 To 19 For j = 0 To 9 If Cells(8, 3) = Sheets(3).Cells(i + 2, 2) And _ Cells(8, 4) = Sheets(3).Cells(i + 2, 5) Then _ Sheets(2).Cells(i * 12 + 2 + j, 3 + nMonth) = _ Sheets(2).Cells(i * 12 + 2 + j, 3 + nMonth) + Sheets(1).Cells(8, 5) Next j Next i End If Sheets(1).Cells(8, 5) = "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "excelent" wrote in message ... Hi fellows, is there a way to shorten this kode down,- maby a loop or som ? i havent found a solution yet,- so ned a little help pls.(im not even finish yet :-) ) Private Sub Worksheet_Change(ByVal Target As Range) If Range("E8").Value < "" Then If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(3, 5) Then Sheets(2).Cells(2, 3 + Month(Now())) = Sheets(2).Cells(2, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(4, 5) Then Sheets(2).Cells(3, 3 + Month(Now())) = Sheets(2).Cells(3, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(5, 5) Then Sheets(2).Cells(4, 3 + Month(Now())) = Sheets(2).Cells(4, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(6, 5) Then Sheets(2).Cells(5, 3 + Month(Now())) = Sheets(2).Cells(5, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(7, 5) Then Sheets(2).Cells(6, 3 + Month(Now())) = Sheets(2).Cells(6, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(8, 5) Then Sheets(2).Cells(7, 3 + Month(Now())) = Sheets(2).Cells(7, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(9, 5) Then Sheets(2).Cells(8, 3 + Month(Now())) = Sheets(2).Cells(8, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(10, 5) Then Sheets(2).Cells(9, 3 + Month(Now())) = Sheets(2).Cells(9, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(11, 5) Then Sheets(2).Cells(10, 3 + Month(Now())) = Sheets(2).Cells(10, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(3, 2) And Cells(8, 4) = Sheets(3).Cells(12, 5) Then Sheets(2).Cells(11, 3 + Month(Now())) = Sheets(2).Cells(11, 3 + Month(Now())) + Sheets(1).Cells(8, 5) 's2 If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(3, 5) Then Sheets(2).Cells(14, 3 + Month(Now())) = Sheets(2).Cells(14, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(4, 5) Then Sheets(2).Cells(15, 3 + Month(Now())) = Sheets(2).Cells(15, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(5, 5) Then Sheets(2).Cells(16, 3 + Month(Now())) = Sheets(2).Cells(16, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(6, 5) Then Sheets(2).Cells(17, 3 + Month(Now())) = Sheets(2).Cells(17, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(7, 5) Then Sheets(2).Cells(18, 3 + Month(Now())) = Sheets(2).Cells(18, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(8, 5) Then Sheets(2).Cells(19, 3 + Month(Now())) = Sheets(2).Cells(19, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(9, 5) Then Sheets(2).Cells(20, 3 + Month(Now())) = Sheets(2).Cells(20, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(10, 5) Then Sheets(2).Cells(21, 3 + Month(Now())) = Sheets(2).Cells(21, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(11, 5) Then Sheets(2).Cells(22, 3 + Month(Now())) = Sheets(2).Cells(22, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(4, 2) And Cells(8, 4) = Sheets(3).Cells(12, 5) Then Sheets(2).Cells(23, 3 + Month(Now())) = Sheets(2).Cells(23, 3 + Month(Now())) + Sheets(1).Cells(8, 5) 's3 's4 'and so on until 20 '20 If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(3, 5) Then Sheets(2).Cells(230, 3 + Month(Now())) = Sheets(2).Cells(230, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(4, 5) Then Sheets(2).Cells(231, 3 + Month(Now())) = Sheets(2).Cells(231, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(5, 5) Then Sheets(2).Cells(232, 3 + Month(Now())) = Sheets(2).Cells(232, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(6, 5) Then Sheets(2).Cells(233, 3 + Month(Now())) = Sheets(2).Cells(233, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(7, 5) Then Sheets(2).Cells(234, 3 + Month(Now())) = Sheets(2).Cells(234, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(8, 5) Then Sheets(2).Cells(235, 3 + Month(Now())) = Sheets(2).Cells(235, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(9, 5) Then Sheets(2).Cells(236, 3 + Month(Now())) = Sheets(2).Cells(236, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(10, 5) Then Sheets(2).Cells(237, 3 + Month(Now())) = Sheets(2).Cells(237, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(11, 5) Then Sheets(2).Cells(238, 3 + Month(Now())) = Sheets(2).Cells(238, 3 + Month(Now())) + Sheets(1).Cells(8, 5) If Cells(8, 3) = Sheets(3).Cells(22, 2) And Cells(8, 4) = Sheets(3).Cells(12, 5) Then Sheets(2).Cells(239, 3 + Month(Now())) = Sheets(2).Cells(239, 3 + Month(Now())) + Sheets(1).Cells(8, 5) End If Sheets(1).Cells(8, 5) = "" End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
Hi Bob tks. for ur reply
it's close to be right, but problem is that salesman got £ in all activity not only the 1 i chose in sheet 1. can u fix that ? maby i can send u the file ? (hard to explane for 1 from Denmark :-) ) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
You can send it to me, I will take a look, but I can't promise anything.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "excelent" wrote in message ... Hi Bob tks. for ur reply it's close to be right, but problem is that salesman got £ in all activity not only the 1 i chose in sheet 1. can u fix that ? maby i can send u the file ? (hard to explane for 1 from Denmark :-) ) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
ok tks. jost do if poseboll
http//pmexcelent.dk/SalgRapport2.xls "Bob Phillips" skrev: You can send it to me, I will take a look, but I can't promise anything. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "excelent" wrote in message ... Hi Bob tks. for ur reply it's close to be right, but problem is that salesman got £ in all activity not only the 1 i chose in sheet 1. can u fix that ? maby i can send u the file ? (hard to explane for 1 from Denmark :-) ) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
|
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten up som sheets-kode maby a loop !
Hi Bob
got it right now tks. for ur help well done :-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| moving through sheets in a loop | Excel Programming | |||
| Loop in sheets | Excel Programming | |||
| Loop through all sheets in workbook | Excel Programming | |||
| Loop through sheets | Excel Programming | |||
| Loop across Sheets and number of sheets | Excel Programming | |||
Linear Mode
