NEW TO VBA...Need Help..driving me nuts

[salh90]

.. Is how I feel trying to write this code.




My intent is for the macro to check cell E3 for an account number(16 digits)... If there is not account number in the cell then check for a secondary acct ID in cell D3(fewer digits)......So you can universally paste an account number in E3 or acct id in D3 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("E3") picking it up and trying to use the tag.



So on my side, after the ID from either E3 or D3 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...this rng.txt object will be used to pull data from the external application then placed into specific designated columns. this is an example

So the macro goes into an program and basically copies and pastes several pieces of data in specific cells.

This is part of the actual code.

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

[SamT]

dim AcctExists As boolean

with Sheets("sheet1") 'Adjust as needed

'Check if 16 digit account number exists
if len(.Range("E3")) = 16 then 'AcctNumber exists
AcctNum = .Range("E3")
AcctExists = true

'Otherwise check if Acct ID Exists
ElseIf .Range"D3") <> "" then
AcctID = "3560ai" & .Range("D3")
AcctExists = true

else 'No Acct Number or AcctID exists
AcctExists = false
end if
end with

[salh90]

Thanks Sam!!! How do I set these as an object to be referred to later... as a rng.txt object?

[Paul_Hossler]

Probably something like

Dim AcctExists As Boolean 
Dim rngAccount as Range
 
With Sheets("sheet1") 'Adjust as needed
     
     'Check if 16 digit account number exists
    If len(.Range("E3")) = 16 Then 'AcctNumber exists
        Set rngAccount = .Range("E3")
        AcctNum = .Range("E3") 
        AcctExists = True 
         
         'Otherwise check if Acct ID Exists
    ElseIf .Range"D3") <> "" Then 
        Set rngAccount = .Range("D3")
        AcctID = "3560ai" & .Range("D3") 
        AcctExists = True 
         
    Else 'No Acct Number or AcctID exists
        AcctExists = False 
    End If 
End With

[SamT]

What is a rng.txt object?

The code has three variables that are Public and wide scoped and can be used anywhere.

Do you understand how to declare and use public variables?

[salh90]

SamT I am not exactly sure. I am very new to VBA.
I think part of the issue is I am not giving you a good enough picture... maybe this will help.

There is other code before this that sets the external Application up for use, but does not affect the rest of the code you see below.
---------------------------------------------------------------------


Dim tmpStr As String <<<<IDK What this is doing..... since it is not refering to any specific cell. Maybe taking the ACCT NUM IN CELL E3 and referring to it as a string of text maybe....



Dim submitScreen As Boolean
Dim waitTime As Integer
waitTime = 100


Dim rng As Range
Set rng = Range("E3") I THINK THIS IS COPYING OR SELECT THE CELL THE ACCOUNT NUMBER IS IN OR TURNING IT INTO A TEXT OBJECT TO REFER TO IT LATER


mSession.Screen.SendKeys ("<Pf3><Clear>") <<THIS IS CLEARING THE SCREEN IN THE EXTERNAL APP...
mSession.Screen.WaitHostQuiet (waitTime) <<WAIT FOR THE PROGRAM TO FINISH CLEARING THE SCREEN OR ILL GET A TIMEOUT ERROR

While (rng.Text <> "") <<<THIS IS THE RNG.TXT I AM REFERRING TO...THEN YOU CAN SEE THAT EVERY LINE REFERS BACK TO rng.offset. Then it goes into the external APP, pulls the data, determines the cell location with rng.offset, then the type of information it puts in that specific cell.


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




rng.Select
DoEvents

mSession.Screen.SendKeys ("<Home>wagn " & rng.Text & "<Enter>") THIS IS BEING DONE IN AN DIFFERENT PROGRAM.
mSession.Screen.WaitHostQuiet (waitTime)




'Related
rng.Offset(0, 1).Formula = " "

'CreateDate
rng.Offset(0, 2).Value = Date
'Source
rng.Offset(0, 3).Value = " "
'Owner
rng.Offset(0, 4).Value = "Tasha"

[salh90]

YUS!! so I can replace rngAccount.txt with my old rng.txt but I am not getting it to pick up the D2 rule..it just stops executing with no error message

[SamT]

Dim tmpStr As String

"Dim" = Declare or create. Tells the Compiler to mark a place in RAM to hold some kind of data.
"tmpStr" = a variable that can hold data or information. The name or address of the memory (RAM)
"As String" = the type of data or information the preceding variable (tmpStr) can hold. Tells the Compiler how much Memory to mark for that spot of memory.

You need someplace in your code to hold Data until you ca do something with the data. "Dim tmpStr As String" does that. The Name tmp+Str tells us that it is for temporary use and it is for String Data. Note that this is backwards from one of the standard naming conventions: "strTmp."

A String is any group of characters found on the keyboard from ~ to / including numbers and the space bar.

Numerical data is held in variables of Types Long, Integer, Double, and Single with Longs and Doubles the most common. Longs and Integers can't hold decimal numbers and Integers are limited to from -32K to +32K.

Other types of common Data are Dates.

MS office applications, including Excel are Object Oriented Programs. In Excel, everything is an object, Ranges, Worksheets, Workbooks, Charts, Rows, Columns, and Cells. Typical Variables name to hold these Objects are Rng, WkSht, WkBk, Cht, Rw, Col, and Cel, or some variation thereof.

A Variable name can be any group of characters that is not already the name of something else, but can not start with a numerical character. "Range1" is ok but "Rng" is not. Sheet1, Sheet2, Sheet3, etc are not allowed because these are already dedicated to Worksheet Names.

Set rng = Range("E3")

Because a Range is an Object the variable must be "Set" rather thanjust being made equal to. This is true for all Object type variables and their associated Objects.

Dim WkSht As Worksheet
Set WkSht =Sheets("Sheet1")

While (rng.Text <> "")

Says: While Rage("E3").Text is not empty. "Text" is a particular type of value that a Cell can have. It is best to not use the Text Property of Ranges, unless absolutely necessary.

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

rng.Offset(0, 1) means the cell 0 Rows up or down and 1 column to the right of cell E3. This would be Range("D3"). It says that if Range("D3") is empty then the code shoud continue executing at the line of code(Label) that reads "gtNext."

Note that negative Row offsets move up, positive Row Offsets move down, negative Column Offsets move left and positive ones move right, all by the number used. Offset(0, 4)or Offset(, 4) moves 4 cells to the right and Offset(-3, 0) or Offset(-3) moves 3 cells up. Pay attention to the commas in those 4 examples.

Until rng is Set to another Range than cell E3, you can always read it as "Range("E3")." For example (Not in your code.)

rng = 42

Means that Range("E3") on the Worksheet now has the number 42 in it


You can get help on any VBA, Excel, or other MS application keyword while in the VBA editor by placing the cursor inside the word and pressing F1.