Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Find value and offset a different value somewhere else.

Posted on 2014-12-02
13
120 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 50

Expert Comment

by:Rgonzo1971
ID: 40475814
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
ID: 40475818
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
ID: 40476101
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 40476117
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
ID: 40476152
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
ID: 40476183
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
 

Author Comment

by:RWayneH
ID: 40476208
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
ID: 40476290
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
ID: 40476574
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
ID: 40478177
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
ID: 40478792
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
ID: 40478848
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
ID: 40483209
Thanks for the help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

860 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