| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel-Macro Problem
Pls help me find soln to the below problem:
Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Rule of generating Sheet3 is not clear! If you want to sum up values in rows
with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Sorry Stefi, u r right!.it is 16.6 and 8.3
"Stefi" wrote: Rule of generating Sheet3 is not clear! If you want to sum up values in rows with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
List all instances of Reqn in Sheet3 column A:
Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 formula1 formula2 Req2 Drag down formulae as required! formula1: =SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L) formula2: =SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L) Regards, Stefi €žSR€ ezt Ã*rta: Sorry Stefi, u r right!.it is 16.6 and 8.3 "Stefi" wrote: Rule of generating Sheet3 is not clear! If you want to sum up values in rows with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Hi,
I need a macro to relect the values in column B of sheet 1 and sheet 2 to be incorporated without duplicates in column A of sheet3.This needs to be done along with the summation of values from column L of sheet1 and sheet2 in to sheet3(column B and column C). Pls help!! "Stefi" wrote: List all instances of Reqn in Sheet3 column A: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 formula1 formula2 Req2 Drag down formulae as required! formula1: =SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L) formula2: =SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L) Regards, Stefi €žSR€ ezt Ã*rta: Sorry Stefi, u r right!.it is 16.6 and 8.3 "Stefi" wrote: Rule of generating Sheet3 is not clear! If you want to sum up values in rows with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Here you a
Sub test() Worksheets("Sheet1").Range("A1").Value = "reqslist" Worksheets("Sheet2").Range("A1").Value = "reqslist" lastr_sh1 = Worksheets("Sheet1").Columns("A:A").End(xlDown).Ro w lastr_sh2 = Worksheets("Sheet2").Columns("A:A").End(xlDown).Ro w Worksheets("Sheet1").Range("A2:A" & lastr_sh1).Copy Destination:=Worksheets("Sheet3").Range("A2") Worksheets("Sheet2").Range("A2:A" & lastr_sh2).Copy Destination:=Worksheets("Sheet3").Range("A" & lastr_sh1 + 1) Worksheets("Sheet3").Select Range("A1").Value = "reqslist" lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Columns("A:A").Select Range("A1:A" & lastr_sh3).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Selection.CurrentRegion.Select Selection.Copy Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.ShowAllData Columns("A").Delete lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Range("B2").Formula = "=SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L)" Range("C2").Formula = "=SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L)" Range("B2:C2").AutoFill Destination:=Range("B2:C" & lastr_sh3), Type:=xlFillDefault End Sub Regards, Stefi €žSR€ ezt Ã*rta: Hi, I need a macro to relect the values in column B of sheet 1 and sheet 2 to be incorporated without duplicates in column A of sheet3.This needs to be done along with the summation of values from column L of sheet1 and sheet2 in to sheet3(column B and column C). Pls help!! "Stefi" wrote: List all instances of Reqn in Sheet3 column A: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 formula1 formula2 Req2 Drag down formulae as required! formula1: =SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L) formula2: =SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L) Regards, Stefi €žSR€ ezt Ã*rta: Sorry Stefi, u r right!.it is 16.6 and 8.3 "Stefi" wrote: Rule of generating Sheet3 is not clear! If you want to sum up values in rows with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Thanks Stefi,
But could u give me a genaralized (which can have any value for data) function which meets the following requirements: Sheet1 --values start from B6, L6 Sheet2---values start from B24, L24 Output Sheet Sheet3--values start from A2, B2,C2 "Stefi" wrote: Here you a Sub test() Worksheets("Sheet1").Range("A1").Value = "reqslist" Worksheets("Sheet2").Range("A1").Value = "reqslist" lastr_sh1 = Worksheets("Sheet1").Columns("A:A").End(xlDown).Ro w lastr_sh2 = Worksheets("Sheet2").Columns("A:A").End(xlDown).Ro w Worksheets("Sheet1").Range("A2:A" & lastr_sh1).Copy Destination:=Worksheets("Sheet3").Range("A2") Worksheets("Sheet2").Range("A2:A" & lastr_sh2).Copy Destination:=Worksheets("Sheet3").Range("A" & lastr_sh1 + 1) Worksheets("Sheet3").Select Range("A1").Value = "reqslist" lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Columns("A:A").Select Range("A1:A" & lastr_sh3).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Selection.CurrentRegion.Select Selection.Copy Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.ShowAllData Columns("A").Delete lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Range("B2").Formula = "=SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L)" Range("C2").Formula = "=SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L)" Range("B2:C2").AutoFill Destination:=Range("B2:C" & lastr_sh3), Type:=xlFillDefault End Sub Regards, Stefi €žSR€ ezt Ã*rta: Hi, I need a macro to relect the values in column B of sheet 1 and sheet 2 to be incorporated without duplicates in column A of sheet3.This needs to be done along with the summation of values from column L of sheet1 and sheet2 in to sheet3(column B and column C). Pls help!! "Stefi" wrote: List all instances of Reqn in Sheet3 column A: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 formula1 formula2 Req2 Drag down formulae as required! formula1: =SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L) formula2: =SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L) Regards, Stefi €žSR€ ezt Ã*rta: Sorry Stefi, u r right!.it is 16.6 and 8.3 "Stefi" wrote: Rule of generating Sheet3 is not clear! If you want to sum up values in rows with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Tomorrow or next week!
Stefi €žSR€ ezt Ã*rta: Thanks Stefi, But could u give me a genaralized (which can have any value for data) function which meets the following requirements: Sheet1 --values start from B6, L6 Sheet2---values start from B24, L24 Output Sheet Sheet3--values start from A2, B2,C2 "Stefi" wrote: Here you a Sub test() Worksheets("Sheet1").Range("A1").Value = "reqslist" Worksheets("Sheet2").Range("A1").Value = "reqslist" lastr_sh1 = Worksheets("Sheet1").Columns("A:A").End(xlDown).Ro w lastr_sh2 = Worksheets("Sheet2").Columns("A:A").End(xlDown).Ro w Worksheets("Sheet1").Range("A2:A" & lastr_sh1).Copy Destination:=Worksheets("Sheet3").Range("A2") Worksheets("Sheet2").Range("A2:A" & lastr_sh2).Copy Destination:=Worksheets("Sheet3").Range("A" & lastr_sh1 + 1) Worksheets("Sheet3").Select Range("A1").Value = "reqslist" lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Columns("A:A").Select Range("A1:A" & lastr_sh3).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Selection.CurrentRegion.Select Selection.Copy Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.ShowAllData Columns("A").Delete lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Range("B2").Formula = "=SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L)" Range("C2").Formula = "=SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L)" Range("B2:C2").AutoFill Destination:=Range("B2:C" & lastr_sh3), Type:=xlFillDefault End Sub Regards, Stefi €žSR€ ezt Ã*rta: Hi, I need a macro to relect the values in column B of sheet 1 and sheet 2 to be incorporated without duplicates in column A of sheet3.This needs to be done along with the summation of values from column L of sheet1 and sheet2 in to sheet3(column B and column C). Pls help!! "Stefi" wrote: List all instances of Reqn in Sheet3 column A: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 formula1 formula2 Req2 Drag down formulae as required! formula1: =SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L) formula2: =SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L) Regards, Stefi €žSR€ ezt Ã*rta: Sorry Stefi, u r right!.it is 16.6 and 8.3 "Stefi" wrote: Rule of generating Sheet3 is not clear! If you want to sum up values in rows with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3 column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true? Regards, Stefi €žSR€ ezt Ã*rta: Pls help me find soln to the below problem: Requirement: Sheet1 Colm B Colm L Req1 8.2 Req2 8.3 Req1 8.4 Sheet2 Colm B Colm L Req1 5.1 Req2 8.5 Req1 5.2 Upon Button click of Sheet2, Sheet3 values should be as shown below: Sheet3 Colm A Colm B (as per sheet1) Colm C (as per sheet2) Req1 8.6 10.3 Req2 8.3 8.5 Pls guide.. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel-Macro Problem
Sub test_caller()
Call Par_test(2, 24) End Sub Sub Par_test(startr_sh1, startr_sh2) Worksheets("Sheet1").Range("B1").Value = "reqslist" Worksheets("Sheet2").Range("B1").Value = "reqslist" lastr_sh1 = Worksheets("Sheet1").Range("B" & startr_sh1 & ":B" & Rows.Count).End(xlDown).Row lastr_sh2 = Worksheets("Sheet2").Range("B" & startr_sh2 & ":B" & Rows.Count).End(xlDown).Row Worksheets("Sheet1").Range("B" & startr_sh1 & ":B" & lastr_sh1).Copy _ Destination:=Worksheets("Sheet3").Range("A2") Worksheets("Sheet2").Range("B" & startr_sh2 & ":B" & lastr_sh2).Copy _ Destination:=Worksheets("Sheet3").Range("A" & lastr_sh1 + 1) Worksheets("Sheet3").Select Range("A1").Value = "reqslist" 'column A must have a header to make "AdvancedFilter Action:=xlFilterInPlace, Unique:=True" work well! lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Columns("A:A").Select Range("A1:A" & lastr_sh3).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Selection.CurrentRegion.Select Selection.Copy Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.ShowAllData Columns("A").Delete lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w Range("B2").Formula = "=SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L)" Range("C2").Formula = "=SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L)" Range("B2:C2").AutoFill Destination:=Range("B2:C" & lastr_sh3), Type:=xlFillDefault End Sub Regards, Stefi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing Problem In Excel Due to Macro? | Excel Discussion (Misc queries) | |||
| Macro problem in Excel | Excel Discussion (Misc queries) | |||
| problem with macro on Excel | Excel Worksheet Functions | |||
| problem with import files by excel macro | Excel Discussion (Misc queries) | |||
| Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Linear Mode
