Find Variant

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following set up to copy data from one workbook, search for that
data in another workbook (I'm not replacing the data, but moving over 2 cells
and continuing...)
I'd like for it to search for the data without requiring my to paste into
Find/Replace. Is this possible?

Dim FindString As String
Dim Rng As Range


Application.CutCopyMode = False
Selection.Copy
Windows("Updated Consumer Acc List 2005_02_15.xls").Activate

FindString = InputBox("Enter a Search value")
Cells.Find(What:=FindString, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
 
hi,
yes it is possible by creating a variable which you
already have.
Dim FindString As String
Dim Rng As Range
Application.CutCopyMode = False
'Selection.Copy
'Instead of selection.copy set FindString to equal the
'activecell. if you are using the selection.copy command
'then that is telling me you have found what you want to
'copy so selection is the current(active)cell.
'since your are not coping the data, after you find the
'date on the other sheet, just turn it around
'Activecell.value = FindString
'this would be the same as paste. so you have copied and
'pasted without using the clipboard by creating a variable,
'assign it a value then assinged the value to another
'cell.
FindString = Activecell.value
Windows("Updated Consumer Acc List2005_02_15.xls").Activate
'FindString = InputBox("Enter a Search value")
Cells.Find(What:=FindString, After:=ActiveCell,
LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False).Activate
-----Original Message-----
I have the following set up to copy data from one workbook, search for that
data in another workbook (I'm not replacing the data, but moving over 2 cells
and continuing...)
I'd like for it to search for the data without requiring my to paste into
Find/Replace. Is this possible?

Dim FindString As String
Dim Rng As Range


Application.CutCopyMode = False
Selection.Copy
Windows("Updated Consumer Acc List 2005_02_15.xls").Activate

FindString = InputBox("Enter a Search value")
Cells.Find(What:=FindString, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart,
Click to expand...
SearchOrder:=xlByRows, SearchDirection:= _
 
hi tom,
yes it does... i think. I think she's pasting into the
input box.
 
If thats what's being done then

Dim FindString as String
FindString = Activecell.Text
Windows("Updated Consumer Acc List 2005_02_15.xls").Activate
Cells.Find(What:=FindString, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
_
xlNext, MatchCase:=False).Activate
 
YAY! Thanks guys. That eliminates me having to paste the data with each
search.

Thanks again.
 
hi again,
that is about what i told her in my post to her. with an
explination about using varibles to move data instead of
using the clipboard and copy/paste.
-----Original Message-----
If thats what's being done then

Dim FindString as String
FindString = Activecell.Text
Windows("Updated Consumer Acc List 2005_02_15.xls").Activate
Cells.Find(What:=FindString, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart,
Click to expand...
SearchOrder:=xlByRows, SearchDirection:=
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Placing the MsgBox "No ID found" statement in my code?? 12
Code does not error but no copy either 4
Data Cleanse Macro Running Very Slow 0
On error goto specified Line 6
Do until loop 6
Getting Excel Find to Default to Searching Workbook not Sheet 3

Back
Top