Avatar of RWayneH
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
RWayneH

8/22/2022 - Mon
Rgonzo1971

Hi,

pls try

    Sheets("Sheet1").Activate
    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

Regards
SOLUTION
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
or
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
RWayneH

ASKER
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?
ASKER CERTIFIED SOLUTION
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.
RWayneH

ASKER
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.
fblack61
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.
RWayneH

ASKER
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"
    Else
        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;"""
        .Open
    End With
    cn.Execute "UPDATE [Sheet1$] SET [F8] = '" & strStatus & "' WHERE [Field1] = 'TP01.01'"
    cn.Close
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.
RWayneH

ASKER
Cool!!  

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"
    Sheets("TP01-TestPlan").Activate
    '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
        Else
            .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

    ActiveWorkbook.Save
    ActiveWorkbook.Close

'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).
RWayneH

ASKER
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
RWayneH

ASKER
Thanks for the help.