VBA CODE HELP ...if you have time!!!

[salh90]

HEY ALL!!

I am new to VBA... I am connecting to an external application to pull data and place it in excel one row at a time then it moves on to the next row.

The code I am trying to write reads cell "E2" 's ID and pulls the data if there is no data in cell "E2" then it checks a different type of ID in cell "D2" and then pulls the data. Except the ID needs and identifier in front of it for the external application to read it properly (example..B900ON(D2 ID) ....Here is the code.


Dim rng As Range
Set rng = Range("E2")
If rng.Value("E2").Text <> "" Then Set rng = Range("D2") And DoEvents
If rng.Value("D2").Text Is Nothing Then GoTo gtNext:

End



The VBA error says Type Mismatch...



Thank you for your time and your help :) ..

[Paul_Hossler]

1. Welcome to the forum

2. If you use the [#] icon you can paste code between the CODE tags to make it easier to read

3. Not sure about your logic / intent, but rng is an Object that you had Set = the Cell E2. That way you can just refers to it's properties (.Value, .Text, etc.) directly




Dim rng As Range
Set rng = Range("E2")

If rng.Text <> "" Then
Set rng = Range("D2")
DoEvents ' probably not needed here

If rng.Text = "" Then
GoTo gtNext
End If

End If

[SamT]

The AND keyword applies to true and false statements

IF X /<> Y And A <= B Then. . .

[salh90]

Thank you Paul! :) .. It worked but then...

I ran your code and it skips setting "E2" as an object first..... not sure why. Also I have to modify the ID for cell D2. It will not pull the data unless it has iagi <autotabs> 7560on PLACED then rng.text PLACED then <enter>... the ID in cell E2 can be read without the extra labeling... Can I insert this in your code as I did below or does it have to be inputted as a strInput




Dim rng As Range
Set rng = Range("E2")

If rng.Text <> "" Then
Set rng = Range("D2")
DoEvents ' probably not needed here

If rng.Text = "" Then
GoTo gtNext
End If

End If

' Clear the screen
mSession.Screen.SendKeys ("<Pf3><Clear>")
mSession.Screen.WaitHostQuiet (waitTime)

If rng = Range("D2") = 10 Then mSession.Screen.SendKeys ("<Home>iagi" & rng.Text & "<Enter>")
End

If rng.Text = "" Then
GoTo gtNext
End If

End

[salh90]

My intent is for the macro to check E2 for an account number... If there is not account number in the cell then check for a secondary acct ID in cell D2......So you can universally paste an acct number or acct id and it will pull data with either value.

The account number is stand alone and can be used to pull the data.

The account ID needs the label 3560ai placed in front of it to be read in the external application.



it seems like it just takes a complex if then statements to run... but I can't figure out where to place the 3560ai in the code to be read without the first Set rng = Range("E2") picking it up and trying to use the tag.

[Paul_Hossler]

Reading this in words ...



Dim rng As Range
Set rng = Range("E2")

If rng.Text <> "" Then
Set rng = Range("D2")
DoEvents ' probably not needed here

If rng.Text = "" Then
GoTo gtNext
End If

End If

' Clear the screen
mSession.Screen.SendKeys ("<Pf3><Clear>")
mSession.Screen.WaitHostQuiet (waitTime)

If rng = Range("D2") = 10 Then mSession.Screen.SendKeys ("<Home>iagi" & rng.Text & "<Enter>")
End

If rng.Text = "" Then
GoTo gtNext
End If

End


1. Set variable rng to the range object Cells E2 on the active sheet

2. If the .Text property in E2 is NOT an empty string, then

3. Set variable rng to the range object Cells D2 on the active sheet

4. If the .Text property in D2 is NOT an empty string, then go to the label gtNext

So I think that it is setting rng to E2, but then sets rng to D2 right away

This usually happens because the cell is not really empty, but only looks empty

Try



