Find return value from two subtracted assigned value of text string
Before I delve into the code, what I'm looking for is the number of times to use Tab key and Spacebar on the keyboard to check off a check box on a web form because not all will have all four example categories as listed below. Time is an issue, and I do not want to create 4 columns, one for each category and count each empty cell as a Tab, but condensed like the excel sheet below. There are 99 unique checkboxes to go thorugh, and usually no more than 10 will be checkbox'd off. I am unable to use `<input type="checkbox" .. >` in the HTML code because it is showing as `<input type="hidden" ..>` so I have to use the keyboard Tab to manually go from field to field, including check boxes.
To tab through all 99 values will take more than half an hour for each web entry, based on the 'If... Then...' statement. I created a lot of Sub to use the `Call subName` to perform various actions. I created a Sub to loop the action of pressing Tab key, so it looks like this `Call Tab(3)` to press the Tab key three times. My hope is to perform math to deduct assigned values to categories, like category Cat is assigned 2, and category Fish is assigned 6, 6-2 is 4, which mean `Call Tab(4)`. I have been spending a few weeks finding how I can perform the math to deduct the number of times to ` Call Tab(n)`. Here's what I'm attempting to do:
The Excel sheet is set up like this
Targeted Audience Type
.. AG AH AI AJ
1 Adult Mom Child Dad
2 Adult Mom Dad
3 Mom Child
The following is on an intranet web form. To check off an ordered checkboxes on a web form, the √ is a check mark in the checkbox:
Entry Row 1:
[√] Adult
[√] Mom
[√] Child
[√] Dad
Entry Row 2:
[√] Adult
[√] Mom
[ ] Child
[√] Dad
Entry Row 3:
[ ] Adult
[√] Mom
[√] Child
[ ] Dad
I need to be able to find out the number of Tabs it takes to get to the next item to check off. Using the example audience type above, from Adult to Dad is 3 items down the list, but from Child to Dad is 1 down. From Adult to Child is 2 down the list. The goal is to have VBA look at the String in ActiveCell, whether it is Adult or Mom, and then perform the math of how far down the list the next item is. It will be done in a loop with the For Loop If Then. The web form's check box list will never change/is constant.
There is a long list, 99 unique items. The math I'm thinking of is taking the position number (assigned value) it previously checked off last, say, it justed checked off Child, which is a assigned value of 3, and then the next string value has a string value of Dad, which is assigned value of 4. Subtract both numbers and it becomes 1.
I found a tutorial online, but it doesn't really tell me how to do math: http://www.homeandlearn.org/arrays_and_loops.html
Sub ArrayCat()
Dim myArray(ActiveCell) as string
Dim Adult as string
Dim Mom as string
Dim Child as string
Dim Dad as string
Dim n as Integer
Dim aNext as String
Dim previous as String
Dim I as Integer
Array("Adult") = 1
Array("Mom") = 2
Array("Child") = 3
Array("Dad") = 4
..
If myArray = "Dad" Then
' Do some math somewhere. One entry might have just Adult and Dad,
' The next netry will have Adult and Dad, and so on.
End If
' Here's my attempt to do subtraction in a code:
' ** Note, Call Tab(#) and checkOffBox is a sub I created to do how many
' times Ihit the Tab key and then check off the box. The # represents the
' return value of math performed (subtraction), and checkOffBox checks the
' web form's box.
aNext = ActiveCell ' how to match this cell value with the myArray?
previous = ActiveCell.Offset(0, -1).select ' matching to Array
n = next - previous ' performs math, next.value minus pervious.value
For i = 1 To IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).select
Call Tab(n) ' "aNext - previous" returns as value
Call checkOffBox
Next
End If
..
End Sub
The following two `Sub` is how my code is set up to take data from Excel and enter string values into intranet's web form. These data are things like title of agencies, phone numbers, mailling addres, website url, etc.
This is not best practice as I understand it; however, I make liberal use of `Application.SendKeys("does something"), True` to perform actions like copy, paste, write Strings into text box or select something from drop down menu, etc.
As I mentioned, I built several different Subs doing different things, such as execute complex actions of copying and pasting, tab from field to field, open drop down menus and select a value by using `SendKeys`, etc. The main purpose of the macro is to act as a person doing hundreds of computer shortcuts (CTRL+C, V; ALT+Down key; ALT+Tab, and so on). I turned the majority of these into `SendKeys`. Here's the main Sub calling different actions:
Sub repeating() ' Submit 5 excel entries' worth of data into the intranet web form
Call ccnyc(5)
Call beep ' to let me know it's done copying and pasting each entry into database
End Sub
Sub ccnyc(numCalls As Integer)
Dim I As Integer
If Not numCalls > 0 Then Exit Sub
For I = 1 To numCalls
Call getExcel
Call hide
' hides the previous row, so I don't lose track of what has
' already been pt into the database
Call copying
' copies and paste the name of Agency I found on the internet and put ' into Excel to be put into database via macro
Call wait(1)
Call aCopyingTwo(1) ' phone
' Note, this means skipping a field box which is attachment button. I
' tab twice to go into Phone field box
Call aCopying(7)
' aCopying involves If... Then, seeing if there's anything to
' copy in Excel's cell, and if there is, paste it, Else, it tabs to the next
' box to complete via macro.
Call dropdowns
' select two values (county and state) in two drop down menus
Call aCopying(10)
' more If... Then field boxes to copy and paste
Call wait(2)
Call tabb(1)
Call checkbox
' this sub is paused while I explain Call checkbox
The last line of code above `Call checkbox` is where I perform the check boxes.
Currently, some involve `If... Then`, but it can only do check off one value per category (Targeted Audience, Type of Organization, and type of Services). Reality is organizations serve more than one targeted audience, more than one type of organization (Faith-based and non-profit, for example), and provide multiple services (such as Food pantry, Child Support, Housing, etc).
As a result, it is going to involve math to find out how many empty checkboxes I will need to Tab (skip) through to the next check box to check. Using the `Call aCopying` Sub will take more than 30 minutes per form to complete; this amount of time spent to find out if the check box should be checked or not is not acceptable.
I am trying to find the best solution to this problem. I'll show some of the `Call checkbox` code I'm using after the main Sub ends.
' sub continues
Call EndEntry
' selecting a drop down value and enter any comments about the Org
Call submit
' save form
Next
End Sub
Some of the check box codes:
Sub checkbox()
Call aud
' Targeted Audience check box
Call threeBoxes
' Check off Type of Organization and Services provided
Call getExcel
Call tabnLeft(12)
' Finds the string value of the county it serves in Excel
Call findCnty ' If... Then, and tab/check
End Sub
The code below contains `' \\` which allows me to manually uncomment any category that applies to that organization. I tend to find a bunch of organizations that have exactly the same audience, type of organization, and services they provide. The best example is Low-Income Families, Business, and Housing, respectively. This way, I don't go back in into the code for every entry and comment/uncomment categories unique to the organization.
Ideally, and this is what I am looking to do with my question, I want to type in the cell within Excel the type of audience, organization type, and services, evaluate the text string for the assigned values with If/Then or other methods of coding. The web form is extremely un-user friendly due to the long check boxes. We can't type out the categories, and there's nothing we can do except to scroll or tab through the list and manually check them off.
The `Sub findCnty` code below is also exactly the same for `Sub aud` as well:
Sub findCnty()
Dim cnty As String
cnty = ActiveCell
If cnty = "Bronx" Then
Call getIE
Call tabbs(205) ' Bronx County
Call waitCheck 'checks the box
Call wait(25) ' neccessary delay time for computer to catch up
ElseIf cnty = "Richmond" Then
Call getIE
Call tabbs(1380) ' Richmond County
Call waitCheck
Call wait(90)
Call wait(10)
End if
End sub
Sub threeBoxes()
' This is the old way I did things to check each box I need. The '\\ comments out the action of checking the box and wait time for computer to catch up
Call tabs ' Advocacy
' \\ call waitcheck
Call tabs ' Business / For-profit Organization
Call waitCheck 'checking off Business in check box
Call tabs ' Community-based / Non-profit Service Organization
' \\ call waitCheck
.. other Org Types
End Sub
Please let me know if you need me to clarify on any points I made with this code. I have asked this question elsewhere, researched online for different methods of performing math to find the (n) value of two assigned values to a text string. If there is a guide on this, I would appreciate if you can point me to it. Please let me know if you need me to clarify any points I've made here. I'm learning VBA as I find need for each aspect of the macro and have been studying for about a year now.
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago