Visual Basic :: Excel VBA - Finding The Total Values In A Range Or Column

Finding Total Range, Copy, And Sort
I have tried a bunch of code but nothing seems to work. I am moving data from one worksheet to another, sorting it, and then reporting on it. Everything works fine except I have to hardcode in the end of the range.
I am really new at this and figure there must be a better way. Please help. Thanks.

Here is my macro:

Code:
Sub Practice_Area()
'
' Practice_Area Macro
' Sort by Practice Area
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Worksheets("Data").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets("Sorter").Select
Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=9
Columns("N:O").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Worksheets("Sorter").Activate
ActiveCell.CurrentRegion.Select
'HERE IS WHERE I AM HARDCODING THE END OF THE RANGE
Range("A1:S261").Select
' Worksheet("Sorter").Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Application.Run "'Subcontractor Survey Report.XLS'!fill"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
ActiveWorkbook.Save
End Sub
Edit by moderator: You can use [vb][/vb] tags to format your code. See here for more about tags, or click 'Reply' on this post to see them in action.

RESOLVED: Finding A 'range' From Within A 'range'? - Excel VBA
Relative newbie...

Wanting to get coding that will perform a search through a spreasheet range to find a unique row of data (i.e. a match to the contents of first 3 columns), returning the row No. where the match is found. I had thought that a 'range.find' would accomplish this, but have since been advised otherwise.

Anyone any ideas????

Adjacent columns in the row are for additional pertinent data. The search will be used either to retreive the additional data, or, to add to or change it.

thnx in advance,
remalay

Edited by - Remalay on 8/22/2006 6:50:32 AM

Column Total In VB In Excel
I'm trying to get the total of a column, starting at E2 to the end, but this procedure will be run on files of varying lengths, so the number of rows needs to be a variable. I'm getting an
Run time error '1004', Application-defined or object-defined error.
Code:Sub AddTotalLine()

  Dim lngUsedRange As Long
  Dim lngLastLine As Long
  Dim lngDataLines As Long


  Sheets("PAYROLL REGISTER - BURDEN").Select
  lngUsedRange = ActiveSheet.UsedRange.Rows.Count
  lngLastLine = lngUsedRange + 1
  lngDataLines = lngUsedRange - 1
  Sheets("PAYROLL REGISTER - BURDEN").Cells(lngLastLine, 5).FormulaR1C1 = "=SUM(R[-lngDataLines]C:R[-1]C)"




End Sub

