I wish to have my macro copy a cell from a tab, then go to another tab and search specifically for the value of the initial cell.

Tags:
Excel 2007
Excel 2007 macros
Excel functions
Excel VB code
Hi.  I wish to have my macro copy a cell from a tab, then go to another tab and search specifically for the value of the initial cell.  i.e. the VB code should allow the "Cells.Find(What:=) to be VARIABLE based on what is pasted.  Any suggestions?

Software/Hardware used:
Microsoft Excel 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DoneThat
    To "get:" the data, you need to (1) select the cell and (2) move the data to a working variable such as strStringToFind used below. That's pretty basic. The code below will allow you to search for that string in any open workbook/worksheet - all variable controlled. Details are: Input parms: pfunFindValueOfString is the string you wish to search for. intOffsetRows & intOffsetCols are the cell you wish to return as offset from the found cell. strDefaultValue is the string you wish to return if the find fails. Vars used inside the function: Every var beginning with "p" is Public - i.e. established outside of the function. pstrClientWB is the name of the currently open workbook you want to search. pstrCurrentWorksheet is the name of the worksheet you want to search. The next few values parameterize the Excel Find function and are well documented. I use typical "search for values, the whole cell must match, search left to right then down, not case sensitive". Finally I return a cell offset from the found cell. Usage: strTemp = pfunFindValueOfString(strTemp2, pintcon0, pintcon1, pstrconNull) strTemp will contain the value in the cell to the right of the found cell using the search string in strTemp2 OR null (pstrconNull).
    '  String VALUE finder.
    
    '  Find the value associated with the string in Parm 1.
    
    Public Function pfunFindValueOfString(strStringToFind As String, intOffsetRows As Integer, intOffsetCols As Integer, strDefaultValue As String) As String
        
        pfunFindValueOfString = ""
        
        On Error Resume Next
            With Workbooks(pstrClientWB).Worksheets(pstrCurrentWorksheet).Range("a1:z300")
                    pfunFindValueOfString = .Find(What:=strStringToFind, After:=.Cells(1, 1), _
                                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, MatchCase:=False).Offset(intOffsetRows, intOffsetCols)
            End With
        On Error GoTo 0
            
        If pfunFindValueOfString = "" Then
            pfunFindValueOfString = strDefaultValue
        End If
        
    End Function
    
    Hope this helps, Gary
    830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following