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.
RWayneHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
0
Rory ArchibaldCommented:
For example:

    Dim vRow
    vRow = Application.match("TP01.01", Sheets("Sheet1").Range("A:A"), 0)
    If Not IsError(vRow) Then
        If testresult Then
            Sheets("Sheet1").Cells(vRow, "H").Value = "Passed"
        Else
            Sheets("Sheet1").Cells(vRow, "H").Value = "Filed"
        End If
    End If

Open in new window

0
RWayneHAuthor Commented:
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?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Rgonzo1971Commented:
Hi,

pls try
    Sheets("Sheet1").Activate
    foundRow = Range("A:A").Find(What:="TP01.01", LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    With Range("H" & foundRow)
        If TestResult Then
            .Value = "Passed"
            .Interior.Color = vbGreen
        Else
            .Value = "Failed"
            .Interior.Color = vbRed
        End If
    End With

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
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?
0
Rory ArchibaldCommented:
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.
0
RWayneHAuthor Commented:
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.
0
Rory ArchibaldCommented:
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

0
RWayneHAuthor Commented:
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

0
Rory ArchibaldCommented:
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).
0
RWayneHAuthor Commented:
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.
0
Rory ArchibaldCommented:
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
0
RWayneHAuthor Commented:
Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.