Macro Editing
I want to find an object in a column that matches the cell I am in, but I
want it to be interactive so that it always looks for whatever data exists in the current cell I am in. I am no programmer but I do edit Macros to do what I want. What I have tried to do is copy the current cell while recording a macro and then go to the column that I want to find this information in and select find. I then paste what is stored on the clipboard into the find box and then I end the Macro. What I see in the Macro is not what I want though. I want to paste what is on the current clipboard not a fixed set of text "Northwest Region - NW Region - Dist 3". ActiveCell.Offset(0, -1).Range("A1").Select Selection.Copy ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select ActiveCell.Offset(-12, -3).Range("A1").Activate Selection.Find(What:="Northwest Region - NW Region - Dist 3", After:= _ ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Range("A1").Select End Sub -- db |
Macro Editing
Don,
I have tried what you input but I am getting an Object Variable not set?? The macro gets down to here and stops with this error. I have input Column L into the popup box and then the macro fails. ActiveCell.Offset(0, -1).Range("A1").Copy _ Columns(mc).Find(What:=mt, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(, 1) -- db "Don Guillett" wrote: This will copy the cell one to the left from the active cell to one to the right of finding your text in the column requested. Is that what you want? Sub copyactivecelllessone() mc = InputBox("Enter column to search ie: C") mt = "xx" ActiveCell.Offset(0, -1).Range("A1").Copy _ Columns(mc).Find(What:=mt, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(, 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Doug B" wrote in message ... I want to find an object in a column that matches the cell I am in, but I want it to be interactive so that it always looks for whatever data exists in the current cell I am in. I am no programmer but I do edit Macros to do what I want. What I have tried to do is copy the current cell while recording a macro and then go to the column that I want to find this information in and select find. I then paste what is stored on the clipboard into the find box and then I end the Macro. What I see in the Macro is not what I want though. I want to paste what is on the current clipboard not a fixed set of text "Northwest Region - NW Region - Dist 3". ActiveCell.Offset(0, -1).Range("A1").Select Selection.Copy ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select ActiveCell.Offset(-12, -3).Range("A1").Activate Selection.Find(What:="Northwest Region - NW Region - Dist 3", After:= _ ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Range("A1").Select End Sub -- db |
Macro Editing
Perhaps you didn't change xx to your desired search string.
If all else fails, send your file to my address below along with a copy of this and clear explanations. -- Don Guillett Microsoft MVP Excel SalesAid Software "Doug B" wrote in message ... Don, I have tried what you input but I am getting an Object Variable not set?? The macro gets down to here and stops with this error. I have input Column L into the popup box and then the macro fails. ActiveCell.Offset(0, -1).Range("A1").Copy _ Columns(mc).Find(What:=mt, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(, 1) -- db "Don Guillett" wrote: This will copy the cell one to the left from the active cell to one to the right of finding your text in the column requested. Is that what you want? Sub copyactivecelllessone() mc = InputBox("Enter column to search ie: C") mt = "xx" ActiveCell.Offset(0, -1).Range("A1").Copy _ Columns(mc).Find(What:=mt, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(, 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Doug B" wrote in message ... I want to find an object in a column that matches the cell I am in, but I want it to be interactive so that it always looks for whatever data exists in the current cell I am in. I am no programmer but I do edit Macros to do what I want. What I have tried to do is copy the current cell while recording a macro and then go to the column that I want to find this information in and select find. I then paste what is stored on the clipboard into the find box and then I end the Macro. What I see in the Macro is not what I want though. I want to paste what is on the current clipboard not a fixed set of text "Northwest Region - NW Region - Dist 3". ActiveCell.Offset(0, -1).Range("A1").Select Selection.Copy ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select ActiveCell.Offset(-12, -3).Range("A1").Activate Selection.Find(What:="Northwest Region - NW Region - Dist 3", After:= _ ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Range("A1").Select End Sub -- db |
Macro Editing
first one does one at at time based on a double click in the next empty
cell. 2nd does all for you at once. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column < 16 Then Exit Sub Range(Target, Target.End(xlToRight)).ClearContents what = Target.Offset(, -1) p1 = Columns("L").Find(what, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row 'MsgBox p1 p2 = Application.CountIf(Columns("L"), Target.Offset(, -1)) 'MsgBox p2 Cells(p1, "m").Resize(p2).Copy 'target Target.PasteSpecial Paste:=xlPasteAll, Transpose:=True Application.CutCopyMode = False End Sub Sub doemall() 'Looks in col p for blanks to fill in for data in col o r1 = Cells(Rows.Count, "p").End(xlUp).Row + 1 'MsgBox r1 r2 = Cells(Rows.Count, "o").End(xlUp).Row ' 'MsgBox r2 'finds the data rangecopiestransposes On Error Resume Next For i = r1 To r2 Range(Cells(i, "q"), Cells(i, "q").End(xlToRight)).ClearContents what = Cells(i, "o") p1 = Columns("L").Find(what, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row 'MsgBox p1 p2 = Application.CountIf(Columns("L"), Cells(i, "o")) 'MsgBox p2 Cells(p1, "m").Resize(p2).Copy Cells(i, "p").PasteSpecial Paste:=xlPasteAll, Transpose:=True Application.CutCopyMode = False Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... This will copy the cell one to the left from the active cell to one to the right of finding your text in the column requested. Is that what you want? Sub copyactivecelllessone() mc = InputBox("Enter column to search ie: C") mt = "xx" ActiveCell.Offset(0, -1).Range("A1").Copy _ Columns(mc).Find(What:=mt, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(, 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Doug B" wrote in message ... I want to find an object in a column that matches the cell I am in, but I want it to be interactive so that it always looks for whatever data exists in the current cell I am in. I am no programmer but I do edit Macros to do what I want. What I have tried to do is copy the current cell while recording a macro and then go to the column that I want to find this information in and select find. I then paste what is stored on the clipboard into the find box and then I end the Macro. What I see in the Macro is not what I want though. I want to paste what is on the current clipboard not a fixed set of text "Northwest Region - NW Region - Dist 3". ActiveCell.Offset(0, -1).Range("A1").Select Selection.Copy ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select ActiveCell.Offset(-12, -3).Range("A1").Activate Selection.Find(What:="Northwest Region - NW Region - Dist 3", After:= _ ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Range("A1").Select End Sub -- db |
All times are GMT +1. The time now is 06:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com