If Len(Trim(rng.Text)) > 0 Then



5. This really doesn't make any sense



If rng = Range("D2") = 10 Then mSession.Screen.SendKeys ("<Home>iagi" & rng.Text & "<Enter>")


I'm guessing that you wanted this under the assumption that rng is set to D2



If rng.Value = 10 Then mSession.Screen.SendKeys ("<Home>iagi" & rng.Text & "<Enter>")

[Paul_Hossler]

Possibly you're over complicating it

This is what I heard your logic to be, turned into VBA code fragments




Option Explicit

'My intent is for the macro to check E2 for an account number
'... If there is not account number in the cell then check for a secondary acct ID in cell D2
'......So you can universally paste an acct number or acct id and it will pull data with either value.
'The account number is stand alone and can be used to pull the data.
'The account ID needs the label 3560ai placed in front of it to be read in the external application

Sub test()

Dim sAccount As String


'stuff


If Len(Trim(Range("E2").Value)) > 0 Then
sAccount = "356ai" & Range("E2").Value
ElseIf Len(Trim(Range("D2").Value)) > 0 Then
sAccount = "356ai" & Range("D2").Value
Else
GoTo gtNext
End If

' Clear the screen
mSession.Screen.SendKeys ("<Pf3><Clear>")
mSession.Screen.WaitHostQuiet (waitTime)
mSession.Screen.SendKeys ("<Home>iagi" & sAccount & "<Enter>") ' not sure

gtNext:

'stuff


End Sub

[SamT]

Paul, I probably read it wrong, but I think he wants

If Len(Trim(Range("E2").Value)) > 6 Then
sAccount = Mid(Range("e2"), 6)
ElseIf Len(Trim(Range("D2").Value)) > 0 Then
sAccount = Range("D2").Value
Else
GoTo gtNext
End If

[SamT]

I can't help it. I always consider speed, even in a snippet that is obviously not a bottleneck.


saccount = Trim(.Range("E2"))
TempAcct = Trim(.Range(D2"))

If Len(saccount) Then
sAccount = Mid(saccount, 6)
ElseIf Len(TempAcct) Then
saccount = TempAcct
Else: GoTo gtNext
End If

[salh90]

So on my side, after the ID from either E2 or D2 is read based on whether the value is present, I want to select that specific ID and turn that selected ID into a rng.txt or equivalent...this rng.txt will be used to pull data from the external application then placed into specific designated columns. this is an example

Imagine a spreadsheet:

(D2);(E2) ;(F2) ; (G2)
Acct ID; Acct Num; Related Accts;Create Date

'PullAcctID
mSession.Screen.SendKeys ("<Pf3><Clear>")
mSession.Screen.WaitHostQuiet (waitTime)
mSession.Screen.SendKeys ("<Home>iagi" & rng.Text & "<Enter>") so this line is typing on the application "iagi" and using the acct number from E2 (rng.txt.) this was set at the beginning of the original script then places the trimmed data from the application in cell D2 with the rng.offset line below

mSession.Screen.WaitHostQuiet (waitTime)
mSession.Screen.SendKeys ("<Home>" & "<Enter>")
mSession.Screen.WaitHostQuiet (waitTime)
rng.Offset(0, -1).Value = (Trim(mSession.Screen.GetString(14, 14, 11)))



So, there are several of these "rules" that use the ID as string to refer to several "screens" ..............the code below bridged the gap between setting the range and then turning that into an object

Dim rng As Range
Set rng = Range("E2")


mSession.Screen.SendKeys ("<Pf3><Clear>")
mSession.Screen.WaitHostQuiet (waitTime)

While (rng.Text <> "")

If rng.Offset(0, 1).Text <> "" Then GoTo gtNext

Call ActiveWindow.ScrollIntoView(rng.Left, rng.Top, rng.Width, rng.Height)
rng.Select
DoEvents

[SamT]

Paul?

I'm completely confused.