Login  | |
|
 |
Help with a VBS Problem, please? - 5/13/2008 5:13:42 AM
|
|
 |
|
| |
alcornj
Posts: 4
Score: 0
Joined: 5/13/2008
Status: offline
|
Hello all, my name is Josh and I'm a java/c++ programmer and know literally nothing about vb/vbs and decided to try my luck at writing a custom script for a work-related excel sheet. Brevity isn't quite my thing but I promise I'll try my best when I explain:
I've got a spreadsheet set up to keep track of data, in this case whenever an employee comes in late I type the date into a cell. At the bottom of the list is a box that keeps a running total of how many boxes I've filled. Im facing two problems with it and I have literally no idea what to do, and I can't seem to find anything for vb that works similar to javadoc insofar as explaining vb functions, etc. Heres the problem. If I type data into consecutive cells, my script works just fine. The table is set up so cells c11 through c44 are looked at for dates. If I start at c11 and don't skip any cells then thats fine. However if I put data into c11, c12, and c14, for example, it will look at c11 and c12 and count 2 cells, but will ignore c14. What I want is to loop through each cell FROM c11 TO c44, and if its NOT empty update my count by 1, otherwise update it by 0. In theory if I were to put data into every odd cell, c11, c13, c15, c17, etc I want it to find and return 4 cells. Instead, its finding 1, in c11, and ignoring everything else.
Next, there are four "sets" of data I want to track. Colum C counts lates, Column D counts absences, and I use E and F as well. I need to do this four times on my page, one for each set. All colums use rows 11 through 44. Can anybody tell me how to get it to work how I want? I don't even know excel enough to know if theres some other way to do it and I don't know if I can use c/c++ or even java to write it instead? Thanks. Here is my code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x As Integer
Dim iCount As Integer
iCount = 0
' Set numrows = number of rows of data.
NumRows = Range("C11", Range("C44").End(xlUp)).Rows.Count
'NumRows = Range("C11", Range("C44").End(xlDown)).Rows.Count
' Select cell C11.
Range("C11").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
If Not IsEmpty(ActiveCell) Then
Application.EnableEvents = False
iCount = iCount + 1
Application.EnableEvents = True
'Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
' MsgBox "Cell" & ActiveCell.Address
Else
iCount = iCount + 0
' MsgBox "Cell" & ActiveCell.Address
End If
Next
Range("C46").Value = iCount
End Sub
< Message edited by alcornj -- 5/13/2008 5:16:05 AM >
|
|
| |
|
|
|
|
RE: Help with a VBS Problem, please? - 5/16/2008 10:36:44 AM
|
|
|
|
| |
TNO
Posts: 1064
Score: 10
Joined: 12/18/2004
From: thenewobjective.com
Status: offline
|
I don't think VBA is needed for this case, the spreadsheet formulas should be plenty. Go to insert formula and look at the COUNT and COUNTIF functions. play with them a little and some others and you can get on the right track pretty fast. VBA is overkill in many instances IMO (I'm not counting Macros, that's a different story). If confused, the insert formula dialog lets you type in a description or a question to find something you need.
_____________________________
To iterate is human, to recurse divine. -- L. Peter Deutsch
|
|
| |
|
|
|
|
|
Printable Version
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread