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,
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Excel Get A if B And C ...Loop
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
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
http://www.tek-tips.com/faqs.cfm?fid=4114
that may help get you started
zach
RE: Excel Get A if B And C ...Loop
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
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
I have used the fruit example, merely as an example. I myself am not a fruit
Ted
It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.
RE: Excel Get A if B And C ...Loop
It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.