Avatar of RWayneH
Flag for United States of America asked on

Find value and offset a different value somewhere else.

In VBA how would I goto Sheet1, do a find task TP01.01 (which will be there and in column A).  When it is found, goto column H and put in a "Passed". (or Failed).  I have a variable called TestResult, if TestResult = True Then "Passed" and if TestResult = False Then "Failed".  I believe an If Else would apply here but not sure how to frame it.
Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon


pls try

    foundRow = Range("A:A").Find(What:="TP01.01", LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    Range("H" & foundRow) = IIf(TestResult, "Passed", "Failed")

Open in new window

Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Small issue, I wanted to background chg the cell in H to green for passed and red for failed.  How do I do that if the cell is not selected?
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

I added to this sub the opening and closing of a workbook.  Is there a way to write to a sheet without it being open?  It is working great!! thanks, but I do not feel that opening and closing a workbook each time I need to write a value to it is going to be a good thing.  I suppose I could leave the file open?  Just wondering if there is a way to leave it closed and still get the Passed and green background to it the target cell?
Your help has saved me hundreds of hours of internet surfing.
Rory Archibald

You could add conditional formatting to the sheet so that it automatically colours based on Passed or Failed, then the code can update the values without worrying about the colours. It's easier to have the workbook open, but it is possible to query and update a closed workbook using ADO.

Hmm is writing to a closed workbook an involved process?  This may work will for my procedure.  This is probably another post, but it pertains to how I am using it.  Yes I did not even think about the conditional formatting.. thanks for reminding me.
Rory Archibald

Not particularly involved, no. For instance:

Sub UpdateData()
' Sample demonstrating how to update a worksheet in a closed workbook
    Dim cn                    As Object
    Dim strStatus             As String

    If TestResult Then
        strStatus = "Passed"
        strStatus = "Failed"
    End If
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=C:\path to file\workbook name.xlsx;" & _
                            "Extended Properties=""Excel 12.0 Xml;HDR=No;"""
    End With
    cn.Execute "UPDATE [Sheet1$] SET [F8] = '" & strStatus & "' WHERE [Field1] = 'TP01.01'"
End Sub

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.


Hmm, I believe I follow this.. but the find/match part is not in this correct?  I would just add that above Ln6 right?   Trying to read the following.

 cn.Execute "UPDATE [Sheet1$] SET [F8] = '" & strStatus & "' WHERE [Field1] = 'TP01.01'"

Here is what I landed on that is working pretty well, thanks for the help, however I am not connecting the dots on how it knows where to place the Passed/Fail result.  Unless that part is not in your example.  Could you comment the code a little so I can see how I need to edit this and remove the file open, save and close?

Sub CheckForSalesOrderSucess()
'Grab newly created SO Number from SAP and assign it to SalesOrderNum variable
'This is needed to take SAP session from QTP and give it to VBA
Application.ScreenUpdating = False
sessChoice = "QEC"  'variables used by TESTSAP function
connChoice = "900"
If TestSAP = False Then Exit Sub  'No End If when Then has something

SalesOrderNumCreateResult = "Failed" 'start with value of Failed
'See if MsgBar in SAP has "has been saved" in it
If InStr(1, session.findById("wnd[0]/sbar").Text, "has been saved") Then
    SalesOrderNum = Mid(session.findById("wnd[0]/sbar").Text, 14, 9) 'grab 9 chars beginning at char 14
    SalesOrderNumCreateResult = "Passed" 'set value
    Workbooks.Open Filename:= _
        "C:\QTP\TestingScenarios\ThirdParty\TP01 - Customer Change - New Catalog Code\Third party Scenario 1a Results.xls"
    'Define foundRow
    foundRow = Range("A:A").Find(What:="TP01.15", LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    'use foundRow to place Passed/Failed in column F
    With Range("F" & foundRow)
        If SalesOrderNumCreateResult = "Passed" Then
            .Value = "Passed"
            .Interior.Color = vbGreen
            .Value = "Failed"
            .Interior.Color = vbRed
        End If
    End With

        'Go populate TestPlan with Create SalesOrderResult.
End If  'SalesOrderNum NOW equals newly created SalesOrder number, begins at 14 char and grabs 9


'VBA had the active SAP session, this throws it back to QTP
AppActivate ("QuickTest Professional - [C:\QTP\TestingScenarios\ThirdParty\TP01 - Customer Change - New Catalog Code]")

End Sub

Open in new window

Rory Archibald

There's actually a typo there - it should be [F1] rather than [Field1]:

cn.Execute "UPDATE [Sheet1$] SET [F8] = '" & strStatus & "' WHERE [F1] = 'TP01.01'"

Open in new window

This will update column 8 (i.e. column H) where column 1 (i.e. col A) is equal to 'TP01.01' so you don't need the Application.Match code any more (you can't use it on closed workbooks anyway).

ah so F1 is for Col A and F8 is for Col H...   Sheet1$ is the sheet tab name.  I am getting it..  This looks as if it is Excel version dependent?  (Provider and Connection String)  Would those need to chg based on the ver of ExceL...   we are moving to Office365 soon.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rory Archibald

Nope - Provider would remain the same. You would only need to alter the Extended Properties part if you were accessing a file format other than .xlsx

Thanks for the help.