Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 15th 09, 05:54 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 2
Default 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

  #2   Report Post  
Old May 15th 09, 06:24 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default Macro Editing

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


  #3   Report Post  
Old May 15th 09, 07:04 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 2
Default 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



  #4   Report Post  
Old May 15th 09, 07:18 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default 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




  #5   Report Post  
Old May 15th 09, 09:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help editing a macro ZBelden Excel Discussion (Misc queries) 3 February 12th 08 05:18 PM
Macro editing Skeeter Excel Discussion (Misc queries) 5 August 6th 07 01:40 AM
Editing a macro pm Excel Discussion (Misc queries) 5 June 6th 07 11:33 PM
Editing a macro Dtmos01 Excel Discussion (Misc queries) 6 April 3rd 07 06:36 PM
Editing Macro heitorfjr Excel Discussion (Misc queries) 1 January 15th 06 08:02 PM


All times are GMT +1. The time now is 02:02 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017