If I replace -lngDataLines, in the SUM, with -167 (the actual number needed for the test data I'm using) it works fine. I've tried using E2 for the first reference instead but it as a string instead of a cell reference. How can I fix this? Or is there a better way?

Finding Distinct Values In VsflexGrid Column
Hi,

Is there any function for vsflexgrid that i can retrieve only distincy
values frim a column,please if any one knows inform me Thanks in advance

Finding A Range Of Letters In Excel
I am looking though branch numbers and depending on the branch number, I send out an email from a particular email address. The problem I am having is that the branches contain letters and numbers. I have some combinations that change email boxes with the same number but different letters (For example 7PZ goes from one email address , while 7RA needs to go though another). Is there a function in VBA that will let me do a range of letters like A-P like I can with numbers or do I have to write out every single letter combination.

Vb/Excel: How To Read An Excel File In A Column WITHOUT Specifying The Range ??
Hello, all.

I found a bit of code that lets me read an Excel file from and to a specifi
range. See the code below. But what if I only want to specify the STARTING
position and simply want it to continue reading down the column until it
hits an empty cell?

Does anyone know how to do this? Thanks!

Here is the range-only code:

' ** turn screen updating off, prevent the user from seeing the source
workbook
' ** then find the file and identify both the worksheet and range
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open(file, False, True)
listitems = sourcewb.Worksheets(sheet).range(temp_range).Value

' ** close the source workbook without saving changes, then reactive
screen update
sourcewb.Close False
Set sourcewb = Nothing
Application.ScreenUpdating = True

' ** read the data, convert values to a vertical array
listitems = Application.WorksheetFunction.Transpose(listitems)

For count = 1 To UBound(listitems)
' ** read the info into a temporary field
temp_var(count) = listitems(count) ' ** populate the array
Next count

' ** no items selected, set to 0 to select the first item
'Combo_machine.ListIndex = -1

Close Workbook

EXCEL: Sum Of Range Only If Next Column Is Right Value
I have values from A1 through A5. I'd like to get the sum of that range, but only for the cells that have "RED" in the next column (over).

Ex:

-----A ----- B
1---50.0---BLUE
2---25.0---YELLOW
3---30.0---RED
4---65.0---RED
5---40.0---BLUE
6---TOTAL---95.00

I'd like to do this through a formula if at all possible.

- Thanks anyways Bruce

Setting Range To Last Excel Column (IV)
Sorry for the newbie question but i can't find the answer on the net or in the reference book i have. I have a loop to find some text that i wish to run until the last column of the worksheet, as in column IV. I have a similer loop to find the last row, which looks like this:

Code:
Loop Until StartAt.Row = 65536

I have tried using:

Code:
Loop Until StartAt.Column = IV

However that seems not to work. Is there another way to reference this cell using numbers instead of letters?

Finding Blanks In Column Or Row, Excel
Do you know how to identify the position of the first blank cell in a column or row without looping through each cell, assuming the starting position of data is known. Thanks.

Reading A Range Of Values From Excel
Hi

I m passing a range of cell address (A15), in a VB application, and I want to read the values of all the cells in that particular range from the excel sheet through a loop.

Can anyone guide on the best way to do it.

Thanking in advance

Finding And Replacing Values In Excel VBA
Hi,

I am trying to find any cells which are blank within my named "view all data"worksheet and replace them with an AB(to denote an absence from a test). However, the values within the worksheet are to be read in from a csv file therefore I do not know the size of the file or the range, and I would like to automate this finding and replacing behind the scenes as it isnt something the user should be aware of.

I have the following module, which when I use the letter "a" instead of ""(Denoting a blank space) it works, but when I try "" nothing happens!!!

Code:Sub FindAB()
Dim Sheet As Worksheet
For Each Sheet In Worksheets
Sheet.Cells.Replace What:="a", Replacement:="AB", LookAt:=xlPart, MatchCase:=False
Next
End Sub

When I invoke this functon within the following code it works:

Code:Public Sub Open_Workbook(OpenName As String)
Dim oCSV As Workbook
Open OpenName For Input As #1

Set oCSV = Workbooks.Open(OpenName)
oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
ThisWorkbook.Worksheets(1).Name = "View All Data"
FindAB '
oCSV.Close False
Set oCSV = Nothing
Close #1
End Sub

I have tried invoking the function below in the code above, however I keep gettin a runtime error "7", stating that it is out of memory, the following element of code is highlighted during debug: Code:If Cells.Value = vbNullString Then

Is there any way of getting this function to work without specifying a range as the code below works if you know the range.

Sub specRange()

   Dim c As Object
   Set c = Cells
For Each c In [E1:e20]
   If c.Value = vbNullString Then
       c.Value = "AB"
   End If
   Next
End Sub

I appreciate any help or comments in advance.

Regards, natalie

I Want To Dump An Array Of Values Into A Range In Excel...
Okay hiya all

I have an array with a bunch of elements... it's set up like arrayname(5,1) where 1 will increment as elements are added, so I'll do a Redim Preserve and make it ArrayName(5,2).

What I wnat to do (and I know it's possible) is to dump an array into a range in excel.

So I want:

ArrayName(1,#) = column 1
arrayname(2,#)=column 2

and so on..

So how can I do it?

VBA For Excel - How To Get A Range Of Multiple Values Across More Than One Line
Sub Copier()

Sheets.Add
Sheets("Sheet1").Select
'Sheets("Sheet1").Name = "Average"
'Range("9976:9976,9989:9989,10001:10001,10011:10011").Select

Range("61:61,122:122,183:183,244:244,305:305,366:366,427:427,488:488,5 49:549,610:610,671:671,732:732,793:793,854:854,915:915,976:976,1037:10 37,1098:1098,1159:1159,1220:1220,1281:1281,1342:1342,1403:1403,1464:14 64,1525:1525,1586:1586,,1647:1647,1708:1708,1769:1769,1830:1830,1891:1 891,,1952:1952,2013:2013,2074:2074,2135:2135,2196:2196,2257:2257,2318: 2318,2379:2379,2440:2440,2501:2501,2562:2562,2623:2623,2684:2684,2745: 2745,2806:2806,2867:2867,2928:2928,2989:2989,3050:3050,3111:3111,3172: 3172,3233:3233,3294:3294,3355:3355,3416:3416,3477:3477,3538:3538,3599: 3599,3660:3660,3721:3721,3782:3782,3843:3843,3904:3904,3965:3965,4026: 4026,4087:4087,4148:4148,4209:4209,4270:4270,4392:4392,4453:4453,4514: 4514,4575:4575,4636:4636,4697:4697,4758:4758,4819:4819,4880:4880,4941: 4941,5002:5002,5063:5063,5124:5124,5185:5185,,5246:5246,5307:5307,5368 :5368,5429:5429,5490:5490,5551:5551,5612:5612,5673:5673,5734:5734,5795 :5795,5856:5856,5917:5917" &_
",5978:5978,6039:6039,6100:6100,6161:6161,6222:6222,6283:6283,6344:634 4,6405:6405,6466:6466,6527:6527,6588:6588,6649:6649,6710:6710,6771:677 1,6832:6832,6893:6893,6954:6954,7015:7015,7076:7076,7137:7137,7198:719 8,7259:7259,7320:7320,7381:7381,7442:7442,7503:7503,7564:7564,7625:762 5,7686:7686,7747:7747,7808:7808,7869:7869,7930:7930,7991:7991,8052:805 2,8113:8113,8174:8174,8235:8235,8296:8296,8357:8357,8418:8418,8479:847 9,8540:8540,8601:8601,8662:8662,8723:8723,8784:8784,8845:8845,8906:890 6,8967:8967,9028:9028,9089:9089,9150:9150,9211:9211,9272:9272,9333:933 3,9394:9394,9455:9455,9516:9516,9577:9577,9638:9638,9699:9699,9760:976 0,9821:9821,9882:9882,9943:9943,10004:10004").Select
Selection.Copy
'Sheets("Sheet3").Select
Sheets("Average").Select
Range("A1").Select
'Sheets("Sheet1").Name = "Average"
ActiveSheet.Paste
'Range("A10011").Activate

End Sub

Excel Macro - How To Modify Values In A Range All At Once
Hello, I'm using this to add and delete a space for every cell in a range (a column)

Code:

Range(Selection, Selection.End(xlDown)).Select

Selection.NumberFormat = "@"

For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next

but the for-next cycle is too slow. (39000 rows)

so here is a faster way!

Code:

Range(Selection, Selection.End(xlDown)).Select 'this is the
'column to change

Selection.NumberFormat = "@" 'not there yet, we have to
'add and remove a space
'from each cell to achieve desired result
v = Selection
for i = 1 to ubound(v)
if isnumeric(v(i,1)) then
v(i,1) = "'" & v(i,1)
end if
next i
Selection.Value = v

thanks to Tom Ogilvy: http://groups.google.com/group/micr...5e9d918862bd670

thx

Finding Multiple Values In An Excel Sheet.
Hello all,

I'm like many others out there a newbie to VBS. I've looked through the forem (grazed really) and I'm sure the answers right there but... I'll stop rambling and ask my question:

I have a database in excel. On one sheet I want to make a sheet that will take the inputed value, look look through a column in another sheet and print all rows with matching values. I tried using non-VB methods (VLOOKUP) but it only returns the first value it finds.

Can anyone help me? I appreciate all of your efforts and assistance. This forum is ideal.

Thanks again!

Excel Column Values
Hello All,
Is there a way of getting the entire column(from excel sheet) values to an array, without using any database connectivity.
some thing like this

ReDim vbAr(mySheet.UsedRange.Rows.Count) As Integer
Dim col1 As String, col2 As String
cell1 = "E1"
cell2 = "E" & mySheet.UsedRange.Rows.Count
vbAr = mySheet.Range(cell1, cell2)
i know this does not work right now, but there should be some way of getting the entire column into an array, without actually reading each of the cells.
Right now this works for me but that is too slow, as my excel files are very very large.
dim TotalRows As Integer,i as integer
TotalRows = mySheet.UsedRange.Rows.Count
ReDim vbAr(TotalRows) As Integer
For i = 2 To TotalRows
vbar(i) = mySheet.Range("E" & i).Value
Next i

Thanks for the help

Vasu

Copy Values Of Big Excel Range (many Columns And Many Rows)
Hi all...
First, excuse me for my Malaysian English..

I'm doing this in Excel VBA:

WBOne.Sheets(1).Range("B10:FB510").Value = WBTwo.Sheets(1).Range("B10:FB510").Value

This code is running from my Addin (XLA).
Both workbooks opened as shared workbook, and both have the same
Sharing Properties (KeepChangeHistory = False)

The problem is: Excel VBA can make it at the first pass, and sometimes
it can make it at the second pass, when the values is swapped between the two workbooks.
Then, at the third pass, Excel seems to be chocked, it takes too long time and begin to be not responding anymore. (I'm using Excel XP in WinXP).
This code also did the same:

WBOne.Sheets(1).Range("B10:FB510").Copy WBTwo.Sheets(1).Range("B10:FB510")

Is that a bug?
Because it is too many COLUMNS and rows to be copied...
But Excel manage to do it at first and/or second pass.

Excel can make it many times if there is only ONE column (and very many rows).

Any help would be very appreciated.
Cheers.

EXCEL VBA: Fastest Way To Populate A Range Of Cells With Values From 2D Array??
What is the fastest algorithm to populate a range of cells with data in a 2 dimensional array of any size? The one I am using seems to be very slow. Look at it below:

Private Function FillSheetFrom2DArray(ws As Worksheet, rowStart As Integer, tempArray() As Double)
        Dim i As Integer, j As Integer
        Dim numCols As Integer, numRows As Integer
        numRows = UBound(tempArray, 1)
        numCols = UBound(tempArray, 2)
        With ws
            For i = 0 To numRows
                For j = 0 To numCols
                    .Cells(rowStart + i, j + 1).Value = tempArray(i, j)
                Next j
            Next i
        End With
End Function

Any ideas?

Thanks

How To Read Specific Excel Range Data Into Datagrid Using Two Date Values?
Hi,

Below is the code that reads a row of Excel data from an Excel File into Visual Basic.NET program datagrid, the code only reads one row of data. I want to add more functionality to the program, on the first column of each rows in the Excel file is date, by adding two datetimepicker in the VB.NET program one listed as start date and other end date. I would like datagrid to return all the data between this two dates, I have trying various scenarios but all unsuccessful. Hope someone can help me.

  Dim oExcel As Object
  Dim i As Integer

        Dim ds As New DataSet
        ds = DataSet1
        ds.Clear()
        DataGrid1.DataSource = Nothing

        oExcel = CreateObject("Excel.Application")

        Dim dr As DataRow
        oExcel.Workbooks.Open(Application.StartupPath & "MyExcel.xls", , False)

        With oExcel.WorkBooks("MyExcel.xls").Worksheets(cmbBox2.SelectedItem)

            dr = ds.Tables("Table1").NewRow

            dr.Item("Date") = .Cells(1, 1).Value
            dr.Item("Min Price") = .Cells(1, 2).Value
            dr.Item("Max Price") = .Cells(1, 3).Value
            dr.Item("Closed Price") = .Cells(1, 4).Value
            dr.Item("Change") = .Cells(1, 5).Value
            dr.Item("Volume") = .Cells(1, 6).Value
            dr.Item("Foreign Inv.") = .Cells(1, 7).Value
            dr.Item("Securities Trust Co.") = .Cells(1, 8).Value
            dr.Item("Dealers") = .Cells(1, 9).Value

            ds.Tables("table1").Rows.Add(dr)

        End With

        Me.DataGrid1.DataSource = ds.Tables("table1")
        Me.DataGrid1.Refresh()

        oExcel.WorkBooks("MyExcel.xls").Close(True)
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

Edited by - roniwu on 11/13/2005 6:04:22 AM

Excel Automation, Walking Down A Column And Setting Values
I've generated a pretty slick Excel automation that I'm using through one of my ASP.NET aps. Basically what it does is;

1. dump a .CSV file
2. open the CSV and copy all of the data in the file
3. Open a second Template (XLT) file and paste the data into the template
4. Run a macro on the pasted data in the template, and save it as a new EXCEL (XLS) file

What I need now is to walk down Column A starting at A2 (where the data starts) and look for reacurring data, if it's there null the field:

For example Column A looks like this

PartNumber
.001-2123
.006-0047
.006-0047
.006-0057
.006-0205
.006-0205
.006-0205
.007-0047
.007-0109
.011-0048
.017-0080
.017-0080
.021-0080

but I want it to look like this

PartNumber
.001-2123
.006-0047

.006-0057
.006-0205

.007-0047
.007-0109
.011-0048
.017-0080

.021-0080

So that the parent part number is listed and any child ones are null. If there are duplicate childs they will fall directly under the parent, so it's not like I need to worry about the part further down in the spreadsheet.

Now I have done this in a VBscript, using a SQL-SERVER database, ISQL and BCP commands, but it's all done on the SQL end. I want to see if it can be done on the .NET end of things using automation.

I'm quite sure it can be done, since you can walk through rows, I just need to know where to start.

Thanks
Petey

Loop Through Named Range And Lock Range Name Based On Values
ok..basicallly I created a worksheet called 'calendar'
this calendar worksheet has 12 named ranges

rngJanuary,rngFebrary....to rngDecember(yes its a calendar)

all of these named ranges are also inside one big range called rngcalendar.

Basically I want to accomplish the following.

when the workbook get opened get the last day of the last month the user entered a value and lock every other named range after the month of the named range the last value was stored in.

eg..if on march 20th the user entered a "w" as a value I want to lock
RngApril through rngDec.

in other words force them to finish entering all values for a month before continuing to the next.

if IT helps all my days of the month are in ltgrey coloring
and the vales the enter go below the day value
in normal white cells(see attachment)

I already validate the entried in the
Private Sub Worksheet_Change(ByVal Target As Range) event but dont know how to accomplish the above step.

Finding Total Ram
what *api[s]*must i use to get the current total phyisical ram and useage or avable ram. and virtual ram if possable. these need to be windows api, i wish to use them in vb and port to other languages(c++,asm) also.

Program, Finding Total...
I want to be able to Calculate the Total of the scores inputted by the user before Calculating the Maximum/Minimum But i don't want to have it showing until later on in the program, When i will have to have the Maximum and Minimum Scores subtracted from the Total of the Scores to create the Final Total. Any help will be Helpful!

Code:
Option Explicit
Dim Scores(1 To 10) As Single

Private Sub cmdEnterScores_Click()
Call Begin
Call Find_Maximum(Scores)
Call Find_Minimum(Scores)
End Sub

Private Sub Begin()
Dim intCounter As Integer 'used to loop through the scores

PicDisplay.Print "Ice Skating Scorer"
PicDisplay.Print ""

For intCounter = 1 To 10
Scores(intCounter) = InputBox("Input next Score")
Do While Scores(intCounter) < 1 Or Scores(intCounter) > 6
Scores(intCounter) = InputBox("The score you have entered is outwith the range 1-6, Please re-enter")
Loop
PicDisplay.Print "Score " & intCounter & " : "; Scores(intCounter)
Next intCounter
End Sub

Private Sub Find_Maximum(ByRef Scores)
Dim Index As Integer
Dim Maximum As Single

Maximum = Scores(1)
For Index = 2 To 10
If Scores(Index) > Maximum Then
Maximum = Scores(Index)
End If
Next Index
PicDisplay.Print ""
PicDisplay.Print "Maximum : "; Maximum
End Sub

Private Sub Find_Minimum(ByRef Scores)
Dim Index As Integer
Dim Minimum As Single

Minimum = Scores(1)
For Index = 2 To 10
If Scores(Index) < Minimum Then
Minimum = Scores(Index)
End If
Next Index
PicDisplay.Print "Minimum : "; Minimum
End Sub

Private Sub cmdEnd_Click()
End
End Sub

Finding Total Time
I have two fields in a database with dates:

StartTime 5/2/01 8:42:07 AM
StopTime 5/2/01 8:45:24 AM

I have to find a way to figure out the total time so I can add it to a textbox.

Total time 2min 17sec
or
TotalTime 137sec

If anyone can help huge thanks in advance!!!!

Searching A Range And Copying Values Into Another Range
Hi all.

Can someone help me with a problem I have. Working in Excel, I have a range (B11:E15) that looks like:

Date    Consumption    Limit    Excess
01/01/04    1100        1000    100
02/01/04    980        1000    0
03/01/04    760        1000    0
04/01/04    1200        1000    200

What I need is code that will search through the column called 'Excess', and for any values that are greater than 0, copy the entire row (values only, not formatting, or equations that may be behind the scenes) into a new range starting at G12. The code will search each row of the data and for any other rows that have an 'excess', VB will paste it immediately beneath any existing entires in the new range. What I want to be left with is:

Date    Consumption    Limit    Excess
01/01/04    1100        1000    100
04/01/04    1200        1000    200

I hope that anyone can help with this as im brand new to VB. I've been searching many forums for people asking similar questions, but i'm having a hard time of it.

Many thanks to anyone that can help.

Total Of A Column
Hi Again
I am trying to get a report to add the total of a column in a Query but I keep getting an Error message in the textbox where the answer should be.
the code I am using is (Sum([TxtSubTotal]))

Total Sum Of Column
Hi, had this in VB Newbie instead of hear....

I have a True data grid control "TDBGrid1" attached to an True Data Control "TdataPayments" connection string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:InstoreDBdata1.mdb;Persist Security Info=False"

In my "TDBGrid1" I have a column "Amount" which is a currancy value, I would like to sum / total this column into a text box "txtAmountTotal".

I have searched back the old posts looking for some pointers or a code snippet that would do this but was unable to find anything.

I am enjoying learning VB and my VB is getting better, but this has me lost...

Any help appreciated

Thanks

Bobby123

Finding The Sum Of Two Column Items And Then, Then Sum Of Column (listview)
I have been working hard on this and I cannot get the sum (subtotal) to be right. Have tried many things and its just not cooperating. Please see screen shot attached.

Also, I have two test records in the DB and only one is showing which makes no sense.

Code below. Thanks in advance for any assistance.

VB Code:
Private Sub cmdViewAll_Click() 'view all time logs in database 'order the records by insert date 'open a connection to the database MyMySQLConnection 'perform an SQL query on the database and order by the insert date strSQLTimeLogViewAll = "SELECT * FROM TimeLog" 'open a recordset OpenTimeLogViewAllRecordset 'populate the listview (lvwTimeLog) With rsTimeLogViewAll If .RecordCount = 0 Then 'do no populating Else Do Until .EOF = True Dim LstTimeLog As ListItem 'lets first clear the list view lvwTimeLog.ListItems.Clear 'populate the list view Set LstTimeLog = lvwTimeLog.ListItems.Add(, , .Fields("JobNumber").Value) LstTimeLog.SubItems(1) = .Fields("CompanyName").Value LstTimeLog.SubItems(2) = .Fields("Category").Value LstTimeLog.SubItems(3) = .Fields("Comments").Value LstTimeLog.SubItems(4) = .Fields("StartTime").Value LstTimeLog.SubItems(5) = .Fields("FinishTime").Value 'get the subtotal of subitems 4 + 5 Dim strFirstValue As String Dim strSecondValue As String Dim strValueTotal As String 'do the calculation strFirstValue = LstTimeLog.SubItems(4) strSecondValue = LstTimeLog.SubItems(5) strValueTotal = Val(strFirstValue) + Val(strSecondValue) 'add it to the last column LstTimeLog.SubItems(6) = Format(strValueTotal, "HH:NN") .MoveNext Loop End If End WithEnd Sub

Access - Finding/getting Total Number Of Records.
Ok, I have a query that searches a massive table by the date. It then displays only unique values for that date. So basically, it takes a total of 500+ records and filters it down by the date entered; comes out to about 100 records per day. Then, it filters once more by unique values in the "Operator ID" field; comes out to about 20-50 different ID's. The main purpose behind my need is to figure out how many users were on for that particular day.

Anyway, I have the query already set up how it should be. I looked in MSDN and Access Help to find some way to count/sum the number of rows, but I can't seem to get anything to work. It's kinda hard for me to explain (still early in the morning here), so I've attached a few screenshots of what I have going on. If you need a better explanation, please let me know.

Thanks!

Linking Coordinates And Finding Total Distance
Hi guys, pls help me with this.

Right now I have many points with x and y coordinates which I have placed into 2 arrays, x(N) and y(N).

I need to
1. start from one point and then find the next nearest point. From the 2nd point, I need to find the next nearest point to be point 3. Then next nearest to be point 4 and so on. I suppose we could use the resultant formula,(sqr[(x2-x1)^2+(y2-y1)^2]) to determine the shortest distance between 2 points.

2. All points can only be chosen once and the last point will go back to the first point.

3. The total distance linking these points must be calculated and outputted as a number.

Any help would be appreciated.

Finding Total Records Out Of Type In Array
Hello All !!!

I need to find out the the total records out there from an Array of Types during run-time..

My Type Declaration is sw. like this

Type tagMyType
sName as STRING*50
sAddress as STRING*100
sEmail as STRING*25
.........................
.........................
End Type

Dim Profile(1 to 100) as tagMyType

I can use 100 as the UpperBound but.. what if I have only some 15 records in the Type..

Isn't there something like UBOUND like in Array??

Thanks in Advance...

How Do I Total A Column In MSHFlexGrid
i got a MSHFlexGrid, and i have amount value on one of the column, i want to add them all so i can see the total amount.
could u guys help me please

thank u in advance

Msflexgrid Column Add Total
OK I want to find the total price from data in a msflexgrid COLUMN and show it in a text box Dont know how to do it.

Total Of A Column In Flexgrid
have a column which i need to get the total of,its a list of doudles,i have a loop but im having trouble getting it to......loop!

Code:
Do While MSHFlexGrid1.TextMatrix(rw, 4) <> Null
total = total + MSHFlexGrid1.TextMatrix(rw, 4)
rw = rw + 1
Loop

its column 4 of the flexgrid
im sure theres a better way and i know theres a thread explaining it but i cant find it,sorry

How To Get The Total Amount Of A Column?
Friends,

How can I get the total of one entire column in a textbox in database?

Getting A Total At The End Of A Flexgrid Column
hi, i have a flex grid populated with overtime hours and the employees details - i'd like to know how to calculate the total overtime worked and pop it in at the bottom of the grid....is this possible?!
thanks

Get Sum Total Of Column In Listview
I need to get Sum total of numeric data in in column

Private Sub cmdLoad_Click()
Dim itm As ListItem
Itemcount = lv.ListItems.Count
Dim a As String, b As String, C As String
Open App.Path & "Converted.txt" For Input As #1
Do Until EOF(1)
Input #1, a, b, C
Set itm = lv.ListItems.Add(, , a)
itm.SubItems(1) = b
itm.SubItems(2) = C
' should I place code here and what code?
Loop

Close #1
' or here maybe a For loop to loop through though I am not
' sure just how to write the loop
' I was thinking something like
ItemCount = lv.Listitems.Count
For i = 1 to ItemCount
' some code here not sure what
Next i
End Sub

Need Help on this one

vbMarkO

Datagrid Column Total
Hello
Any One Help Me?
I have 4 columns in grid
1 itemname
2. Price
3 Qty
4 Amout
I want to amout = price*Qty at time of type value

DataGrid Column Total
Hello
Any One Help Me?
I have 4 columns in grid
1 itemname
2. Price
3 Qty
4 Amout
I want to amout = price*Qty at time of type value

Get The Total Of A Column Of A Flexgrid
How would you go about getting the total(currency) of a column in a MSHFlexGrid??

Getting The Total Of A Column In A MSHFlexGrid
How would you go about getting the total(currency) of a column in a MSHFlexGrid??

Every day above ground is a GOOD DAY!!!

Total Up Column In Msflexgrid
Hi,

I am using ADO and Access. I pull the data from a table into a mshflexgrid. I have a currency field in Access called "service fee". I have positioned a text box directly above the "service Fee" column in the flexgrid and would like the total to be inserted here.

How do I go about getting this total?

I was thinking about creating a query based on this column and pulling it down into a variable.
or maybe the total can be created in Access and then pulled down - not sure how to do this either!

Any ideas or advice will be much appreciated.

Thanks in advance

Dzon

Total Sum Of A Datagrid Column
Hi All

I have a datagrid attached to an dbase app I am working on, the last column has a price value and I would like to create a total value for this column in a label or text box. I was searching back the old posts looking for some pointers or a code snippet that would do this but was unable to find anything.

Any help appreciated

Thanks

Rob

Listview1 Column Total
Sir,
I use VB6 and MSAccess. I get data to the listview. I want to TOTAL of one of the columns in the Listview where some numeric data is found. The data to the listview is brought through ADODC control. Is it possible to get the TOTAL of a column through the ADODC link?

pkb_pkb

Add A New Column After The Row Grand Total
By using Crystal Designer, I created a crosstab report to show the number employees in every department. The summarized column contain 2 different sum field, "foreign" and "local".

         JAN      FEB
Department           Grand Total
----------------------------------------------------------------
ProductionA Foreign      [20] [30]    [ 50]
     Local [60] [50] [110]
ProductionB Foreign [30] [35] [ 65]
     Local [55] [50] [105]

As we can c, the crosstab report will auto generate a grandtotal field for my summarized column. Now, my question is: How could I change the Grand Total into the "Average No. of Workers". the formula should look like this:
sum(foreign)/12.

Any idea? ^_^

Concat Column Values From Multiple Rows Into A Single Column USING Mysql4.1
I"M USING MYSQL 4.1
I'm having two table like:
Doctor Product
1) Doc_ID Name 2) Doc_ID Product_Name
    Doc_1 X Doc_1 p1
    Doc_2 Y Doc_1 p2
         Doc_1 p3
                                           Doc_2 p4
                                           Doc_2 p5
I'm joining these two table via query
Select Doc.Doctor_Id, Doc.Name, Prod.Product_Name from Doctor as Doc inner join Product as Prod on Doc.Doc_ID = PRod.Doc_ID

Result is:
Doc_1 X p1
Doc_1 X p2
Doc_1 X p3
Doc_2 Y p4
Doc_2 Y p5

BUT I WANT IT IN THIS WAY: WITHOUT USING THIRD TABLE
Doc_1 X p1,p2,p3
Doc_2 y p4,p5

CAN ANY ONE HELP ME OUT

Edited by - Macmillan on 6/9/2005 5:19:20 AM

Total Values
I have a stored procedure that is executed on an access form. I want to be able to total the values up for the stored procedure on the form as o dont htink i can do it in the stored preocedure.

Stored precedure executed with these values:

AM times1: 2 PM times1: 0
AM times2: 1 PM times2: 5

Want field on for to show total for the above:

AM times: 3 PM times: 5

At the moment i only get the first value on the form as it is not totaling the second values. How can i do this. This is how i have executed the stored preocedure and displayed on my form.

Code:

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "DBO.sp_EngineerTimeslotsSchedule"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("STARTDATE", adDate, adParamInput, , txt_bookon)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("CONTRACT", adVarChar, adParamInput, 3, txt_contract)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("ENGINEER", adVarChar, adParamInput, 20, cbo_engineer)
cmd.Parameters.Append prm

Set rst = cmd.Execute()

If Not rst.EOF Then
txt_totalAM = rst.Fields("AMAppointments")
txt_totalPM = rst.Fields("PMAppointments")
End If

Set rst = Nothing
Set prm = Nothing
Set cmd = Nothing

Listview Column Total Summing
Hello.

I have a listview and need to find the total value of the currency figures in one of the columns.
Is this possible, if anyone knows the answer your help would be much appreciated.

Thanks,
SKM

Total Currency In DataGrid Column
Here is the code I'm trying to use to add the currency in Column1 of a DataGrid and have it show in the txtTotal.Text textbox. I have used this a couple of years back so I just copied the code but am now getting an error here

dSum = dSum + rs("Total2")

It has something to do with rs("Total2")
Would appreciate any and all help

========================================
Dim fmtCurr As New StdDataFormat
fmtCurr.Format = "Currency"
Set DataGrid1.Columns(1).DataFormat = fmtCurr
Dim rs As ADODB.Recordset
Dim dSum As Double
Set rs = Adodc2.Recordset.Clone
rs.MoveFirst
Do Until rs.EOF
dSum = dSum + rs("Total2")
rs.MoveNext
Loop

Set rs = Nothing

txtTotal.Text = dSum
===========================

Thanks