×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Get A if B And C ...Loop

Excel Get A if B And C ...Loop

Excel Get A if B And C ...Loop

(OP)
I have scanned through the forum discussions and examples, but none exactly clearly show a way to get Excel data using eb macro for what I need. I will keep looking through links I have already visited to see if there is something there that would assist me in solving this problem.

What I need to do is go to excel with a string I obtained through the terminal and find the corresponding string in an Excel column. If it finds it, it has to check that the corresponding cell matches a specific criteria. Both must match, else the loop will continue to the next match if any, then return the value. Scan B, if B and C get A, else next B.

Any related forum links with a possible explanation as to how I can apply it to this query, would be very much appreciated.

Ted,

RE: Excel Get A if B And C ...Loop




Hi,

What code have you tried so far?  Where are you stuck?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Excel Get A if B And C ...Loop

(OP)
Hi Skip,

I will tell you that what I attempted, which I deleted when things went weird, behaved strangely. It compiled with no errors, but when I ran it, it did nothing; actually what it did do was change the name of the macro from ebexcleb.ebm to ebex~~.ebm????? I wanted to show you some effort on my part, but was too taken aback by what occurred to try again. So I went looking around the Forum and found a post you commented on, on October 8, 2009 @ 8:28PM. Data from Excel to Extra and back  In it you provided a template that got me to come up with this...

CODE

Sub EB2Excel2EB()

    Dim Excel, ExclWorkbook As Object

    
    Set Excel=GetObject("Excel.Applicaton")
    Set System=CreateObject("Extra.System")
    Set MySess=System.ActiveSession
    Set MyTerminal=MySess.Screen
    
    A=MyTeminal.GetString(01, 02, 03)
    b=MyTeminal.GetString(03, 02, 01)
    c=Inputbox("Cee")

    With Worksheets("Worksheet1")

       For x = 1 To ActiveSheet.Rows.Count

           If A = "" Then Exit Sub
        
           Do
        
               If C = "" Then
               
               Else
        
               End If
        
        
           Loop

       Next x

    End With

I am more than ready to work out the logic, as that is where most of the fun comes from, but I need to feel confident, through some success before I attempt it again. Is there a simple EB code to just:
A) Check to see if the file (ex. "C:\Temp\WB1.xls") is open
B) Open File if it isn't open
C) Locate ("WS1")
D) Get the String from cell (B1)
E) EB MsgBox String

?



 

RE: Excel Get A if B And C ...Loop

(OP)
Hello again,

I need someone to help me make sense out my poor logic. What I will present is not achieving the desired result.

Assume if you will, there are two cols, one listing fruit
and the other listing corresponding names.
Col"a"              Col"b"
______              ______
Apple               Micheal
Apple               John
Banana              Jennifer
Banana              David
Banana              Alice
Cherry              Mark
Kiwi                Jose
Kiwi                Maria
Kiwi                Alex
Kiwi                Kerry
Mango               Micheal
Orange              Joshua
Orange              Veronica
------              ------

Now I would like two list boxes in one or two dialog boxes.
The first one will list the fruit of choice, but only one of
each kind. If the user selects the fruit from the list, the names
corresponding to the selected fruit would show in the alternate list. The user can then choose which person to get the fruit that is being offered. If the user selects "Kiwi" from the first
list box, the the second will show the names "Jose", "Maria", "Alex", "Kerry". Then the user selects "Maria", and perhaps a msgbox will show "Maria to get a Kiwi"

Here's what I have but it's not getting me anywhere. Can you show me some pointers, or where my logic is going wrong?

CODE

Sub Main

        Dim obj as object

        Dim objWorkbook as object
    
    Dim rw as LONG, FirstRow as long, LastRow as long

    Dim Fruit() As String, Name1() As String, NewFruit() As String

        Set obj=CreateObject("Excel.Application")

        Set obj = Getobject("C:\Temp\Snack.xls")

    set objWorkbook=obj.Worksheets("Sheet2")

        ReDim Fruit(30)

        ReDim Name1(30)

        ReDim NewFruit(20)

        CountA=3

        NewCnt=0

       With objWorkbook
              with .Range(.["A" + CountA], .["A" + CountA].End(xldown))
                FirstRow = .row
                Last Row = .rows.count + FirstRow - 1
              end with
              for rw = FirstRow to LastRow
                Fruit(CountA)=Trim(objWorkBook.Range("A" + CountA))
                Name1(CountA)=Trim(objWorkBook.Range("C" + CountA))
                row = row + 1  
            If Fruit(CountA) <> Fruit(CountA-1) then

                Fruit(CountA)=NewFruit(NewCnt)

                NewCnt=NewCnt+1

            End if
            CountA=CountA+1
              next
       End With

NewFruit(y) = NewFruit(y) + CHR(9)

Name1(x) = Name1(x) & CHR(9)

    Begin Dialog dlgOptions 154, 11, 182, 188, "Third Language Action Dialog"

       OkButton  130, 6, 50, 14, .btnOK

       CancelButton  130, 23, 50, 14, .btnCancel

       Text  48, 37, 70, 10, "Fruits"

       DropComboBox  46, 112, 73, 40, NewFruit(CountA), .CB1

       Text  52, 100, 70, 10, "People"

       DropComboBox  49, 54, 73, 40, Name1(y), .CB2

    End Dialog

    Dim dlgVar as dlgOptions

    Dialog dlgVar

    If Error=102 then
    Stop
    End If

    msgbox Name1

End Sub

RE: Excel Get A if B And C ...Loop

(OP)
Hi again,
I have used the fruit example, merely as an example. I myself am not a fruit clown What I have been trying to work out, unsuccessfully thus far, is a way to send a request to individuals that specialize in performing certain functions. In some instances, there may be as many as 5 individuals (this number can change) who can perform one of many specific functions. I would need one initial list taken from an excel column for the functions, and another that would follow showing a list of individuals who can perform the selected specialty function. I wish I could have more time in my day to play with it and get it to work. The code provided, which is terrible I agree, was meant to give an idea of what I am attempting to achieve. I have tried many different ideas presented in the forum, but I get logic errors up the wazoo. Please, if there is anyone who can show me the light, I will strive to find my way.

Ted

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes. clown

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close