Solved

Find value and offset a different value somewhere else.

Posted on 2014-12-02
13
112 Views
Last Modified: 2014-12-05
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.
0
Comment
Question by:RWayneH
  • 6
  • 5
  • 2
13 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 300 total points
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Closing Comment

by:RWayneH
Comment Utility
Thanks for the help.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now