Excel Get A if B And C ...Loop

[tedb13]

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,

 

[SkipVought]

Hi,

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tedb13]

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...

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

?

 

[vzachin]

ted, here's another link for you

http://www.tek-tips.com/faqs.cfm?fid=4114

that may help get you started

zach

 

[tedb13]

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?

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

 

[tedb13]

Hi again,
I have used the fruit example, merely as an example. I myself am not a fruit ) 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. )

 

[tedb13]

New thread thread99-1638197 started in error. Please refer to it only to obtain more details provided for this request. Thank you.

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