?
Solved

VBA macro: correct FIND code

Posted on 2011-10-17
6
Medium Priority
?
223 Views
Last Modified: 2012-06-21
Hello!
What is the correct FIND code [Cells.Find(What:="...]  if I want to do a search in one worksheet for a value which always stands at the same place in another worksheet (where I regularly paste new values). Thanks for your help from Zurich!
Karl

PS: I have no clue how the point allocation works - so I guessed my question is not that difficult... :-)
0
Comment
Question by:CandidKarlito
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:clamps
ID: 36978864
Hey Karl,

can you tell me, what you want to do with the Data you want to find?
I made an example which replaces all found occurrences in a range in worksheet 1 by a value in worksheet 2, cell A1
Dim tempString As String
tempString = Worksheets(2).Cells(1, 1)


With Worksheets(1).Range("a1:a500")
    Set findings = .Find(tempString, LookIn:=xlValues)
    If Not findings Is Nothing Then
        firstOccurrence = findings.Address
        Do
            findings.Value = 5
            Set findings = .FindNext(findings)
        Loop While Not findings Is Nothing And findings.Address <> firstOccurrence
    End If
End With

Open in new window

0
 

Author Comment

by:CandidKarlito
ID: 36979145
Hi Clamps! Thank you for this good macro. As for your question: I actually just need to get this worksheet 2, cell A1 value into the find command so the cursor jumps to that corresponding field in worksheet 1 - and I can take it from there (in my clumsy programming...).
Thanks for your time!
Karl
 
0
 
LVL 3

Expert Comment

by:clamps
ID: 36979175
Hi Karl,

if you just need to "jump" in the field in Worksheet 1 you can do this:

tempString = Worksheets(2).cells(1,1)
'this puts the Value from Worksheet 2, Cell A1 in the variable tempString

With Worksheets(1).Range("a1:a500")
    Set findings = .Find(tempString, LookIn:=xlValues)
    If Not findings Is Nothing Then
        findings.Select
    End If
End With
'this looks in the Range A1:A500 (adjust like needed) for the value in tempString and selects that cell...

shorter but the same is the following:
With Worksheets(1).Range("a1:a500")
    Set findings = .Find(Worksheets(2).Cells(1, 1), LookIn:=xlValues)
    If Not findings Is Nothing Then
        findings.Select
    End If
End With

Cheers
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:CandidKarlito
ID: 36980078
Liked your 'short' solution - tks! Ran the otherwise empty macro with your bit (I copied it exactly as it was); however, it stopped at "findings. Select".  Hmm.
Well,I call my sheets "xxx" [=(worksheets(2)] and "tptp" [=(worksheets(1). How does your code look with these sheet names inserted?

Your help is much appreciated.

Karl
0
 
LVL 3

Accepted Solution

by:
clamps earned 375 total points
ID: 36980932
Hey Karl,

if you give me a little more information on what you plan to do, I can assist you better :-)
Your question about the Worksheet names is done like this:

With Worksheets("tptp").Range("a1:a500")
    Set findings = .Find(Worksheets("xxx").Cells(1, 1), LookIn:=xlValues)
    If Not findings Is Nothing Then
        findings.Select
    End If
End With

all this does is select the first occurrence of the value you're searching for.
if you want to change the contents of that cell or color it or whatever, you have to work from there...
and if you want to work with all occurrences of the searched value you will have to do the loop from my first post.
Let me know if I can help you.
Cheers
0
 

Author Closing Comment

by:CandidKarlito
ID: 36992246
Thank you very much for your help!
It works now. Backed by your variants, I set my macro as follows:

Sheets("tptp").Select
 
tempString = Worksheets("xxx").Cells(1, 1)

With Worksheets("tptp").Range("a1:a500")
    Set findings = .Find(tempString, LookIn:=xlValues)
    If Not findings Is Nothing Then
        findings.Select
    End If
End With
   
ActiveCell.EntireRow.Select
' ... and I could take it from here ...    

Best regards
Karl
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question