Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
This is my macro..
Sub FindDuplicates() Sheets("Current Month").Select Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault Range("R2:R2000").Select Sheets("Current Month").Select Columns("R:R").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
Sub FindDuplicates()
Dim SheetName As String Dim Target As Range Set Target = Application.InputBox("Select any cell on the target sheet with the mouse", Type:=8) If Not Target Is Nothing Then SheetName = Target.Parent.Name With Sheets("Current Month") .Range("R2").FormulaR1C1 = _ "=IF(COUNTIF('" & SheetName & "'!C[-14],RC[-14])=1,""Y"","""")" .Range("R2").AutoFill Destination:=.Range("R2:R2000"), Type:=xlFillDefaultSelect .Columns("R:R").Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Range("R3").Select End With Application.CutCopyMode = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... This is my macro.. Sub FindDuplicates() Sheets("Current Month").Select Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault Range("R2:R2000").Select Sheets("Current Month").Select Columns("R:R").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
On Apr 28, 9:32*am, "Bob Phillips" wrote:
Sub FindDuplicates() Dim SheetName As String Dim Target As Range * * Set Target = Application.InputBox("Select any cell on the target sheet with the mouse", Type:=8) * * If Not Target Is Nothing Then * * * * SheetName = Target.Parent.Name * * * * With Sheets("Current Month") * * * * * * .Range("R2").FormulaR1C1 = _ * * * * * * * * "=IF(COUNTIF('" & SheetName & "'!C[-14],RC[-14])=1,""Y"","""")" * * * * * * .Range("R2").AutoFill Destination:=.Range("R2:R2000"), Type:=xlFillDefaultSelect * * * * * * .Columns("R:R").Copy * * * * * * .PasteSpecial Paste:=xlPasteValues, _ * * * * * * * * * * * * * Operation:=xlNone, _ * * * * * * * * * * * * * SkipBlanks:=False, _ * * * * * * * * * * * * * Transpose:=False * * * * * * .Range("R3").Select * * * * End With * * * * Application.CutCopyMode = False * * End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... This is my macro.. Sub FindDuplicates() * *Sheets("Current Month").Select * *Range("R2").Select * *ActiveCell.FormulaR1C1 = _ * * * *"=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" * *Range("R2").Select * *Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault * *Range("R2:R2000").Select * *Sheets("Current Month").Select * *Columns("R:R").Select * *Selection.Copy * *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * *:=False, Transpose:=False * *Application.CutCopyMode = False * *Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance.- Hide quoted text - - Show quoted text - This is brilliant, thankyou. I had to edit the end bit though, cos i kept getting that the desination cells were different - so i changed .Columns("R:R").Copy To Columns("R:R").Select Selection.Copy But thats minor, thanks very much Bob. Ive tried it now with 2 worksheets, is there a way of getting to to leave the Y if it is already there - rather than overwriting it?? Thanks again if someone can help with this tweak. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
Leave which Y where?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... On Apr 28, 9:32 am, "Bob Phillips" wrote: Sub FindDuplicates() Dim SheetName As String Dim Target As Range Set Target = Application.InputBox("Select any cell on the target sheet with the mouse", Type:=8) If Not Target Is Nothing Then SheetName = Target.Parent.Name With Sheets("Current Month") .Range("R2").FormulaR1C1 = _ "=IF(COUNTIF('" & SheetName & "'!C[-14],RC[-14])=1,""Y"","""")" .Range("R2").AutoFill Destination:=.Range("R2:R2000"), Type:=xlFillDefaultSelect .Columns("R:R").Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Range("R3").Select End With Application.CutCopyMode = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... This is my macro.. Sub FindDuplicates() Sheets("Current Month").Select Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault Range("R2:R2000").Select Sheets("Current Month").Select Columns("R:R").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance.- Hide quoted text - - Show quoted text - This is brilliant, thankyou. I had to edit the end bit though, cos i kept getting that the desination cells were different - so i changed ..Columns("R:R").Copy To Columns("R:R").Select Selection.Copy But thats minor, thanks very much Bob. Ive tried it now with 2 worksheets, is there a way of getting to to leave the Y if it is already there - rather than overwriting it?? Thanks again if someone can help with this tweak. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
On Apr 28, 10:43*am, "Bob Phillips" wrote:
Leave which Y where? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... On Apr 28, 9:32 am, "Bob Phillips" wrote: Sub FindDuplicates() Dim SheetName As String Dim Target As Range Set Target = Application.InputBox("Select any cell on the target sheet with the mouse", Type:=8) If Not Target Is Nothing Then SheetName = Target.Parent.Name With Sheets("Current Month") .Range("R2").FormulaR1C1 = _ "=IF(COUNTIF('" & SheetName & "'!C[-14],RC[-14])=1,""Y"","""")" .Range("R2").AutoFill Destination:=.Range("R2:R2000"), Type:=xlFillDefaultSelect .Columns("R:R").Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Range("R3").Select End With Application.CutCopyMode = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... This is my macro.. Sub FindDuplicates() Sheets("Current Month").Select Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault Range("R2:R2000").Select Sheets("Current Month").Select Columns("R:R").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance.- Hide quoted text - - Show quoted text - This is brilliant, thankyou. I had to edit the end bit though, cos i kept getting that the desination cells were different - so i changed .Columns("R:R").Copy To * * Columns("R:R").Select * * Selection.Copy But thats minor, thanks very much Bob. Ive tried it now with 2 worksheets, is there a way of getting to to leave the Y if it is already there - rather than overwriting it?? Thanks again if someone can help with this tweak.- Hide quoted text - - Show quoted text - It shows if its a duplicate by putting a Y... but if i re-do it again referencing it to another tab, it overwrites any Y thats there with a space/blank cell if it isnt applicable again - can i get it to leave it as a Y if its there? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
Does this do what you want?
Sub FindDuplicates() Dim SheetName As String Dim Target As Range Set Target = Application.InputBox( _ "Select any cell on the target sheet with the mouse", Type:=8) If Not Target Is Nothing Then SheetName = Target.Parent.Name With Sheets("Current Month") If .Range("R2").Value < "Y" Then .Range("R2").FormulaR1C1 = _ "=IF(COUNTIF('" & SheetName & _ "'!C[-14],RC[-14])=1,""Y"","""")" .Range("R2").AutoFill Destination:=.Range("R2:R2000"), _ Type:=xlFillDefaultSelect .Columns("R:R").Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Range("R3").Select End If End With Application.CutCopyMode = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... On Apr 28, 10:43 am, "Bob Phillips" wrote: Leave which Y where? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... On Apr 28, 9:32 am, "Bob Phillips" wrote: Sub FindDuplicates() Dim SheetName As String Dim Target As Range Set Target = Application.InputBox("Select any cell on the target sheet with the mouse", Type:=8) If Not Target Is Nothing Then SheetName = Target.Parent.Name With Sheets("Current Month") .Range("R2").FormulaR1C1 = _ "=IF(COUNTIF('" & SheetName & "'!C[-14],RC[-14])=1,""Y"","""")" .Range("R2").AutoFill Destination:=.Range("R2:R2000"), Type:=xlFillDefaultSelect .Columns("R:R").Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Range("R3").Select End With Application.CutCopyMode = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... This is my macro.. Sub FindDuplicates() Sheets("Current Month").Select Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault Range("R2:R2000").Select Sheets("Current Month").Select Columns("R:R").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance.- Hide quoted text - - Show quoted text - This is brilliant, thankyou. I had to edit the end bit though, cos i kept getting that the desination cells were different - so i changed .Columns("R:R").Copy To Columns("R:R").Select Selection.Copy But thats minor, thanks very much Bob. Ive tried it now with 2 worksheets, is there a way of getting to to leave the Y if it is already there - rather than overwriting it?? Thanks again if someone can help with this tweak.- Hide quoted text - - Show quoted text - It shows if its a duplicate by putting a Y... but if i re-do it again referencing it to another tab, it overwrites any Y thats there with a space/blank cell if it isnt applicable again - can i get it to leave it as a Y if its there? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
On Apr 29, 10:18*am, "Bob Phillips" wrote:
Does this do what you want? Sub FindDuplicates() Dim SheetName As String Dim Target As Range * * Set Target = Application.InputBox( _ * * * * "Select any cell on the target sheet with the mouse", Type:=8) * * If Not Target Is Nothing Then * * * * SheetName = Target.Parent.Name * * * * With Sheets("Current Month") * * * * * * If .Range("R2").Value < "Y" Then * * * * * * * * .Range("R2").FormulaR1C1 = _ * * * * * * * * * * "=IF(COUNTIF('" & SheetName & _ * * * * * * * * * * * * "'!C[-14],RC[-14])=1,""Y"","""")" * * * * * * * * .Range("R2").AutoFill Destination:=.Range("R2:R2000"), _ * * * * * * * * * * * * * * * * * * * * * * * * * Type:=xlFillDefaultSelect * * * * * * * * .Columns("R:R").Copy * * * * * * * * .PasteSpecial Paste:=xlPasteValues, _ * * * * * * * * * * * * * Operation:=xlNone, _ * * * * * * * * * * * * * SkipBlanks:=False, _ * * * * * * * * * * * * * Transpose:=False * * * * * * * * .Range("R3").Select * * * * * * End If * * * * End With * * * * Application.CutCopyMode = False * * End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... On Apr 28, 10:43 am, "Bob Phillips" wrote: Leave which Y where? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message ... On Apr 28, 9:32 am, "Bob Phillips" wrote: Sub FindDuplicates() Dim SheetName As String Dim Target As Range Set Target = Application.InputBox("Select any cell on the target sheet with the mouse", Type:=8) If Not Target Is Nothing Then SheetName = Target.Parent.Name With Sheets("Current Month") .Range("R2").FormulaR1C1 = _ "=IF(COUNTIF('" & SheetName & "'!C[-14],RC[-14])=1,""Y"","""")" .Range("R2").AutoFill Destination:=.Range("R2:R2000"), Type:=xlFillDefaultSelect .Columns("R:R").Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Range("R3").Select End With Application.CutCopyMode = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NPell" wrote in message .... This is my macro.. Sub FindDuplicates() Sheets("Current Month").Select Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R2000"), Type:=xlFillDefault Range("R2:R2000").Select Sheets("Current Month").Select Columns("R:R").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("R3").Select End Sub It works fine, but the Worksheet name "Duplicates" varies and i need to be able to select different cells - how can i make a pop up box to type in the Month name, or just select the right worksheet, or even column (although it is D:D on every worksheet), If you can help - thanks in advance.- Hide quoted text - - Show quoted text - This is brilliant, thankyou. I had to edit the end bit though, cos i kept getting that the desination cells were different - so i changed .Columns("R:R").Copy To Columns("R:R").Select Selection.Copy But thats minor, thanks very much Bob. Ive tried it now with 2 worksheets, is there a way of getting to to leave the Y if it is already there - rather than overwriting it?? Thanks again if someone can help with this tweak.- Hide quoted text - - Show quoted text - It shows if its a duplicate by putting a Y... but if i re-do it again referencing it to another tab, it overwrites any Y thats there with a space/blank cell if it isnt applicable again - can i get it to leave it as a Y if its there?- Hide quoted text - - Show quoted text - Awesome, thanks mate. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |