I have data that looks like the following in a given column
Mary
Dave
Mary
Mary
Tom
Dave
Jim
Barney
"Blank Cell"
Leo

I want to be albe to count the number of unique text occurances and also ingnore the blank cells. The answer shjold be 6

Dear Ron,

Will you please explain me the calculationsteps of your formula.

Best Regards,
Mohammed Nafi.

I have a series of data points in a single row. Some cells in the row contain values and some don't. I would like to count the number of value containing cells between blank cells. For example:

To answer your questions:
1) There are always 52 columns in my data
2) The positions of the numbers always change between the rows of data
3) I am expecting counts of the number of non-blank cells between blank cells, not a total

Any of the cells in the array might be empty (blank)
Some cells might contain a single number only (1 to 4 digits)
Some cells might contain several numbers of 1 to 4 digits separated by commas only
Some cells might contain several numbers of 1 to 4 digits separated by commas and spaces
There can be any combination of the above within the array

Can anyone say how to count the numbers in the array?

I see 2 parts to this: "count" will return the number of single numbers
(but what if the cell is formatted as text?)

Then need to add a count of the commas+1 per cell
This is the tricky bit where I'm stuck

Who has the answer?

Thank you

Ron1Cebu

Thank you very much Ron,

I just found your answer after a couple of years (or so) but still very useful
Had several computer crashes and various other upheavals in my Life
but back in business now (!)

Cheers,
Ron1Cebu

I have searched and read posts, articles, websitesfor days and have not found a solution to match my problem. I really wanted to figure this out on my own, but I need your help. So I appologize if this is covered elsewhere and I missed it.
I have a rather complex macro that extracts productivity and quality statistics for our tech operations department based off a lookup list of the associates and a large extraction from our ticketing database. The macro then performs all the math and functions,
spits all the data out into a simple form which Ipresent to our managers.
I have double/triple checked all my With/End With If/Then/Else/End If statments as well as declared dimensions. Everything'should' work, but isn't. Also I have similar code usedthroughout the macro nearly a dozentimes and do not get
this error. Only in the set of 'new code'I am trying to implement...
The error occurs around line 390 lngPGPResPriority1 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Any suggestions are greatly appreciated.
Thank you,
Jason
Sub IMReview()

Dim lngPGPPriority1 As Long
Dim lngPGPPriority2 As Long
Dim lngPGPPriority3 As Long
Dim lngPGPPriority1SLA As Long
Dim lngPGPPriority2SLA As Long
Dim lngPGPPriority3SLA As Long
Dim lngPGPCI As Long
Dim lngPGPPM As Long
Dim lngPGPPMCheck As Long
Dim lngPGPCVM As Long
Dim lngPGPChange As Long
Dim lngPGPActive As Long
Dim lngPGP1Min As Long
Dim lngPGPReport As Long
Dim lngPGPCreate As Long
Dim lngPGPLog As Long
Dim lngPGPNoLog As Long
Dim lngPGPAssign As Long
Dim lngPGPRestore As Long
Dim lngPGPUnique As Long
Dim lngPGPUntouch As Long
Dim lngWork As Long
Dim lngPriority1 As Long
Dim lngPriority2 As Long
Dim lngPriority3 As Long
Dim lngCI As Long
Dim lngPM As Long
Dim lngPMCheck As Long
Dim lngCVM As Long
Dim lngChange As Long
Dim lngActive As Long
Dim lng1Min As Long
Dim lngPriority1SLA As Long
Dim lngPriority2SLA As Long
Dim lngPriority3SLA As Long
Dim lngReport As Long
Dim lngCreate As Long
Dim lngLog As Long
Dim lngNoLog As Long
Dim lngAssign As Long
Dim lngRestore As Long
Dim lngResCountSLA As Long
Dim lngPGPResCountSLA As Long
Dim lngPGPResPriority1SLA As Long
Dim lngPGPResPriority2SLA As Long
Dim lngPGPResPriority3SLA As Long
Dim lngPGPResPriority1 As Long
Dim lngPGPResPriority2 As Long
Dim lngPGPResPriority3 As Long
Dim strNBID As String
Dim strMaxName As String
Dim strExtract As String
Dim strExtClean As String
Dim strResCount As String
Dim strLookUp As String
Dim strStats As String
Dim strLogTemp As String
Dim lngDataRow As Long
Dim lngDataLRow As Long
Dim lngLookUpRow As Long
Dim lngLookUpLRow As Long
Dim lngStatsRow As Long
Dim lngStatsLRow As Long
Dim lngRowRow As Long
Dim lngRowLRow As Long
Dim strIncident As String
Dim strIncidentN As String
Dim lngColumn As Long
Dim rngColumn As Range
Dim lngBlank As Long
Dim cel As Range
Dim arrCellAddress()
Dim i As Long

Rem Main Stat page name and variable
strStats = Stats
Rem Main Associate lookup page name and variable
strLookUp = Lookup
Rem Temp page created to count work logs, name and variable
strLogTemp = LogTemp
Rem Temp page created to count restoral sla rates, name and variable
strResCount = ResCount
Rem Main eSmart Data Extract page name and variable
strExtract = Extract
Rem Copy of original Extract data after CI/AIT cleaning, name and variable
strExtClean = ExtractClean
Rem Stat page always starts on Row 3 when clean
lngStatsRow = 3
Rem Stat page always begins with the last row being 3 as well
lngStatsLRow = 3
Rem Lookup Data Rows always start on 2, Table Headers are on Row 1
lngLookUpRow = 2

Application.StatusBar = (1 of 12) Report macro has started, validating setup preparing report data. Please be patient...

Rem Lookup data validation - Select lookup sheet, clear filters.
Sheets(strLookUp).Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Find first populated row of column A
Do Until Sheets(strLookUp).Range(A lngLookUpRow).Value
lngLookUpRow = lngLookUpRow 1
Loop
Rem Check for setup errors
Rem If lookup row is greater than 2, attempt to move found data into correct position
If lngLookUpRow 2 Then
Sheets(strLookUp).Range(A lngLookUpRow).Select
With Sheets(strLookUp)
lngLookUpLRow = .Cells(.Rows.Count, B).End(xlUp).Row
End With
Range(Selection, Cells(lngLookUpLRow, B)).Select
Application.CutCopyMode = False
Selection.Cut
Sheets(strLookUp).Range(A2).Select
ActiveSheet.Paste
End If
Rem Everything should be in position now, reset starting value for lookup to row 2 and identify last row to continue.
lngLookUpRow = 2
With Sheets(strLookUp)
lngLookUpLRow = .Cells(.Rows.Count, B).End(xlUp).Row
End With

Rem Check each Maximo name is less than 30 (29 max) characters (Excel limitation)
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Do While Sheets(strLookUp).Range(B lngLookUpRow).Value
If Len(strMaxName) 30 Then
Sheets(strLookUp).Range(B lngLookUpRow).Value = Mid(strMaxName, 1, 29)
End If
lngLookUpRow = lngLookUpRow 1
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Loop
lngLookUpRow = 2

Rem Begin Extract data validation
Rem Select Extract sheet, clear filters and sorts.
Sheets(strExtract).Select
ActiveSheet.ListObjects(1).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Find first populated row of column A
Do Until Sheets(strExtract).Range(A lngLookUpRow).Value
lngLookUpRow = lngLookUpRow 1
Loop
Rem Check for setup errors
Rem If extract row is greater than 2, attempt to move data into correct position.
If lngLookUpRow 2 Then
Sheets(strExtract).Range(A lngLookUpRow).Select
With Sheets(strExtract)
lngDataLRow = .Cells(.Rows.Count, A).End(xlUp).Row
End With
Range(Selection, Cells(lngDataLRow, W)).Select
Application.CutCopyMode = False
Selection.Cut
Sheets(strExtract).Range(A2).Select
ActiveSheet.Paste
End If
Rem everything should be in position now, reset lookup value.
lngLookUpRow = 2

Rem Begin Stat sheet validation
Rem select stat sheet
Sheets(strStats).Select
Rem Check Stat sheet is clear and ready for use.
Do Until Sheets(strStats).Range(A lngStatsLRow).Value = Associate Totals
lngStatsLRow = lngStatsLRow 1
Loop
Rem delete any stat rows beyond 4
If lngStatsLRow 4 Then
Sheets(strStats).Rows(4: lngStatsLRow - 1).Delete Shift:=xlUp
End If
Rem clear the contents of row 3
Sheets(strStats).Rows(3:3).ClearContents
Rem Set the Last LookUp row value
With Sheets(strLookUp)
lngLookUpLRow = .Cells(.Rows.Count, B).End(xlUp).Row
End With
Rem Set the Last Extract row value
With Sheets(strExtract)
lngDataLRow = .Cells(.Rows.Count, A).End(xlUp).Row
End With

Application.StatusBar = (2 of 12) Validation complete. Cleaning eSmart data, reports with 10,000 lines will several minutes. Please be patient...

Rem Begin eSmart CI/AIT cleanup concatenate columns where blanks are found
Rem Select Extract Sheet, clear sort filters
Sheets(strExtract).Select
ActiveSheet.ListObjects(1).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Sort the Extract sheet by Column K '(AIT).AIT Number'
Sheets(strExtract).Sort.SortFields.Add Key:=Range(K1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(strExtract).Sort
.SetRange Range(A1:W lngDataLRow)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Rem Find the first blank cell in Column K '(AIT).AIT Number', set value
If Sheets(Extract).Range(K2).Value = Then
Sheets(Extract).Range(K2).Select
lngBlank = ActiveCell.Row
Else
Sheets(Extract).Range(K2).End(xlDown).Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
lngBlank = ActiveCell.Row
End If
Rem First move of data between columns.
Rem Select all data of Column L M (AIT.AIT Name Number) from the 'Blank cell value' to the end, cut to clipboard
Sheets(strExtract).Range(L lngBlank).Select
Range(Selection, Cells(lngDataLRow, M)).Select
Application.CutCopyMode = False
Selection.Cut
Rem Paste the contents in Column J K
Sheets(strExtract).Range(J lngBlank).Select
ActiveSheet.Paste
Rem Resort column K again
Sheets(strExtract).Sort.SortFields.Clear
Sheets(strExtract).Sort.SortFields.Add Key:=Range(K1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(strExtract).Sort
.SetRange Range(A1:V lngDataLRow)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Rem second move of data between columns.
Rem Find the first blank cell in Column K, set value
Sheets(Extract).Range(K1).End(xlDown).Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
lngBlank = ActiveCell.Row
Rem Move to the 'Blank cell value' for Column N (CI.AIT.CI Number), select all remaining contents, cut to clipboard.
Sheets(strExtract).Range(N lngBlank).Select
Range(Selection, Cells(lngDataLRow, N)).Select
Application.CutCopyMode = False
Selection.Cut
Rem Move to the 'Blank cell value' for Column K, paste.
Sheets(strExtract).Range(K lngBlank).Select
ActiveSheet.Paste
Rem third and final move of data between columns.
Rem Sort K again.
Sheets(strExtract).Sort.SortFields.Clear
Sheets(strExtract).Sort.SortFields.Add Key:=Range(K1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(strExtract).Sort
.SetRange Range(A1:W lngDataLRow)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Rem Find the first blank in K, set value.
Sheets(Extract).Range(K1).End(xlDown).Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
lngBlank = ActiveCell.Row
Rem Move to Column O, cut any remaining data.
Sheets(strExtract).Range(O lngBlank).Select
Range(Selection, Cells(lngDataLRow, O)).Select
Application.CutCopyMode = False
Selection.Cut
Rem Paste it in Column K
Sheets(strExtract).Range(K lngBlank).Select
ActiveSheet.Paste
Rem First and only alteration of data in cells of extract.
Rem Remove the CIAIT text from Column K
Sheets(strExtract).Range(K2).Select
Range(Selection, Cells(lngDataLRow, K)).Select
Selection.Replace What:=CIAIT, Replacement:=, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Application.StatusBar = (3 of 12) Creating temp worksheets for metric stat counts, please be patient...

Rem Create two copies of Template sheet rename as LogTemp ResCount for later data collection.
Rem Create copy of Extract Sheet. NOTE: Original Extract data is no longer used or manipulated, it is kept only as a maunal reference.

Sheets(Template).Visible = True
Sheets(Template).Application.CutCopyMode = False
Sheets(Template).Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = strLogTemp
ActiveSheet.ListObjects(1).Name = strLogTemp
Sheets(Template).Application.CutCopyMode = False
Sheets(Template).Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = strResCount
ActiveSheet.ListObjects(1).Name = strResCount
Sheets(Template).Visible = False

Sheets(Extract).Application.CutCopyMode = False
Sheets(Extract).Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = strExtClean

Application.StatusBar = (4 of 12) Counting unique work log and restoral stats, please be patient...

Rem search the new ExtractClean tab for blank worklog entries for each associate name listed in lookup.
Rem select the first associate lookup, set as value
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Do While Sheets(strLookUp).Range(B lngLookUpRow).Value
Application.ScreenUpdating = False
Rem Select Extract Clean sheet, Clear sort/filter, then filter WorkLog Created by associate name
Sheets(strExtClean).Select
Sheets(strExtClean).ListObjects(1).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Sheets(strExtClean).ListObjects(1).Range.AutoFilter Field:=7, Criteria1:==* strMaxName *
Rem Select any rows found by filter and copy to a temp log capture sheet, repeat for all names in lookup.
Range(A1).CurrentRegion.Select
Application.CutCopyMode = False
Selection.Copy
Rem Select the log temp sheet, paste data to the first empty row, delete the header row if pasted.
Sheets(strLogTemp).Select
With Sheets(LogTemp)
lngRowRow = .Cells(.Rows.Count, A).End(xlUp).Row
End With
If lngRowRow = 2 Then
Sheets(strLogTemp).Range(A lngRowRow).Select
ActiveSheet.Paste
ActiveCell.EntireRow.Delete
Else
Sheets(strLogTemp).Range(A lngRowRow 1).Select
ActiveSheet.Paste
ActiveCell.EntireRow.Delete
End If
Rem Increase lookup row by 1, set new look up value, repeat loop till complete.
lngLookUpRow = lngLookUpRow 1
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Loop

Rem once logtemp process is complete remove any duplicate ticket entries, count remaining rows, set as value.
Sheets(strLogTemp).Select
ActiveSheet.ListObjects(1).DataBodyRange.RemoveDuplicates Columns:=2, Header:=xlNo
With Sheets(LogTemp)
lngPGPLog = .Cells(.Rows.Count, A).End(xlUp).Row
End With
Rem delete the logtemp sheet once value for total blank worklogs has been counted
Application.DisplayAlerts = False
Sheets(strLogTemp).Delete
Application.DisplayAlerts = True
Rem Reset standard sheet values for next search
lngRowRow = 2
lngLookUpRow = 2
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value

Rem NEW CODE ************************************************

Rem search the new ExtractClean tab for restoral entries for each associate name listed in lookup.
Rem select the first associate lookup, set as value
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Do While Sheets(strLookUp).Range(B lngLookUpRow).Value
Application.ScreenUpdating = False
Rem Select Extract Clean sheet, Clear sort/filter, then filter Restored by associate name
Sheets(strExtClean).Select
Sheets(strExtClean).ListObjects(1).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Sheets(strExtClean).ListObjects(1).Range.AutoFilter Field:=9, Criteria1:==* strMaxName *
Rem Select any rows found by filter and copy to the ResCount capture sheet, repeat for all names in lookup.
Range(A1).CurrentRegion.Select
Application.CutCopyMode = False
Selection.Copy
Rem Select the ResCount sheet, paste data to the first empty row, delete the header row if pasted.
Sheets(strResCount).Select
With Sheets(ResCount)
lngRowRow = .Cells(.Rows.Count, A).End(xlUp).Row
End With
If lngRowRow = 2 Then
Sheets(strResCount).Range(A lngRowRow).Select
ActiveSheet.Paste
ActiveCell.EntireRow.Delete
Else
Sheets(strResCount).Range(A lngRowRow 1).Select
ActiveSheet.Paste
ActiveCell.EntireRow.Delete
End If
Rem Increase lookup row by 1, set new look up value, repeat loop till complete.
lngLookUpRow = lngLookUpRow 1
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Loop

Rem once restoral count process is complete remove any duplicate ticket entries.
Sheets(strResCount).Select
ActiveSheet.ListObjects(1).DataBodyRange.RemoveDuplicates Columns:=2, Header:=xlNo

Stop

Rem select ResCount, clear sort/filter start PGP stat counts.
Sheets(strResCount).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Filter count for P1 counts (Column C/3)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=1
lngPGPResPriority1 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem Filter Count for PGP P1 SLA counts. (Column CV)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=22, Criteria1:=00:01:59:00
lngPGPResPriority1SLA = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Filter Count for PGP P2 counts (Column C/3)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=2
lngPGPResPriority2 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem Filter Count for PGP P2 SLA Counts. (Column C/3 V/22)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=22, Criteria1:=00:23:59:00
lngPGPResPriority2SLA = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Filter Count PGP P3 counts (Column C/3)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=3
lngPGPResPriority3 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem Filter Count PGP P3 SLA Counts. (Column C/3 W/23)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=23, Criteria1:= 2879
lngPGPResPriority3SLA = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem End With

Rem clean up code (if needed) ...

Rem With Sheets(ResCount)
Rem lngResCount = .Cells(.Rows.Count, A).End(xlUp).Row
Rem End With
Rem delete the logtemp sheet once value for total blank worklogs has been counted
Rem Application.DisplayAlerts = False
Rem Sheets(strLogTemp).Delete
Rem Application.DisplayAlerts = True
Rem Reset standard sheet values for next search
Rem lngRowRow = 2
Rem lngLookUpRow = 2
Rem strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value

Rem NEW CODE ************************************************

Application.StatusBar = (5 of 12) Removing duplicates from temp data, please be patient...

Rem Select Extract Clean sheet, clear sort/filters, and remove all duplicate ticket entries, reset the last data row value.
Sheets(strExtClean).Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
ActiveSheet.ListObjects(1).DataBodyRange.RemoveDuplicates Columns:=2, Header:=xlNo
With Sheets(strExtClean)
lngDataLRow = .Cells(.Rows.Count, A).End(xlUp).Row
End With

Application.StatusBar = (6 of 12) Creating Pivot table for AIT/CI data collection, please be patient...

Rem make visible and select the Pivot Temp sheet for data collection.
Sheets(PivotTemp).Visible = True
Rem add a new pivot table sheet, using Extract Clean sheet,
Sheets.Add
ActiveSheet.Name = Pivot
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets(strExtClean).ListObjects(1), Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=Pivot!R3C1, TableName:=Pivot1, DefaultVersion:=xlPivotTableVersion12
Rem Select new Pivot Table sheet and first cell of pivot table.
Sheets(Pivot).Select
Cells(3, 1).Select
Rem Set AIT number (Column K) in pivot
With ActiveSheet.PivotTables(Pivot1).PivotFields(Incident.CI (AIT).AIT Number)
.Orientation = xlRowField
.Position = 1
End With
Rem Set AIT name (Column J) in pivot
With ActiveSheet.PivotTables(Pivot1).PivotFields(Incident.CI (AIT).AIT Name)
.Orientation = xlRowField
.Position = 2
End With
Rem set Ticket ID column in pivot, set to count as total and sort descending.
ActiveSheet.PivotTables(Pivot1).AddDataField ActiveSheet.PivotTables( _
Pivot1).PivotFields(Incident.Ticket ID), Count of Incident.Ticket ID, xlCount
Rem select all data in sorted pivot table and copy to clipboard.
Sheets(Pivot).Range(A3).End(xlDown).Select
lngRowLRow = ActiveCell.Row - 1
ActiveSheet.Range(A4:B lngRowLRow).Select
ActiveSheet.Application.CutCopyMode = False
Selection.Copy
Rem Select Pivot Temp sheet and paste collected data for additional clean up.
Sheets(PivotTemp).Select
ActiveSheet.Range(A2).PasteSpecial Paste:=xlPasteValues
lngRowRow = 2
Rem Select all data in column A, cut to clipboard.
Do While Sheets(PivotTemp).Range(A lngRowRow).Value
ActiveSheet.Range(A lngRowRow 1 :B lngRowRow 1).Select
ActiveSheet.Application.CutCopyMode = False
Selection.Cut
Rem move to column C and paste
ActiveSheet.Range(C lngRowRow).Select
ActiveSheet.Paste
lngRowRow = lngRowRow 2
Loop
Rem Clear the sort/filter on Pivot Temp sheet, sort Column D descending.
Sheets(PivotTemp).ListObjects(1).Sort.SortFields.Clear
Sheets(PivotTemp).Sort.SortFields.Add Key:=Range(D1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Sheets(PivotTemp).Sort
.SetRange Range(A1:D lngRowRow - 1)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Rem select data from Column B and clear it, redundant count of total tickets.
Columns(B:B).Select
Selection.ClearContents
Rem move data from column A (AIT number) to Column B
Sheets(PivotTemp).Range(A2).End(xlDown).Select
lngRowLRow = ActiveCell.Row
Range(A2).Select
Range(Selection, Selection.End(xlDown)).Select
Range(A2:A lngRowLRow).Cut Destination:=Range(B2:B lngRowLRow)
Rem move data from Column D (Ticket count to Column A
Range(D2).Select
Range(Selection, Selection.End(xlDown)).Select
Range(D2:D lngRowLRow).Cut Destination:=Range(A2:A lngRowLRow)
Rem delete the now blank column D, data is now sorted Count, AIT#, Name.
Columns(D:D).Select
Selection.Delete Shift:=xlToLeft
Rem Delete the pivot table sheet
Range(A2).Select
Application.DisplayAlerts = False
Sheets(Pivot).Delete
Application.DisplayAlerts = True
Sheets(strExtClean).Select
Rem count total tickets on Extract Clean, set as value.
With Sheets(ExtractClean)
lngPGPUnique = .Cells(.Rows.Count, B).End(xlUp).Row - 1
End With

Application.StatusBar = (7 of 12) Starting count of PGP stats, please be patient...

Rem select Extract Clean, clear sort/filter start PGP stat counts.
Sheets(strExtClean).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Filter count for PGP P1 counts (Column C/3)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=1
lngPGPPriority1 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem Filter Count for PGP P1 SLA counts. (Column CV)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=22, Criteria1:=00:01:59:00
lngPGPPriority1SLA = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Filter Count for PGP P2 counts (Column C/3)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=2
lngPGPPriority2 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem Filter Count for PGP P2 SLA Counts. (Column C/3 V/22)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=22, Criteria1:=00:23:59:00
lngPGPPriority2SLA = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Filter Count PGP P3 counts (Column C/3)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=3
lngPGPPriority3 = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem Filter Count PGP P3 SLA Counts. (Column C/3 W/23)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=23, Criteria1:= 2879
lngPGPPriority3SLA = ActiveSheet.AutoFilter.Range.Columns(C).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP CI/AIT counts (Column K/11)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=11, Criteria1:==
lngPGPCI = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP PM count (Column R/18)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=18, Criteria1:=
lngPGPPM = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP PM Check counts (Column Q/17)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=17, Criteria1:=YES
lngPGPPMCheck = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP CVM Counts (Column S/19)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=19, Criteria1:=
lngPGPCVM = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP Change Related count (Column P/16)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=16, Criteria1:=YES
lngPGPChange = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem Clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP Active ticket count (Column U/21)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=21, Criteria1:==
lngPGPActive = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem Clear Sort/Filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP restored under 1 min (Column V/23 U/22)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=22, Criteria1:=00:00:01:01
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=22, Criteria1:=
lngPGP1Min = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem filter count PGP No Logs (Column G/7)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=7, Criteria1:==
lngPGPNoLog = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem Reset lookup row
lngLookUpRow = 2
Rem pull values from Lookup tab for data search
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Do While Sheets(strLookUp).Range(B lngLookUpRow).Value
Application.ScreenUpdating = False
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem sort Reported By, by lookup value, set PGP Reported (increment each loop)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=5, Criteria1:==* strMaxName *
lngPGPReport = lngPGPReport ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem sort Created By, by lookup value, set PGP Created (increment each loop)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=6, Criteria1:==* strMaxName *
lngPGPCreate = lngPGPCreate ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Sort Assignment Changed By, by lookup value, set PGP Assigned (increment each loop)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=8, Criteria1:==* strMaxName *
lngPGPAssign = lngPGPAssign ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem clear sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem sort Restored By, by lookup value, set PGP Restored (increment each loop)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=9, Criteria1:==* strMaxName *
lngPGPRestore = lngPGPRestore ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem Clear Sort/filter
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem Increment Lookup value by 1 pull next name for lookup loop.
lngLookUpRow = lngLookUpRow 1
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
Loop
Rem if sheet Untouched already exists, delete it, otherwise create it.
If SheetExists(Untouched) Then
Application.DisplayAlerts = False
Sheets(Untouched).Delete
Application.DisplayAlerts = True
End If

Application.StatusBar = (8 of 12) Identifying tickets that have gone Untouched, please be patient...

Rem Reset lookup row value to default start.
lngLookUpRow = 2
Rem Start count of tickets untouched (no look name present in Action Take By columns)
Do While Sheets(strLookUp).Range(B lngLookUpRow).Value
Application.ScreenUpdating = False
Rem pull first Lookup name value, search columns 5-9 for any reference and delete it.
Rem lines deleted because if your name shows up, you did touch it and we are looking for untouched.
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
For lngColumn = 5 To 9
Rem Set search criteria for each column by lookup value
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=lngColumn, Criteria1:==* strMaxName *
If ActiveSheet.ListObjects(1).AutoFilter.Range.Columns(lngColumn).SpecialCells(xlCellTypeVisible).Cells.Count 1 Then
With ActiveSheet.ListObjects(1).DataBodyRange
Set rngColumn = .Columns(lngColumn).SpecialCells(xlCellTypeVisible)
End With
Rem set array to search each cell in column for lookup data.
For Each cel In rngColumn
i = i 1
ReDim Preserve arrCellAddress(1 To i)
arrCellAddress(i) = cel.Address
Next cel
For i = UBound(arrCellAddress) To 1 Step -1
Range(arrCellAddress(i)).EntireRow.Delete
Next i
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Else
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
End If
Next lngColumn
Rem Reset Lookup Row value
lngLookUpRow = lngLookUpRow 1
Loop
Rem clear sort/filter and rename remaining data on sheet to Untouched, set variable value.
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
ActiveSheet.Name = Untouched
lngPGPUntouch = ActiveSheet.AutoFilter.Range.Columns(B).SpecialCells(xlCellTypeVisible).Count - 1
Rem reset lookup row value to default start.
lngLookUpRow = 2
Rem Select Extract sheet for additional data collection, clear sort/filters
Sheets(strExtract).Select
ActiveSheet.ListObjects(1).Sort.SortFields.Clear
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Rem set value for last row of Extract.
With Sheets(strExtract)
lngDataLRow = .Cells(.Rows.Count, A).End(xlUp).Row
End With
Rem sort/filter
ActiveSheet.ListObjects(1).Sort.SortFields.Add Key:=Range(B1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(strExtract).Sort
.SetRange Range(A1:W lngDataLRow)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With

Application.StatusBar = (9 of 12) Creating individual associate tabs and collecting data, please be patient...

Do While Sheets(strLookUp).Range(B lngLookUpRow).Value
Application.ScreenUpdating = False
strMaxName = Sheets(strLookUp).Range(B lngLookUpRow).Value
strNBID = Sheets(strLookUp).Range(A lngLookUpRow).Value
If Sheets(strStats).Range(A lngStatsRow).Value = Associate Totals Then
Range(A3).ListObject.ListRows.Add AlwaysInsert:=True
End If
Sheets(strStats).Range(A lngStatsRow).Value = strMaxName
If SheetExists(strMaxName) Then
Application.DisplayAlerts = False
Sheets(strMaxName).Delete
Application.DisplayAlerts = True
End If
Sheets(Template).Visible = True
Sheets(Template).Application.CutCopyMode = False
Sheets(Template).Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = strMaxName
ActiveSheet.ListObjects(1).Name = strNBID
Sheets(Template).Visible = False
For varColumn = 5 To 9
Sheets(strExtract).Select
Sheets(strExtract).ListObjects(1).Range.AutoFilter Field:=varColumn, Criteria1:==* strMaxName *
Range(A1).CurrentRegion.Select
Application.CutCopyMode = False
Selection.Copy
Sheets(strMaxName).Select
lngRowRow = Sheets(strMaxName).Range(A1).End(xlDown).Row
If lngRowRow = 2 Then
Sheets(strMaxName).Range(A lngRowRow).Select
ActiveSheet.Paste
Sheets(strMaxName).Rows(lngRowRow : lngRowRow).Delete
Else
Sheets(strMaxName).Range(A lngRowRow 1).Select
ActiveSheet.Paste
Sheets(strMaxName).Rows(lngRowRow 1 : lngRowRow 1).Delete
End If
Sheets(strMaxName).Range(A1).Select
Sheets(strExtract).Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Next
lngRowRow = 2
Sheets(strMaxName).Select
If Sheets(strMaxName).Range(A2) Then
ActiveSheet.ListObjects(1).DataBodyRange.RemoveDuplicates Columns:=2, Header:=xlNo
Else
End If
lngDataLRow = Sheets(strMaxName).Range(A1).End(xlDown).Row
Do While lngRowRow lngDataLRow 1
strIncident = Sheets(strMaxName).Range(B lngRowRow).Value
strIncidentN = Sheets(strMaxName).Range(B lngRowRow 1).Value
If LCase(Sheets(strMaxName).Range(E lngRowRow).Value) Like * LCase(strMaxName) * _
Or LCase(Sheets(strMaxName).Range(F lngRowRow).Value) Like * LCase(strMaxName) * _
Or LCase(Sheets(strMaxName).Range(G lngRowRow).Value) Like * LCase(strMaxName) * _
Or LCase(Sheets(strMaxName).Range(H lngRowRow).Value) Like * LCase(strMaxName) * _
Or LCase(Sheets(strMaxName).Range(I lngRowRow).Value) Like * LCase(strMaxName) * Then
lngWork = 1
If Sheets(strMaxName).Range(C lngRowRow).Value = 1 Then
lngPriority1 = 1
If Sheets(strMaxName).Range(V lngRowRow).Value 00:01:59:00 Then lngPriority1SLA = 1
ElseIf Sheets(strMaxName).Range(C lngRowRow).Value = 2 Then
lngPriority2 = 1
If Sheets(strMaxName).Range(V lngRowRow).Value 00:23:59:00 Then lngPriority2SLA = 1
ElseIf Sheets(strMaxName).Range(C lngRowRow).Value = 3 Then
lngPriority3 = 1
If Sheets(strMaxName).Range(W lngRowRow).Value 2879 Then lngPriority3SLA = 1
End If
If LCase(Sheets(strMaxName).Range(E lngRowRow).Value) Like * LCase(strMaxName) * Then lngReport = 1
If LCase(Sheets(strMaxName).Range(F lngRowRow).Value) Like * LCase(strMaxName) * Then lngCreate = 1
If LCase(Sheets(strMaxName).Range(G lngRowRow).Value) Like * LCase(strMaxName) * Then lngLog = 1
If Sheets(strMaxName).Range(G lngRowRow).Value = Then lngNoLog = 1
If LCase(Sheets(strMaxName).Range(H lngRowRow).Value) Like * LCase(strMaxName) * Then lngAssign = 1
If LCase(Sheets(strMaxName).Range(I lngRowRow).Value) Like * LCase(strMaxName) * Then lngRestore = 1
If Sheets(strMaxName).Range(K lngRowRow).Value = Then lngCI = 1
If Sheets(strMaxName).Range(P lngRowRow).Value = YES Then lngChange = 1
If Sheets(strMaxName).Range(Q lngRowRow).Value = YES Then lngPMCheck = 1
If Sheets(strMaxName).Range(R lngRowRow).Value 1 Then lngPM = 1
If Sheets(strMaxName).Range(S lngRowRow).Value 1 Then lngCVM = 1
If Sheets(strMaxName).Range(U lngRowRow).Value = Then lngActive = 1
If Sheets(strMaxName).Range(U lngRowRow).Value 1 And Sheets(strMaxName).Range(V lngRowRow).Value 00:00:01:01 Then lng1Min = 1
End If
If strIncident strNIncident And (lngWork lngPriority1 lngPriority2 lngPriority3 lngCI lngPM lngPMCheck lngCMV lngChange lngActive lng1Min lngPriority1SLA lngPriority2SLA lngPriority3SLA lngCreate lngLog lngNoLog lngAssign lngRestore) 0 Then
Sheets(strStats).Range(B lngStatsRow).Value = Sheets(strStats).Range(B lngStatsRow).Value lngWork
Sheets(strStats).Range(C lngStatsRow).Value = Sheets(strStats).Range(C lngStatsRow).Value lngPriority1
Sheets(strStats).Range(D lngStatsRow).Value = Sheets(strStats).Range(D lngStatsRow).Value lngPriority2
Sheets(strStats).Range(E lngStatsRow).Value = Sheets(strStats).Range(E lngStatsRow).Value lngPriority3
Sheets(strStats).Range(F lngStatsRow).Value = Sheets(strStats).Range(F lngStatsRow).Value lngCI
Sheets(strStats).Range(G lngStatsRow).Value = Sheets(strStats).Range(G lngStatsRow).Value lngPM
Sheets(strStats).Range(H lngStatsRow).Value = Sheets(strStats).Range(H lngStatsRow).Value lngPMCheck
Sheets(strStats).Range(I lngStatsRow).Value = Sheets(strStats).Range(I lngStatsRow).Value lngCVM
Sheets(strStats).Range(J lngStatsRow).Value = Sheets(strStats).Range(J lngStatsRow).Value lngChange
Sheets(strStats).Range(K lngStatsRow).Value = Sheets(strStats).Range(K lngStatsRow).Value lngActive
Sheets(strStats).Range(L lngStatsRow).Value = Sheets(strStats).Range(L lngStatsRow).Value lng1Min
Sheets(strStats).Range(M lngStatsRow).Value = Sheets(strStats).Range(M lngStatsRow).Value lngPriority1SLA
Sheets(strStats).Range(N lngStatsRow).Value = Sheets(strStats).Range(N lngStatsRow).Value lngPriority2SLA
Sheets(strStats).Range(O lngStatsRow).Value = Sheets(strStats).Range(O lngStatsRow).Value lngPriority3SLA
Sheets(strStats).Range(P lngStatsRow).Value = Sheets(strStats).Range(P lngStatsRow).Value lngReport
Sheets(strStats).Range(Q lngStatsRow).Value = Sheets(strStats).Range(Q lngStatsRow).Value lngCreate
Sheets(strStats).Range(R lngStatsRow).Value = Sheets(strStats).Range(R lngStatsRow).Value lngLog
Sheets(strStats).Range(S lngStatsRow).Value = Sheets(strStats).Range(S lngStatsRow).Value lngNoLog
Sheets(strStats).Range(T lngStatsRow).Value = Sheets(strStats).Range(T lngStatsRow).Value lngAssign
Sheets(strStats).Range(U lngStatsRow).Value = Sheets(strStats).Range(U lngStatsRow).Value lngRestore
lngWork = 0
lngPriority1 = 0
lngPriority2 = 0
lngPriority3 = 0
lngCI = 0
lngPM = 0
lngPMCheck = 0
lngCVM = 0
lngChange = 0
lngActive = 0
lng1Min = 0
lngPriority1SLA = 0
lngPriority2SLA = 0
lngPriority3SLA = 0
lngReport = 0
lngCreate = 0
lngLog = 0
lngNoLog = 0
lngAssign = 0
lngRestore = 0
End If
lngRowRow = lngRowRow 1
Loop
lngStatsRow = lngStatsRow 1
Application.ScreenUpdating = True
Sheets(strStats).Select
Sheets(strStats).Range(A1).Select

Application.StatusBar = (10 of 12) Associate tab process is Format((lngLookUpRow - 2) / (lngLookUpLRow - 2), Percent) Complete:

lngLookUpRow = lngLookUpRow 1
Loop

Application.StatusBar = (11 of 12) Performing finishing touches, almost done...

Sheets(strStats).Range(B lngStatsRow 3) = lngPGPUnique
Sheets(strStats).Range(C lngStatsRow 3) = lngPGPPriority1
Sheets(strStats).Range(D lngStatsRow 3) = lngPGPPriority2
Sheets(strStats).Range(E lngStatsRow 3) = lngPGPPriority3
Sheets(strStats).Range(F lngStatsRow 3) = lngPGPCI
Sheets(strStats).Range(G lngStatsRow 3) = lngPGPPM
Sheets(strStats).Range(H lngStatsRow 3) = lngPGPPMCheck
Sheets(strStats).Range(I lngStatsRow 3) = lngPGPCVM
Sheets(strStats).Range(J lngStatsRow 3) = lngPGPChange
Sheets(strStats).Range(K lngStatsRow 3) = lngPGPActive
Sheets(strStats).Range(L lngStatsRow 3) = lngPGP1Min
Sheets(strStats).Range(M lngStatsRow 3) = lngPGPPriority1SLA
Sheets(strStats).Range(N lngStatsRow 3) = lngPGPPriority2SLA
Sheets(strStats).Range(O lngStatsRow 3) = lngPGPPriority3SLA
Sheets(strStats).Range(P lngStatsRow 3) = lngPGPReport
Sheets(strStats).Range(Q lngStatsRow 3) = lngPGPCreate
Sheets(strStats).Range(R lngStatsRow 3) = lngPGPLog
Sheets(strStats).Range(S lngStatsRow 3) = lngPGPNoLog
Sheets(strStats).Range(T lngStatsRow 3) = lngPGPAssign
Sheets(strStats).Range(U lngStatsRow 3) = lngPGPRestore
Sheets(strStats).Range(J lngStatsRow 8) = lngPGPUntouch
Sheets(strStats).Sort.SortFields.Add Key:=Range(B3), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Sheets(strStats).Sort
.SetRange Range(A2:U lngStatsRow - 1)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Sheets(PivotTemp).Select
Sheets(PivotTemp).Range(A2).End(xlDown).Select
lngRowLRow = ActiveCell.Row
Sheets(PivotTemp).Range(A2:C lngRowLRow).Select
Selection.Copy
Sheets(strStats).Select
Range(W3).Select
ActiveSheet.Paste
Sheets(strStats).Range(W3:Y7).Select
Selection.Copy
Sheets(strStats).Range(B lngStatsRow 14).Select
ActiveSheet.Paste
Sheets(strStats).Range(W8:Y12).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(strStats).Range(L lngStatsRow 14).Select
ActiveSheet.Paste
Cells.Select
ActiveSheet.Rows.AutoFit
Sheets(strStats).Range(B3).Select
Application.DisplayAlerts = False
Rem Sheets(PivotTemp).Delete
Rem Sheets(Template).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets(strStats).Select
Sheets(strStats).Range(A1).Select
Application.StatusBar = Report Extraction Complete.
End Sub

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function

I'm new, please help. Begging for forgiveness in advance.

George, thank you for the suggestion. This solution gets me past the RTE91. Now the count of visible cells is off. I have tried using several variations using the suggestions above, still plugging away at it. Thanks for the tip!I'm new, please help. Begging for forgiveness in advance.

All my text cells in my excel sheet have been converted into #Value :( The numbers appear the same thankfully :)

It could be due to a corruption in the workbook.

I have set up a form that I input data into including the date, when the data comes across into the excel worksheet it is recognised as text (I want it recognised as a date). Here is my code below, looking forward to your answers.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lRow2 As Long
Dim lRow3 As Long
Dim lRow4 As Long
Dim lRow5 As Long
Dim lRow6 As Long
Dim lRow7 As Long
Dim lRow8 As Long
Dim lRow9 As Long
Dim lRow10 As Long
Dim lRow11 As Long
Dim lRow12 As Long
Dim lPart As Long
Dim lPart2 As Long
Dim lPart3 As Long
Dim lPart4 As Long
Dim lPart5 As Long
Dim lPart6 As Long
Dim lPart7 As Long
Dim lpart8 As Long
Dim lPart9 As Long
Dim lPart10 As Long
Dim lPart11 As Long
Dim lPart12 As Long
Dim ws As Worksheet
Set ws = Worksheets("WarehouseTransactions")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'find second empty row in database
lRow2 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(2, 0).Row

'find third empty row in database
lRow3 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(3, 0).Row
'find forth empty row in database
lRow4 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(4, 0).Row

'find fifth empty row in database
lRow5 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(5, 0).Row

'find sixth empty row in database
lRow6 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(6, 0).Row

'find seventh empty row in database
lRow7 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(7, 0).Row

'find eighth empty row in database
lRow8 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(8, 0).Row

'find nineth empty row in database
lRow9 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(9, 0).Row
'find tenth empty row in database
lRow10 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(10, 0).Row

'find eleven empty row in database
lRow11 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(11, 0).Row

'find twelve empty row in database
lRow12 = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(12, 0).Row

lPart = Me.cboProduct.ListIndex
lPart2 = Me.cboProduct2.ListIndex
lPart3 = Me.cboProduct3.ListIndex
lPart4 = Me.cboProduct4.ListIndex
lPart5 = Me.cboProduct5.ListIndex
lPart6 = Me.cboProduct6.ListIndex
lPart7 = Me.cboProduct7.ListIndex
lpart8 = Me.cboProduct8.ListIndex
lPart9 = Me.cboProduct9.ListIndex
lPart10 = Me.cboProduct10.ListIndex
lPart11 = Me.cboProduct11.ListIndex
lPart12 = Me.cboProduct12.ListIndex

'check for a part number
If Trim(Me.cboProduct.Value) = "" Then
Me.cboProduct.SetFocus
MsgBox "Please enter a product number"
Exit Sub
End If
'copy the data to the database
With ws
If Me.cboProduct.ListIndex -1 Then
.Cells(lRow, 1).Value = Me.txtDate.Value
.Cells(lRow, 2).Value = Me.txtDocketNo.Value
.Cells(lRow, 3).Value = Me.cboProduct.Value
.Cells(lRow, 4).Value = Me.cboProduct.List(lPart, 1)
.Cells(lRow, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow, 6).Value = Me.cboLocationTo.Value
.Cells(lRow, 7).Value = Me.txtPalletIn.Value
.Cells(lRow, 8).Value = Me.txtCaseIn.Value
.Cells(lRow, 9).Value = Me.txtBtlIn.Value
.Cells(lRow, 10).Value = Me.txtPackPlt.Value
.Cells(lRow, 11).Value = Me.txtPackCase.Value
.Cells(lRow, 12).Value = Me.txtPackBtl.Value
.Cells(lRow, 13).Value = Me.txtPltOut.Value
.Cells(lRow, 14).Value = Me.txtCaseOut.Value
.Cells(lRow, 15).Value = Me.txtBtlOut.Value
.Cells(lRow, 16).Value = Me.txtPickPlt.Value
.Cells(lRow, 17).Value = Me.txtPickCase.Value
.Cells(lRow, 18).Value = Me.txtPickBtl.Value
.Cells(lRow, 19).Value = Me.cboCarrier.Value
.Cells(lRow, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow, 22).Value = Me.txtLabourHours.Value
.Cells(lRow, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct2.ListIndex -1 Then
.Cells(lRow2, 1).Value = Me.txtDate.Value
.Cells(lRow2, 2).Value = Me.txtDocketNo.Value
.Cells(lRow2, 3).Value = Me.cboProduct2.Value
.Cells(lRow2, 4).Value = Me.cboProduct2.List(lPart2, 1)
.Cells(lRow2, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow2, 6).Value = Me.cboLocationTo.Value
.Cells(lRow2, 7).Value = Me.txtPalletIn2.Value
.Cells(lRow2, 8).Value = Me.txtCaseIn2.Value
.Cells(lRow2, 9).Value = Me.txtBtlIn2.Value
.Cells(lRow2, 10).Value = Me.txtPackPlt2.Value
.Cells(lRow2, 11).Value = Me.txtPackCase2.Value
.Cells(lRow2, 12).Value = Me.txtPackBtl2.Value
.Cells(lRow2, 13).Value = Me.txtPltOut2.Value
.Cells(lRow2, 14).Value = Me.txtCaseOut2.Value
.Cells(lRow2, 15).Value = Me.txtBtlOut2.Value
.Cells(lRow2, 16).Value = Me.txtPickPlt2.Value
.Cells(lRow2, 17).Value = Me.txtPickCase2.Value
.Cells(lRow2, 18).Value = Me.txtPickBtl2.Value
.Cells(lRow2, 19).Value = Me.cboCarrier.Value
.Cells(lRow2, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow2, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow2, 22).Value = Me.txtLabourHours.Value
.Cells(lRow2, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct3.ListIndex -1 Then
.Cells(lRow3, 1).Value = Me.txtDate.Value
.Cells(lRow3, 2).Value = Me.txtDocketNo.Value
.Cells(lRow3, 3).Value = Me.cboProduct3.Value
.Cells(lRow3, 4).Value = Me.cboProduct3.List(lPart3, 1)
.Cells(lRow3, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow3, 6).Value = Me.cboLocationTo.Value
.Cells(lRow3, 7).Value = Me.txtPalletIn3.Value
.Cells(lRow3, 8).Value = Me.txtCaseIn3.Value
.Cells(lRow3, 9).Value = Me.txtBtlIn3.Value
.Cells(lRow3, 10).Value = Me.txtPackPlt3.Value
.Cells(lRow3, 11).Value = Me.txtPackCase3.Value
.Cells(lRow3, 12).Value = Me.txtPackBtl3.Value
.Cells(lRow3, 13).Value = Me.txtPltOut3.Value
.Cells(lRow3, 14).Value = Me.txtCaseOut3.Value
.Cells(lRow3, 15).Value = Me.txtBtlOut3.Value
.Cells(lRow3, 16).Value = Me.txtPickPlt3.Value
.Cells(lRow3, 17).Value = Me.txtPickCase3.Value
.Cells(lRow3, 18).Value = Me.txtPickBtl3.Value
.Cells(lRow3, 19).Value = Me.cboCarrier.Value
.Cells(lRow3, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow3, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow3, 22).Value = Me.txtLabourHours.Value
.Cells(lRow3, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct4.ListIndex -1 Then
.Cells(lRow4, 1).Value = Me.txtDate.Value
.Cells(lRow4, 2).Value = Me.txtDocketNo.Value
.Cells(lRow4, 3).Value = Me.cboProduct4.Value
.Cells(lRow4, 4).Value = Me.cboProduct4.List(lPart4, 1)
.Cells(lRow4, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow4, 6).Value = Me.cboLocationTo.Value
.Cells(lRow4, 7).Value = Me.txtPalletIn4.Value
.Cells(lRow4, 8).Value = Me.txtCaseIn4.Value
.Cells(lRow4, 9).Value = Me.txtBtlIn4.Value
.Cells(lRow4, 10).Value = Me.txtPackPlt4.Value
.Cells(lRow4, 11).Value = Me.txtPackCase4.Value
.Cells(lRow4, 12).Value = Me.txtPackBtl4.Value
.Cells(lRow4, 13).Value = Me.txtPltOut4.Value
.Cells(lRow4, 14).Value = Me.txtCaseOut4.Value
.Cells(lRow4, 15).Value = Me.txtBtlOut4.Value
.Cells(lRow4, 16).Value = Me.txtPickPlt4.Value
.Cells(lRow4, 17).Value = Me.txtPickCase4.Value
.Cells(lRow4, 18).Value = Me.txtPickBtl4.Value
.Cells(lRow4, 19).Value = Me.cboCarrier.Value
.Cells(lRow4, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow4, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow4, 22).Value = Me.txtLabourHours.Value
.Cells(lRow4, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct5.ListIndex -1 Then
.Cells(lRow5, 1).Value = Me.txtDate.Value
.Cells(lRow5, 2).Value = Me.txtDocketNo.Value
.Cells(lRow5, 3).Value = Me.cboProduct5.Value
.Cells(lRow5, 4).Value = Me.cboProduct5.List(lPart5, 1)
.Cells(lRow5, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow5, 6).Value = Me.cboLocationTo.Value
.Cells(lRow5, 7).Value = Me.txtPalletIn5.Value
.Cells(lRow5, 8).Value = Me.txtCaseIn5.Value
.Cells(lRow5, 9).Value = Me.txtBtlIn5.Value
.Cells(lRow5, 10).Value = Me.txtPackPlt5.Value
.Cells(lRow5, 11).Value = Me.txtPackCase5.Value
.Cells(lRow5, 12).Value = Me.txtPackBtl5.Value
.Cells(lRow5, 13).Value = Me.txtPltOut5.Value
.Cells(lRow5, 14).Value = Me.txtCaseOut5.Value
.Cells(lRow5, 15).Value = Me.txtBtlOut5.Value
.Cells(lRow5, 16).Value = Me.txtPickPlt5.Value
.Cells(lRow5, 17).Value = Me.txtPickCase5.Value
.Cells(lRow5, 18).Value = Me.txtPickBtl5.Value
.Cells(lRow5, 19).Value = Me.cboCarrier.Value
.Cells(lRow5, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow5, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow5, 22).Value = Me.txtLabourHours.Value
.Cells(lRow5, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct6.ListIndex -1 Then
.Cells(lRow6, 1).Value = Me.txtDate.Value
.Cells(lRow6, 2).Value = Me.txtDocketNo.Value
.Cells(lRow6, 3).Value = Me.cboProduct6.Value
.Cells(lRow6, 4).Value = Me.cboProduct6.List(lPart6, 1)
.Cells(lRow6, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow6, 6).Value = Me.cboLocationTo.Value
.Cells(lRow6, 7).Value = Me.txtPalletIn6.Value
.Cells(lRow6, 8).Value = Me.txtCaseIn6.Value
.Cells(lRow6, 9).Value = Me.txtBtlIn6.Value
.Cells(lRow6, 10).Value = Me.txtPackPlt6.Value
.Cells(lRow6, 11).Value = Me.txtPackCase6.Value
.Cells(lRow6, 12).Value = Me.txtPackBtl6.Value
.Cells(lRow6, 13).Value = Me.txtPltOut6.Value
.Cells(lRow6, 14).Value = Me.txtCaseOut6.Value
.Cells(lRow6, 15).Value = Me.txtBtlOut6.Value
.Cells(lRow6, 16).Value = Me.txtPickPlt6.Value
.Cells(lRow6, 17).Value = Me.txtPickCase6.Value
.Cells(lRow6, 18).Value = Me.txtPickBtl6.Value
.Cells(lRow6, 19).Value = Me.cboCarrier.Value
.Cells(lRow6, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow6, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow6, 22).Value = Me.txtLabourHours.Value
.Cells(lRow6, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct7.ListIndex -1 Then
.Cells(lRow7, 1).Value = Me.txtDate.Value
.Cells(lRow7, 2).Value = Me.txtDocketNo.Value
.Cells(lRow7, 3).Value = Me.cboProduct7.Value
.Cells(lRow7, 4).Value = Me.cboProduct7.List(lPart7, 1)
.Cells(lRow7, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow7, 6).Value = Me.cboLocationTo.Value
.Cells(lRow7, 7).Value = Me.txtPalletIn7.Value
.Cells(lRow7, 8).Value = Me.txtCaseIn7.Value
.Cells(lRow7, 9).Value = Me.txtBtlIn7.Value
.Cells(lRow7, 10).Value = Me.txtPackPlt7.Value
.Cells(lRow7, 11).Value = Me.txtPackCase7.Value
.Cells(lRow7, 12).Value = Me.txtPackBtl7.Value
.Cells(lRow7, 13).Value = Me.txtPltOut7.Value
.Cells(lRow7, 14).Value = Me.txtCaseOut7.Value
.Cells(lRow7, 15).Value = Me.txtBtlOut7.Value
.Cells(lRow7, 16).Value = Me.txtPickPlt7.Value
.Cells(lRow7, 17).Value = Me.txtPickCase7.Value
.Cells(lRow7, 18).Value = Me.txtPickBtl7.Value
.Cells(lRow7, 19).Value = Me.cboCarrier.Value
.Cells(lRow7, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow7, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow7, 22).Value = Me.txtLabourHours.Value
.Cells(lRow7, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct8.ListIndex -1 Then
.Cells(lRow8, 1).Value = Me.txtDate.Value
.Cells(lRow8, 2).Value = Me.txtDocketNo.Value
.Cells(lRow8, 3).Value = Me.cboProduct8.Value
.Cells(lRow8, 4).Value = Me.cboProduct8.List(lpart8, 1)
.Cells(lRow8, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow8, 6).Value = Me.cboLocationTo.Value
.Cells(lRow8, 7).Value = Me.txtPalletIn8.Value
.Cells(lRow8, 8).Value = Me.txtCaseIn8.Value
.Cells(lRow8, 9).Value = Me.txtBtlIn8.Value
.Cells(lRow8, 10).Value = Me.txtPackPlt8.Value
.Cells(lRow8, 11).Value = Me.txtPackCase8.Value
.Cells(lRow8, 12).Value = Me.txtPackBtl8.Value
.Cells(lRow8, 13).Value = Me.txtPltOut8.Value
.Cells(lRow8, 14).Value = Me.txtCaseOut8.Value
.Cells(lRow8, 15).Value = Me.txtBtlOut8.Value
.Cells(lRow8, 16).Value = Me.txtPickPlt8.Value
.Cells(lRow8, 17).Value = Me.txtPickCase8.Value
.Cells(lRow8, 18).Value = Me.txtPickBtl8.Value
.Cells(lRow8, 19).Value = Me.cboCarrier.Value
.Cells(lRow8, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow8, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow8, 22).Value = Me.txtLabourHours.Value
.Cells(lRow8, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct9.ListIndex -1 Then
.Cells(lRow9, 1).Value = Me.txtDate.Value
.Cells(lRow9, 2).Value = Me.txtDocketNo.Value
.Cells(lRow9, 3).Value = Me.cboProduct9.Value
.Cells(lRow9, 4).Value = Me.cboProduct9.List(lPart9, 1)
.Cells(lRow9, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow9, 6).Value = Me.cboLocationTo.Value
.Cells(lRow9, 7).Value = Me.txtPalletIn9.Value
.Cells(lRow9, 8).Value = Me.txtCaseIn9.Value
.Cells(lRow9, 9).Value = Me.txtBtlIn9.Value
.Cells(lRow9, 10).Value = Me.txtPackPlt9.Value
.Cells(lRow9, 11).Value = Me.txtPackCase9.Value
.Cells(lRow9, 12).Value = Me.txtPackBtl9.Value
.Cells(lRow9, 13).Value = Me.txtPltOut9.Value
.Cells(lRow9, 14).Value = Me.txtCaseOut9.Value
.Cells(lRow9, 15).Value = Me.txtBtlOut9.Value
.Cells(lRow9, 16).Value = Me.txtPickPlt9.Value
.Cells(lRow9, 17).Value = Me.txtPickCase9.Value
.Cells(lRow9, 18).Value = Me.txtPickBtl9.Value
.Cells(lRow9, 19).Value = Me.cboCarrier.Value
.Cells(lRow9, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow9, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow9, 22).Value = Me.txtLabourHours.Value
.Cells(lRow9, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct10.ListIndex -1 Then
.Cells(lRow10, 1).Value = Me.txtDate.Value
.Cells(lRow10, 2).Value = Me.txtDocketNo.Value
.Cells(lRow10, 3).Value = Me.cboProduct10.Value
.Cells(lRow10, 4).Value = Me.cboProduct10.List(lPart10, 1)
.Cells(lRow10, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow10, 6).Value = Me.cboLocationTo.Value
.Cells(lRow10, 7).Value = Me.txtPalletIn10.Value
.Cells(lRow10, 8).Value = Me.txtCaseIn10.Value
.Cells(lRow10, 9).Value = Me.txtBtlIn10.Value
.Cells(lRow10, 10).Value = Me.txtPackPlt10.Value
.Cells(lRow10, 11).Value = Me.txtPackCase10.Value
.Cells(lRow10, 12).Value = Me.txtPackBtl10.Value
.Cells(lRow10, 13).Value = Me.txtPltOut10.Value
.Cells(lRow10, 14).Value = Me.txtCaseOut10.Value
.Cells(lRow10, 15).Value = Me.txtBtlOut10.Value
.Cells(lRow10, 16).Value = Me.txtPickPlt10.Value
.Cells(lRow10, 17).Value = Me.txtPickCase10.Value
.Cells(lRow10, 18).Value = Me.txtPickBtl10.Value
.Cells(lRow10, 19).Value = Me.cboCarrier.Value
.Cells(lRow10, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow10, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow10, 22).Value = Me.txtLabourHours.Value
.Cells(lRow10, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct11.ListIndex -1 Then
.Cells(lRow11, 1).Value = Me.txtDate.Value
.Cells(lRow11, 2).Value = Me.txtDocketNo.Value
.Cells(lRow11, 3).Value = Me.cboProduct11.Value
.Cells(lRow11, 4).Value = Me.cboProduct11.List(lPart11, 1)
.Cells(lRow11, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow11, 6).Value = Me.cboLocationTo.Value
.Cells(lRow11, 7).Value = Me.txtPalletIn11.Value
.Cells(lRow11, 8).Value = Me.txtCaseIn11.Value
.Cells(lRow11, 9).Value = Me.txtBtlIn11.Value
.Cells(lRow11, 10).Value = Me.txtPackPlt11.Value
.Cells(lRow11, 11).Value = Me.txtPackCase11.Value
.Cells(lRow11, 12).Value = Me.txtPackBtl11.Value
.Cells(lRow11, 13).Value = Me.txtPltOut11.Value
.Cells(lRow11, 14).Value = Me.txtCaseOut11.Value
.Cells(lRow11, 15).Value = Me.txtBtlOut11.Value
.Cells(lRow11, 16).Value = Me.txtPickPlt11.Value
.Cells(lRow11, 17).Value = Me.txtPickCase11.Value
.Cells(lRow11, 18).Value = Me.txtPickBtl11.Value
.Cells(lRow11, 19).Value = Me.cboCarrier.Value
.Cells(lRow11, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow11, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow11, 22).Value = Me.txtLabourHours.Value
.Cells(lRow11, 34).Value = Me.txtCustomer.Value
End If
If Me.cboProduct12.ListIndex -1 Then
.Cells(lRow12, 1).Value = Me.txtDate.Value
.Cells(lRow12, 2).Value = Me.txtDocketNo.Value
.Cells(lRow12, 3).Value = Me.cboProduct12.Value
.Cells(lRow12, 4).Value = Me.cboProduct12.List(lPart12, 1)
.Cells(lRow12, 5).Value = Me.cboLocationFrom.Value
.Cells(lRow12, 6).Value = Me.cboLocationTo.Value
.Cells(lRow12, 7).Value = Me.txtPalletIn12.Value
.Cells(lRow12, 8).Value = Me.txtCaseIn12.Value
.Cells(lRow12, 9).Value = Me.txtBtlIn12.Value
.Cells(lRow12, 10).Value = Me.txtPackPlt12.Value
.Cells(lRow12, 11).Value = Me.txtPackCase12.Value
.Cells(lRow12, 12).Value = Me.txtPackBtl12.Value
.Cells(lRow12, 13).Value = Me.txtPltOut12.Value
.Cells(lRow12, 14).Value = Me.txtCaseOut12.Value
.Cells(lRow12, 15).Value = Me.txtBtlOut12.Value
.Cells(lRow12, 16).Value = Me.txtPickPlt12.Value
.Cells(lRow12, 17).Value = Me.txtPickCase12.Value
.Cells(lRow12, 18).Value = Me.txtPickBtl12.Value
.Cells(lRow12, 19).Value = Me.cboCarrier.Value
.Cells(lRow12, 20).Value = Me.txtOtherInfo.Value
.Cells(lRow12, 21).Value = Me.txtDeliveryHours.Value
.Cells(lRow12, 22).Value = Me.txtLabourHours.Value
.Cells(lRow12, 34).Value = Me.txtCustomer.Value
End If
End With
'clear the data
Me.cboProduct.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.cboCarrier.Value = ""
Me.txtDate.Value = Format(Date, "dd/mm/yyyy")
Me.txtPalletIn.Value = ""
Me.txtCaseIn.Value = ""
Me.txtBtlIn.Value = ""
Me.txtPackPlt.Value = ""
Me.txtPackCase.Value = ""
Me.txtPackBtl.Value = ""
Me.txtPltOut.Value = ""
Me.txtCaseOut.Value = ""
Me.txtBtlOut.Value = ""
Me.txtPickPlt.Value = ""
Me.txtPickCase.Value = ""
Me.txtPickBtl.Value = ""
Me.cboProduct2.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn2.Value = ""
Me.txtCaseIn2.Value = ""
Me.txtBtlIn2.Value = ""
Me.txtPackPlt2.Value = ""
Me.txtPackCase2.Value = ""
Me.txtPackBtl2.Value = ""
Me.txtPltOut2.Value = ""
Me.txtCaseOut2.Value = ""
Me.txtBtlOut2.Value = ""
Me.txtPickPlt2.Value = ""
Me.txtPickCase2.Value = ""
Me.txtPickBtl2.Value = ""
Me.cboProduct3.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn3.Value = ""
Me.txtCaseIn3.Value = ""
Me.txtBtlIn3.Value = ""
Me.txtPackPlt3.Value = ""
Me.txtPackCase3.Value = ""
Me.txtPackBtl3.Value = ""
Me.txtPltOut3.Value = ""
Me.txtCaseOut3.Value = ""
Me.txtBtlOut3.Value = ""
Me.txtPickPlt3.Value = ""
Me.txtPickCase3.Value = ""
Me.txtPickBtl3.Value = ""
Me.cboProduct4.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn4.Value = ""
Me.txtCaseIn4.Value = ""
Me.txtBtlIn4.Value = ""
Me.txtPackPlt4.Value = ""
Me.txtPackCase4.Value = ""
Me.txtPackBtl4.Value = ""
Me.txtPltOut4.Value = ""
Me.txtCaseOut4.Value = ""
Me.txtBtlOut4.Value = ""
Me.txtPickPlt4.Value = ""
Me.txtPickCase4.Value = ""
Me.txtPickBtl4.Value = ""
Me.cboProduct5.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn5.Value = ""
Me.txtCaseIn5.Value = ""
Me.txtBtlIn5.Value = ""
Me.txtPackPlt5.Value = ""
Me.txtPackCase5.Value = ""
Me.txtPackBtl5.Value = ""
Me.txtPltOut5.Value = ""
Me.txtCaseOut5.Value = ""
Me.txtBtlOut5.Value = ""
Me.txtPickPlt5.Value = ""
Me.txtPickCase5.Value = ""
Me.txtPickBtl5.Value = ""
Me.cboProduct6.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn6.Value = ""
Me.txtCaseIn6.Value = ""
Me.txtBtlIn6.Value = ""
Me.txtPackPlt6.Value = ""
Me.txtPackCase6.Value = ""
Me.txtPackBtl6.Value = ""
Me.txtPltOut6.Value = ""
Me.txtCaseOut6.Value = ""
Me.txtBtlOut6.Value = ""
Me.txtPickPlt6.Value = ""
Me.txtPickCase6.Value = ""
Me.txtPickBtl6.Value = ""
Me.cboProduct7.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.cboCarrier.Value = ""
Me.txtDate.Value = Format(Date, "dd/mm/yyyy")
Me.txtPalletIn7.Value = ""
Me.txtCaseIn7.Value = ""
Me.txtBtlIn7.Value = ""
Me.txtPackPlt7.Value = ""
Me.txtPackCase7.Value = ""
Me.txtPackBtl7.Value = ""
Me.txtPltOut7.Value = ""
Me.txtCaseOut7.Value = ""
Me.txtBtlOut7.Value = ""
Me.txtPickPlt7.Value = ""
Me.txtPickCase7.Value = ""
Me.txtPickBtl7.Value = ""
Me.cboProduct8.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn8.Value = ""
Me.txtCaseIn8.Value = ""
Me.txtBtlIn8.Value = ""
Me.txtPackPlt8.Value = ""
Me.txtPackCase8.Value = ""
Me.txtPackBtl8.Value = ""
Me.txtPltOut8.Value = ""
Me.txtCaseOut8.Value = ""
Me.txtBtlOut8.Value = ""
Me.txtPickPlt8.Value = ""
Me.txtPickCase8.Value = ""
Me.txtPickBtl8.Value = ""
Me.cboProduct9.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn9.Value = ""
Me.txtCaseIn9.Value = ""
Me.txtBtlIn9.Value = ""
Me.txtPackPlt9.Value = ""
Me.txtPackCase9.Value = ""
Me.txtPackBtl9.Value = ""
Me.txtPltOut9.Value = ""
Me.txtCaseOut9.Value = ""
Me.txtBtlOut9.Value = ""
Me.txtPickPlt9.Value = ""
Me.txtPickCase9.Value = ""
Me.txtPickBtl9.Value = ""
Me.cboProduct10.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn10.Value = ""
Me.txtCaseIn10.Value = ""
Me.txtBtlIn10.Value = ""
Me.txtPackPlt10.Value = ""
Me.txtPackCase10.Value = ""
Me.txtPackBtl10.Value = ""
Me.txtPltOut10.Value = ""
Me.txtCaseOut10.Value = ""
Me.txtBtlOut10.Value = ""
Me.txtPickPlt10.Value = ""
Me.txtPickCase10.Value = ""
Me.txtPickBtl10.Value = ""
Me.cboProduct11.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn11.Value = ""
Me.txtCaseIn11.Value = ""
Me.txtBtlIn11.Value = ""
Me.txtPackPlt11.Value = ""
Me.txtPackCase11.Value = ""
Me.txtPackBtl11.Value = ""
Me.txtPltOut11.Value = ""
Me.txtCaseOut11.Value = ""
Me.txtBtlOut11.Value = ""
Me.txtPickPlt11.Value = ""
Me.txtPickCase11.Value = ""
Me.txtPickBtl11.Value = ""
Me.cboProduct12.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.txtPalletIn12.Value = ""
Me.txtCaseIn12.Value = ""
Me.txtBtlIn12.Value = ""
Me.txtPackPlt12.Value = ""
Me.txtPackCase12.Value = ""
Me.txtPackBtl12.Value = ""
Me.txtPltOut12.Value = ""
Me.txtCaseOut12.Value = ""
Me.txtBtlOut12.Value = ""
Me.txtPickPlt12.Value = ""
Me.txtPickCase12.Value = ""
Me.txtPickBtl12.Value = ""
Me.txtDocketNo.Value = ""
Me.txtOtherInfo.Value = ""
Me.txtDeliveryHours.Value = ""
Me.txtLabourHours.Value = ""
Me.txtCustomer.Value = ""
Me.txtDate.SetFocus
End Sub
Private Sub cmdCLOSE_click()
Unload Me
End Sub
Private Sub Label1_Click()
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub ComboBox2_Change()
End Sub
Private Sub ComboBox9_Change()
End Sub
Private Sub Label7_Click()
End Sub
Private Sub txtDate_Change()
Me.txtDate.Value = Format(Date, "dd/mm/yyyy")
End Sub
Private Sub UserForm_initialize()
Dim cPart As Range
Dim cpart2 As Range
Dim cPart3 As Range
Dim cPart4 As Range
Dim cpart5 As Range
Dim cPart6 As Range
Dim cPart7 As Range
Dim cpart8 As Range
Dim cPart9 As Range
Dim cPart10 As Range
Dim cpart11 As Range
Dim cPart12 As Range
Dim cLoc As Range
Dim cCarr As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cPart In ws.Range("PartIDList")
With Me.cboProduct
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
For Each cpart2 In ws.Range("PartIDList")
With Me.cboProduct2
.AddItem cpart2.Value
.List(.ListCount - 1, 1) = cpart2.Offset(0, 1).Value
End With
Next cpart2
For Each cPart3 In ws.Range("PartIDList")
With Me.cboProduct3
.AddItem cPart3.Value
.List(.ListCount - 1, 1) = cPart3.Offset(0, 1).Value
End With
Next cPart3
For Each cPart4 In ws.Range("PartIDList")
With Me.cboProduct4
.AddItem cPart4.Value
.List(.ListCount - 1, 1) = cPart4.Offset(0, 1).Value
End With
Next cPart4
For Each cpart5 In ws.Range("PartIDList")
With Me.cboProduct5
.AddItem cpart5.Value
.List(.ListCount - 1, 1) = cpart5.Offset(0, 1).Value
End With
Next cpart5
For Each cPart6 In ws.Range("PartIDList")
With Me.cboProduct6
.AddItem cPart6.Value
.List(.ListCount - 1, 1) = cPart6.Offset(0, 1).Value
End With
Next cPart6
For Each cPart7 In ws.Range("PartIDList")
With Me.cboProduct7
.AddItem cPart7.Value
.List(.ListCount - 1, 1) = cPart7.Offset(0, 1).Value
End With
Next cPart7
For Each cpart8 In ws.Range("PartIDList")
With Me.cboProduct8
.AddItem cpart8.Value
.List(.ListCount - 1, 1) = cpart8.Offset(0, 1).Value
End With
Next cpart8
For Each cPart9 In ws.Range("PartIDList")
With Me.cboProduct9
.AddItem cPart9.Value
.List(.ListCount - 1, 1) = cPart9.Offset(0, 1).Value
End With
Next cPart9
For Each cPart10 In ws.Range("PartIDList")
With Me.cboProduct10
.AddItem cPart10.Value
.List(.ListCount - 1, 1) = cPart10.Offset(0, 1).Value
End With
Next cPart10
For Each cpart11 In ws.Range("PartIDList")
With Me.cboProduct11
.AddItem cpart11.Value
.List(.ListCount - 1, 1) = cpart11.Offset(0, 1).Value
End With
Next cpart11
For Each cPart12 In ws.Range("PartIDList")
With Me.cboProduct12
.AddItem cPart12.Value
.List(.ListCount - 1, 1) = cPart12.Offset(0, 1).Value
End With
Next cPart12
For Each cLoc In ws.Range("LocationList")
With Me.cboLocationFrom
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc
For Each cLoc In ws.Range("LocationList")
With Me.cboLocationTo
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc
For Each cCarr In ws.Range("CarrierList")
With Me.cboCarrier
.AddItem cCarr.Value
.List(.ListCount - 1, 1) = cCarr.Offset(0, 1).Value
End With
Next cCarr
Me.txtDate.Value = Format(Date, "dd/mm/yyyy")
Me.txtDocketNo.Value = ""
Me.cboLocationFrom.Value = ""
Me.cboLocationTo.Value = ""
Me.cboCarrier.Value = ""
Me.txtPalletIn.Value = ""
Me.txtCaseIn.Value = ""
Me.txtBtlIn.Value = ""
Me.txtPackPlt.Value = ""
Me.txtPackCase.Value = ""
Me.txtPackBtl.Value = ""
Me.txtPltOut.Value = ""
Me.txtCaseOut.Value = ""
Me.txtBtlOut.Value = ""
Me.txtPickPlt.Value = ""
Me.txtPickCase.Value = ""
Me.txtPickBtl.Value = ""
Me.txtPalletIn2.Value = ""
Me.txtCaseIn2.Value = ""
Me.txtBtlIn2.Value = ""
Me.txtPackPlt2.Value = ""
Me.txtPackCase2.Value = ""
Me.txtPackBtl2.Value = ""
Me.txtPltOut2.Value = ""
Me.txtCaseOut2.Value = ""
Me.txtBtlOut2.Value = ""
Me.txtPickPlt2.Value = ""
Me.txtPickCase2.Value = ""
Me.txtPickBtl2.Value = ""
Me.txtDeliveryHours.Value = ""
Me.txtLabourHours.Value = ""
Me.txtOtherInfo.Value = ""
Me.txtDate.SetFocus
End Sub

Use .Cells(lRow, 1).Value = CDate(Me.txtDate.Value)

-- Hope this helps.
Doug Robbins - Word MVP,
dkr[atsymbol]mvps[dot]org
Posted via the Community Bridge
"SuperDe" wrote in message news:*** Email address is removed for privacy ***...
Geoff, Thanks for your suggestion, but the regional setting is correct.

Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

Ola Preciso separar Responsavel com seus beneficiarios de um datagridview e colocar em um textbox, só que quando separo, ocorre duplicação de alguns registros
meu código

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Adicionar colunas
With DataGridView1

.Columns.Add(CPF, CPF)
.Columns(0).Width = 150
.Columns.Add(NomeResponsavel, NomeResponsavel)
.Columns(1).Width = 190
.Columns.Add(NomeCrianca, NomeCrianca)
.Columns(2).Width = 190

End With
' Adicionando linhas
DataGridView1.Rows.Add(10)

'Adicionando dados ao grid
DataGridView1.Rows(0).Cells(0).Value = 02398979784
DataGridView1.Rows(1).Cells(0).Value = 02398979784
DataGridView1.Rows(2).Cells(0).Value = 02476853640
DataGridView1.Rows(3).Cells(0).Value = 02772956962
DataGridView1.Rows(4).Cells(0).Value = 03244072704
DataGridView1.Rows(5).Cells(0).Value = 03244072704
DataGridView1.Rows(6).Cells(0).Value = 03244072704
DataGridView1.Rows(7).Cells(0).Value = 03614306801
DataGridView1.Rows(8).Cells(0).Value = 04259946609
DataGridView1.Rows(9).Cells(0).Value = 04259946609
DataGridView1.Rows(10).Cells(0).Value = 05402838860

DataGridView1.Rows(0).Cells(1).Value = Marcos Vilela
DataGridView1.Rows(1).Cells(1).Value = Marcos Vilela
DataGridView1.Rows(2).Cells(1).Value = Pedro Nunes
DataGridView1.Rows(3).Cells(1).Value = Fernanda Macedo
DataGridView1.Rows(4).Cells(1).Value = RAFAEL LIMA CAVALIERI TEIXEIRA
DataGridView1.Rows(5).Cells(1).Value = RAFAEL LIMA CAVALIERI TEIXEIRA
DataGridView1.Rows(6).Cells(1).Value = RAFAEL LIMA CAVALIERI TEIXEIRA
DataGridView1.Rows(7).Cells(1).Value = Enrico Fauze
DataGridView1.Rows(8).Cells(1).Value = Monica Chicarino
DataGridView1.Rows(9).Cells(1).Value = Monica Chicarino
DataGridView1.Rows(10).Cells(1).Value = GUSTAVO ZANARDI

DataGridView1.Rows(0).Cells(2).Value = Fernando Vilela
DataGridView1.Rows(1).Cells(2).Value = Patricia Vilela
DataGridView1.Rows(2).Cells(2).Value = Rafael Nunes
DataGridView1.Rows(3).Cells(2).Value = Marcela Macedo
DataGridView1.Rows(4).Cells(2).Value = Vitor Cavalieri Teixeira
DataGridView1.Rows(5).Cells(2).Value = Franciana Cavalieri Teixeira
DataGridView1.Rows(6).Cells(2).Value = Jair Cavalieri Teixeira
DataGridView1.Rows(7).Cells(2).Value = Marcos Fauze
DataGridView1.Rows(8).Cells(2).Value = Greta Chicarino
DataGridView1.Rows(9).Cells(2).Value = Adam Chicarino
DataGridView1.Rows(10).Cells(2).Value = Olavo Zanardi

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim CPF, NomeResponsavel, NomeCrianca As String

For w = 0 To DataGridView1.Rows.Count - 1

CPF = DataGridView1.Rows(w).Cells(0).Value.ToString.Trim()
NomeResponsavel = DataGridView1.Rows(w).Cells(1).Value.ToString.Trim()
NomeCrianca = DataGridView1.Rows(w).Cells(2).Value.ToString.Trim()

TextBox1.Text = RESPONSAVEL: NomeResponsavel _
vbNewLine _
CPF: CPF vbNewLine

For L = 0 To DataGridView1.Rows.Count - 1

If CPF = DataGridView1.Rows(L).Cells(0).Value Then
TextBox1.Text = TextBox1.Text _
BENEFICIARIO: NomeCrianca vbNewLine

End If

Next

TextBox1.Text = vbNewLine

Next

End Sub

E faz isso

E o que eu quero seria isso

Obrigada

Coloquei
Me.dtn = dtn
No Friend sub new,
Mas a data de nascimento fica como fosse um nome de beneficiario. Eu gostaria de colocar antes do nome do beneficiario.
Tipo

Imports System.Windows.Forms
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Text
Imports System.Text.RegularExpressions

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Adicionar colunas
With DataGridView1

.Columns.Add(CPF, CPF)
.Columns(0).Width = 150
.Columns.Add(NomeResponsavel, NomeResponsavel)
.Columns(1).Width = 190
.Columns.Add(NomeCrianca, NomeCrianca)
.Columns(2).Width = 190
.Columns.Add(dtn, dtn)
.Columns(2).Width = 150

End With
' Adicionando linhas
DataGridView1.Rows.Add(10)

'Adicionando dados ao grid
DataGridView1.Rows(0).Cells(0).Value = 02398979784
DataGridView1.Rows(1).Cells(0).Value = 02398979784
DataGridView1.Rows(2).Cells(0).Value = 02476853640
DataGridView1.Rows(3).Cells(0).Value = 02772956962
DataGridView1.Rows(4).Cells(0).Value = 03244072704
DataGridView1.Rows(5).Cells(0).Value = 03244072704
DataGridView1.Rows(6).Cells(0).Value = 03244072704
DataGridView1.Rows(7).Cells(0).Value = 03614306801
DataGridView1.Rows(8).Cells(0).Value = 04259946609
DataGridView1.Rows(9).Cells(0).Value = 04259946609
DataGridView1.Rows(10).Cells(0).Value = 05402838860

DataGridView1.Rows(0).Cells(1).Value = Marcos Vilela
DataGridView1.Rows(1).Cells(1).Value = Marcos Vilela
DataGridView1.Rows(2).Cells(1).Value = Pedro Nunes
DataGridView1.Rows(3).Cells(1).Value = Fernanda Macedo
DataGridView1.Rows(4).Cells(1).Value = RAFAEL LIMA CAVALIERI TEIXEIRA
DataGridView1.Rows(5).Cells(1).Value = RAFAEL LIMA CAVALIERI TEIXEIRA
DataGridView1.Rows(6).Cells(1).Value = RAFAEL LIMA CAVALIERI TEIXEIRA
DataGridView1.Rows(7).Cells(1).Value = Enrico Fauze
DataGridView1.Rows(8).Cells(1).Value = Monica Chicarino
DataGridView1.Rows(9).Cells(1).Value = Monica Chicarino
DataGridView1.Rows(10).Cells(1).Value = GUSTAVO ZANARDI

DataGridView1.Rows(0).Cells(2).Value = Fernando Vilela
DataGridView1.Rows(1).Cells(2).Value = Patricia Vilela
DataGridView1.Rows(2).Cells(2).Value = Rafael Nunes
DataGridView1.Rows(3).Cells(2).Value = Marcela Macedo
DataGridView1.Rows(4).Cells(2).Value = Vitor Cavalieri Teixeira
DataGridView1.Rows(5).Cells(2).Value = Franciana Cavalieri Teixeira
DataGridView1.Rows(6).Cells(2).Value = Jair Cavalieri Teixeira
DataGridView1.Rows(7).Cells(2).Value = Marcos Fauze
DataGridView1.Rows(8).Cells(2).Value = Greta Chicarino
DataGridView1.Rows(9).Cells(2).Value = Adam Chicarino
DataGridView1.Rows(10).Cells(2).Value = Olavo Zanardi

DataGridView1.Rows(0).Cells(3).Value = 01/10/2011
DataGridView1.Rows(1).Cells(3).Value = 01/10/2011
DataGridView1.Rows(2).Cells(3).Value = 01/10/2011
DataGridView1.Rows(3).Cells(3).Value = 01/10/2011
DataGridView1.Rows(4).Cells(3).Value = 01/10/2011
DataGridView1.Rows(5).Cells(3).Value = 01/10/2011
DataGridView1.Rows(6).Cells(3).Value = 01/10/2011
DataGridView1.Rows(7).Cells(3).Value = 01/10/2011
DataGridView1.Rows(8).Cells(3).Value = 01/10/2011
DataGridView1.Rows(9).Cells(3).Value = 01/10/2011
DataGridView1.Rows(10).Cells(3).Value = 01/10/2011

End Sub

Private Class Responsavel
Friend CPF As String
Friend Nome As String
Friend dtn As String
Friend Beneficiarios() As String

Friend Shared Function Procurar(ByRef List As System.Collections.ObjectModel.Collection(Of Responsavel), ByRef CPF As String) As Responsavel
For Each R As Responsavel In List
If R.CPF = CPF Then Return R
Next R
Return Nothing
End Function

Friend Sub Adicionar(ByRef Beneficiario As String)
ReDim Preserve Me.Beneficiarios(0 To Me.Beneficiarios.Length)
Me.Beneficiarios(Me.Beneficiarios.Length - 1) = Beneficiario
End Sub

Friend Sub New(ByRef CPF As String, ByRef Nome As String, ByRef dtn As String, ByRef Beneficiario As String)
Me.CPF = CPF
Me.Nome = Nome
Me.dtn = dtn
Me.Beneficiarios = New String() {Beneficiario}
End Sub
End Class

'CPF, RESPONSAVEL,BENEFICIARIO
Private Sub btnMostrar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMostrar.Click
Dim List As New System.Collections.ObjectModel.Collection(Of Responsavel)
For Each Row As DataGridViewRow In DataGridView1.Rows
Dim Procurado As Responsavel = Responsavel.Procurar(List, CStr(Row.Cells(0).Value))
'Se a Lista Procurado for nulo
If Procurado Is Nothing Then
'Adiciona CPF,RESPONSAVEL,BENEFICIARIO E DATA DE NASCIMENTO
List.Add(New Responsavel(CStr(Row.Cells(0).Value), CStr(Row.Cells(1).Value), CStr(Row.Cells(2).Value), CStr(Row.Cells(3).Value)))
Else
'Senão adiciona beneficiario
Procurado.Adicionar(CStr(Row.Cells(2).Value))
End If
Next Row
Dim Text As String =
For Each R As Responsavel In List
Text = RESPONSAVEL: R.Nome vbNewLine CPF: R.CPF vbNewLine
For Each B As String In R.Beneficiarios
Text = BENEFICIARIO: B vbNewLine
Next B
Next R
TextBox1.Text = Text

End Sub

Private Sub btnExportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click
Dim salvarComo As SaveFileDialog = New SaveFileDialog()
Dim caminho As DialogResult
Dim fluxoTexto As IO.StreamWriter
Dim Arquivo As String
Dim List As New System.Collections.ObjectModel.Collection(Of Responsavel)

For Each Row As DataGridViewRow In DataGridView1.Rows
Dim Procurado As Responsavel = Responsavel.Procurar(List, CStr(Row.Cells(0).Value))
If Procurado Is Nothing Then
List.Add(New Responsavel(CStr(Row.Cells(0).Value), CStr(Row.Cells(1).Value), CStr(Row.Cells(2).Value), CStr(Row.Cells(3).Value)))
Else
Procurado.Adicionar(CStr(Row.Cells(2).Value))
End If
Next Row

salvarComo.CheckFileExists = False
salvarComo.Filter = Documentos de texto (*.txt)|*.txt
caminho = salvarComo.ShowDialog
Arquivo = salvarComo.FileName

fluxoTexto = New StreamWriter(Arquivo)
Using writer As New StreamWriter(caminho)
For Each R As Responsavel In List
fluxoTexto.WriteLine( _
RESPONSAVEL: R.Nome vbNewLine CPF: R.CPF
)
For Each B As String In R.Beneficiarios
fluxoTexto.WriteLine( _
BENEFICIARIO: B
)
Next B
Next R

End Using
fluxoTexto.Close()
End Sub

End Class

Looking for something like COUNT DISTINCT in SQL. I'd like to select a longishcolumn of names and return a count of how many unique ones are contained. If the list were Smith, Smith, Smith, Jones, Jones, Doe, the return value I would expect would be
3.
I looked at the Excel COUNT functions but didn't see something like this.HomeCookN

Thank you very much, Hans! This perfect. I guess I needed to understand COUNTIF first.HomeCookN

Looking to use a formula that would act much like SUMPRODUCT, but be able to count how many different items of text or string of numbers appear in a column based on the condition of another. For example, I have been using this formula:
=SUMPRODUCT($A$1:$A$237="North America")*($T$9:$T$237)
An example of the data found in column Tmight be1001000, 1001001, 1001002 and 1001003. I wantto know for all of the cells in column Awith "North America", I wanta count of allof the cells incolumn T with unique values,with the answer being 4 rather
than 4004006.

Glad to help!

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

I have a spread sheet where the C column is a block (about 12 consecutive rows/records I selected) of rows/records where each one in the C column says 32ND, yet the content in the E column varies slightly, like "OKMULGEE" , "W OKMULGEE" or "W OKMULGEE
N EDGE"

I want the formula to count the number of rows (is records the correct term?) where the C column records that are 32ND match the records that have the value "OKMULGEE" somewhere in the E column. How do I make it ignore other characters before or after "OKMULGEE"
?? Can Excel do this?

Give this formula a try....

=SUMPRODUCT((C1:C100="32ND")*ISNUMBER(SEARCH("OKMULGEE",E1:E100)))

Adjust the row range to match your setup (but make sure both ranges cover the same number of cells).

hi ,
In vb.net,i want to dynamically count the number of rows which is used(with data in cells) in excel 2007 and i want to use that count value in for loop.
eg: for i= 1 to count
thanks in advance

ajay

Hi,
Please consider
Worksheet.UsedRange
Property. It gets a
Range object that represents all the cells that have contained a value at any time.
Also, please refer to this thread
Excel - Getting the actual usedrange
Sub ResetUsedRng()
Application.ActiveSheet.UsedRange
End Sub
In fact, this issue is related with office development excel, if you have any further questions about it, please reopen anew thread in

Excel for Developers forum.
Thank you for your understanding!
Best regards,

My spreadsheet has col A with a unique part number for each row. Each part number has its details that fill up the columns on its row. the details vary so the number of columns filled vary. ex. partnumer 1, has data in col B:F. partnumber 2 has data
in col B:N.

Id like a formula that will look at each row and tell me how many cells actually have data. So using the example above, partnumber 1 would return a value of 5, partnumber 2 would return a value of 13.

My spreadsheet has col A with a unique part number for each row. Each part number has its details that fill up the columns on its row. the details vary so the number of columns filled vary. ex. partnumer 1, has data in col B:F. partnumber 2
has data in col B:N.

Id like a formula that will look at each row and tell me how many cells actually have data. So using the example above, partnumber 1 would return a value of 5, partnumber 2 would return a value of 13.

Try this formula:

=COUNTA(B10:Z10)

to show the number of cells with data on row 10 (from column B to column Z, you can change the Z to suit the size of your data table columnwise.

Hope this helps / Lars-Åke

C# VS2008
Добрый вечер!
Есть таблица представленная через DataView. Неогу воспользоваться етодо FindRows для того чтобы сделать выборку строк по дву критерия. Вот что у еня выходит:

string fullMod = cbo1.Text cbo2.Text;

DataRowView[] foundRows = dv.FindRows(new object[]{fullMod, cbo3.SelectedValue});

int count = 0;

foreach (DataRowView myRowView in foundRows)

{

for (int count = 0; count dv.Table.Columns.Count; count)

{

datagridview.Rows[count].Cells[0].Value = myRowView[Number].ToString();

count;

}

Доброе утро. Который раз я неогу отправить ответ, аш с пятницы. Вот очередная попытка. Буду краток. Ошибку исправил и вот кирпичик кода, который не нужен был, все работает:
string fullMod = cboRefrigerator.Text cboRefrigeratorClass.Text;
// Получае ассив срок, соотетсвующих критерию поиска
DataRowView[] foundRows = dvTitle.FindRows(new object[]{fullMod, cboTitle.SelectedValue});
if (foundRows.Length == 0)
{
dgvTitle.Rows.Clear();
}
else
{
dgvTitle.Rows.Clear();
dgvTitle.Rows.Add(foundRows.Length);
}

int count = 0;
// Перебирае все полученные строки
foreach (DataRowView myRowView in foundRows)
{
// Присваивае значение из текущей перебираеой записи отфильтрованых строк
dgvTitle.Rows[count].Cells[0].Value = myRowView[Number].ToString();
dgvTitle.Rows[count].Cells[1].Value = myRowView[Title].ToString();
dgvTitle.Rows[count].Cells[2].Value = myRowView[HandTitle].ToString();
dgvTitle.Rows[count].Cells[3].Value = myRowView[HandTitlePart].ToString();
dgvTitle.Rows[count].Cells[4].Value = myRowView[NotesRefNum].ToString();
dgvTitle.Rows[count].Cells[5].Value = myRowView[Quantity].ToString();
count;
}
Если что не так напишите. Спасибо.
Удачи все.

DataGridView1 is bound to a sql table. Cells(0) and (1) come from that table. Cells(2) and (3) are calculated. While debugging the code, the calculations and lookuplook fine, but the results will not display in the columns. Here is the code:

Dim SeatsUsed As Integer = 0

For Each r As DataGridViewRow In DataGridView1.Rows
Try

Dim CountUsed = LookItUp(SELECT COUNT(StudentID) FROM JJAEP WHERE EndDate Is Null AND ISD = Trim(DataGridView1.CurrentRow.Cells(0).Value) )
Dim CountAvailable = Convert.ToInt16(DataGridView1.CurrentRow.Cells(1).Value) - Convert.ToInt16(DataGridView1.CurrentRow.Cells(2).Value)
r.Cells(Used).Value = CInt(CountUsed)
r.Cells(Available).Value = CInt(CountAvailable)

SeatsUsed = SeatsUsed + Convert.ToInt16(CountUsed)
Catch

r.Cells(Used).Value = 0
r.Cells(Available).Value = 0
End Try
Next
TextBox1.Text = LookItUp(SELECT SUM(Chairs) FROM ISDs)
TextBox2.Text = SeatsUsed
TextBox3.Text = Convert.ToInt16(TextBox1.Text) - Convert.ToInt16(TextBox2.Text)

Dim SeatsUsed As Integer = 0
Dim ISDCount = ISDsBindingSource.Count
Dim ISDNumber = ISDCount - 1
ISDsBindingSource.MoveFirst()

Do Until ISDCount = 0
Dim ISDName = Trim(CType(ISDsBindingSource.Item(ISDNumber), DataRowView).Item(ISDName))
Dim ISDSeats = Trim(CType(ISDsBindingSource.Item(ISDNumber), DataRowView).Item(Chairs))

Dim ISDUsed = LookItUp(SELECT COUNT(StudentID) FROM JJAEP WHERE EndDate Is Null AND ISD = Trim(ISDName) )
Dim Available = Integer.Parse(ISDSeats) - Integer.Parse(ISDUsed)
DataGridView1.Rows.Insert(0, ISDName, ISDSeats, ISDUsed, Available)

SeatsUsed = SeatsUsed + ISDUsed
ISDsBindingSource.MoveNext()
ISDCount = ISDCount - 1
ISDNumber = ISDNumber - 1

Loop

TextBox1.Text = LookItUp(SELECT SUM(Chairs) FROM ISDs)
TextBox2.Text = SeatsUsed
TextBox3.Text = Integer.Parse(TextBox1.Text) - Integer.Parse(TextBox2.Text)

i have a range of cells, the possible score values are 0-9, i would like to have a cell updated with the number of cells that have the number 9 in them, so two cells have the number value 9 the target cell has a count of 2 in it etc. i have tried a "SUMIF"
formula butunfortunatelyi'm notfamiliarenough with the expressions needed.
any help gratefullyreceived, thanks Mark

Hi,

Try this non formula approach

1. Create a pivot table and drag the column of numbers to row labels and data area
2. Right click on any one number in the data area and under Summarise by, select Count

If you want to see data for number 9 only in the row labels, filter the row labels on number 9.

Hope this helps.

I face a problem with my code on DataGridView1 as the textboxs always display a false when i start running my program but DataGridView2 is fine. How can i change the text in the textbox when the program start up so it can display something like that.....DataGridView1     DataGridView2Bit7                     Bit7Bit6                     Bit6Bit5                     Bit5Bit4                     Bit4........Till Bit0 ? Below is my code for the DataGridView. Thanks in advance for the help.
private void Form2_Load(object sender, EventArgs e)
{
dataGridView1.RowCount = 8;
dataGridView2.RowCount = 8;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
row.Cells[0].Value = false;
row.Selected = false;
}
}

void t_Tick2(object sender, EventArgs e)
{
for(int i=7; i=0; i--)
{
DataGridViewCell cell = dataGridView1.Rows[count].Cells[0];
if (Bit[i] == 1)
{
textBox2.BackColor = Color.Green;
cell.Style.BackColor = Color.Green;
dataGridView2.Rows[count].Cells[0].Style.BackColor = Color.Green;
}
else if (Bit[i] == 0)
{
textBox2.BackColor = Color.Red;
cell.Style.BackColor = Color.Red;
dataGridView2.Rows[count].Cells[0].Style.BackColor = Color.Red;
dataGridView2.Rows[count].Cells[0].Selected = false;
}
count++;
}
}

Hi Wookie85, Thank you for your information. Based on my understanding, I've written the sample code as the following:
private void Form1_Load(object sender, EventArgs e)
{
//Add rows
dataGridView1.Rows.Add(8);
dataGridView2.Rows.Add(8);

//Initialize
for (int i = 7; i = 0; i--)
{
dataGridView1.Rows[i].Cells[0].Value = Bit + Convert.ToString(7 - i);
dataGridView2.Rows[i].Cells[0].Value = Bit + Convert.ToString(7 - i);
}
}

It will show the data in both datagridview1 and datagridview2 when the form is loaded. Hope this helps. If you have any concern, please feel free to let me know. Best regards, Yichun ChenPlease remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

I have a bound datagridview with some unbound calculated columns. The end resultis a form that shows the top 10 by sales customers. I use the datagridview to sort the unbound column SubTotal by descending order then i populate a form with top 10
records. Everything works as planned except i noticed that the first record from datasource is at bottom of sorted datagridview which is not right. All other records are sorting properly except the first record. From research on internet i am sure it has to
do with the fact the first record is selected, but i have been unable to fix this problem. Any Ideas?

Public Sub Top10ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Top10ToolStripMenuItem.Click
Dim thisConnection As New iDB2Connection(DataSource=sub; Naming=System; LibraryList=QS36F, *USRLIBL)
Dim thisCommand As New iDB2Command(SELECT UACCT, UNAME, ACCGRP, UGUSED, UGU01, UGU02, UGU03, UGU04, UGU05, UGU06, UGU07, UGU08, UGU09, UGU10, UGU11, UGCHG, UARG01, UARG02, UARG03, UARG04, UARG05, UARG06, UARG07, UARG08, UARG09, UARG10, UARG11 FROM QS36F/UBA.MF WHERE UACREC='A' AND UGSTAT='A' AND UACCT399999999, thisConnection)
Dim thisReader As iDB2DataReader
Try
thisConnection.Open()
thisReader = thisCommand.ExecuteReader()
Dim dt As New DataTable(UBA.MF)
dt.Load(thisReader)
dt.Columns.Add(AnnualUse, Type.GetType(System.Int32), UGUSEDUGU01UGU02UGU03UGU04UGU05UGU06UGU07UGU08UGU09UGU10UGU11)
dt.Columns.Add(AnnualBill, Type.GetType(System.Double), UGCHG(UARG01UARG02UARG03UARG04UARG05UARG06UARG07UARG08UARG09UARG10UARG11)/100)
dt.Columns.Add(AnnualUsage, Type.GetType(System.Int32))
dt.Columns.Add(SubTotal, Type.GetType(System.Double))
frmResult.DataGridView1.DataSource = dt
thisReader.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
thisConnection.Close()
frmResult.Label1.Text = Top 10 Customers
Top10.Label4.Text = CStr(Today)
Top10.Label5.Text = CStr(TimeOfDay)
Dim x, y As Integer
Dim name, name1 As String
For x = 0 To frmResult.DataGridView1.Rows.Count - 1
frmResult.DataGridView1.Rows(x).Cells(29).Value = CInt(frmResult.DataGridView1.Rows(x).Cells(27).Value)
frmResult.DataGridView1.Rows(x).Cells(30).Value = CDbl(frmResult.DataGridView1.Rows(x).Cells(28).Value)
Next
For x = 0 To frmResult.DataGridView1.Rows.Count - 1
name = CStr(frmResult.DataGridView1.Rows(x).Cells(2).Value)
For y = x 1 To frmResult.DataGridView1.Rows.Count - 1
name1 = CStr(frmResult.DataGridView1.Rows(y).Cells(2).Value)
If name.Equals(name1) And name Then
frmResult.DataGridView1.Rows(y).Cells(30).Value = CDbl(frmResult.DataGridView1.Rows(x).Cells(30).Value) CDbl(frmResult.DataGridView1.Rows(y).Cells(28).Value)
frmResult.DataGridView1.Rows(x).Cells(30).Value = 0
frmResult.DataGridView1.Rows(y).Cells(29).Value = CInt(frmResult.DataGridView1.Rows(x).Cells(29).Value) CInt(frmResult.DataGridView1.Rows(y).Cells(27).Value)
End If
Next
Next
frmResult.DataGridView1.Sort(frmResult.DataGridView1.Columns(30), System.ComponentModel.ListSortDirection.Descending)
Dim cust As String = CStr(frmResult.DataGridView1.Rows(0).Cells(1).Value)
Dim cust1 As String = CStr(frmResult.DataGridView1.Rows(1).Cells(1).Value)
Dim cust2 As String = CStr(frmResult.DataGridView1.Rows(2).Cells(1).Value)
Dim cust3 As String = CStr(frmResult.DataGridView1.Rows(3).Cells(1).Value)
Dim cust4 As String = CStr(frmResult.DataGridView1.Rows(4).Cells(1).Value)
Dim cust5 As String = CStr(frmResult.DataGridView1.Rows(5).Cells(1).Value)
Dim cust6 As String = CStr(frmResult.DataGridView1.Rows(6).Cells(1).Value)
Dim cust7 As String = CStr(frmResult.DataGridView1.Rows(7).Cells(1).Value)
Dim cust8 As String = CStr(frmResult.DataGridView1.Rows(8).Cells(1).Value)
Dim cust9 As String = CStr(frmResult.DataGridView1.Rows(9).Cells(1).Value)
Dim ause As Integer = CInt(frmResult.DataGridView1.Rows(0).Cells(29).Value)
Dim ause1 As Integer = CInt(frmResult.DataGridView1.Rows(1).Cells(29).Value)
Dim ause2 As Integer = CInt(frmResult.DataGridView1.Rows(2).Cells(29).Value)
Dim ause3 As Integer = CInt(frmResult.DataGridView1.Rows(3).Cells(29).Value)
Dim ause4 As Integer = CInt(frmResult.DataGridView1.Rows(4).Cells(29).Value)
Dim ause5 As Integer = CInt(frmResult.DataGridView1.Rows(5).Cells(29).Value)
Dim ause6 As Integer = CInt(frmResult.DataGridView1.Rows(6).Cells(29).Value)
Dim ause7 As Integer = CInt(frmResult.DataGridView1.Rows(7).Cells(29).Value)
Dim ause8 As Integer = CInt(frmResult.DataGridView1.Rows(8).Cells(29).Value)
Dim ause9 As Integer = CInt(frmResult.DataGridView1.Rows(9).Cells(29).Value)
Dim abill As Double = CDbl(frmResult.DataGridView1.Rows(0).Cells(30).Value)
Dim abill1 As Double = CDbl(frmResult.DataGridView1.Rows(1).Cells(30).Value)
Dim abill2 As Double = CDbl(frmResult.DataGridView1.Rows(2).Cells(30).Value)
Dim abill3 As Double = CDbl(frmResult.DataGridView1.Rows(3).Cells(30).Value)
Dim abill4 As Double = CDbl(frmResult.DataGridView1.Rows(4).Cells(30).Value)
Dim abill5 As Double = CDbl(frmResult.DataGridView1.Rows(5).Cells(30).Value)
Dim abill6 As Double = CDbl(frmResult.DataGridView1.Rows(6).Cells(30).Value)
Dim abill7 As Double = CDbl(frmResult.DataGridView1.Rows(7).Cells(30).Value)
Dim abill8 As Double = CDbl(frmResult.DataGridView1.Rows(8).Cells(30).Value)
Dim abill9 As Double = CDbl(frmResult.DataGridView1.Rows(9).Cells(30).Value)
Top10.Label1.Text = Top 10 Gas Customers
Top10.Label9.Text = cust
Top10.Label14.Text = cust1
Top10.Label17.Text = cust2
Top10.Label20.Text = cust3
Top10.Label23.Text = cust4
Top10.Label26.Text = cust5
Top10.Label29.Text = cust6
Top10.Label32.Text = cust7
Top10.Label35.Text = cust8
Top10.Label38.Text = cust9
Top10.Label10.Text = ause.ToString(N0)
Top10.Label13.Text = ause1.ToString(N0)
Top10.Label16.Text = ause2.ToString(N0)
Top10.Label19.Text = ause3.ToString(N0)
Top10.Label22.Text = ause4.ToString(N0)
Top10.Label25.Text = ause5.ToString(N0)
Top10.Label28.Text = ause6.ToString(N0)
Top10.Label31.Text = ause7.ToString(N0)
Top10.Label34.Text = ause8.ToString(N0)
Top10.Label37.Text = ause9.ToString(N0)
Top10.Label11.Text = abill.ToString(C2)
Top10.Label12.Text = abill1.ToString(C2)
Top10.Label15.Text = abill2.ToString(C2)
Top10.Label18.Text = abill3.ToString(C2)
Top10.Label21.Text = abill4.ToString(C2)
Top10.Label24.Text = abill5.ToString(C2)
Top10.Label27.Text = abill6.ToString(C2)
Top10.Label30.Text = abill7.ToString(C2)
Top10.Label33.Text = abill8.ToString(C2)
Top10.Label36.Text = abill9.ToString(C2)
thisCommand.Dispose()
End Try
'frmResult.Show()
Top10.Show()
End Sub

Hi Mdruk,
Welcome to the MSDN Forum.
On how to sort, A DataView can help you. When you want to bound the data to the DataGridview, you can bind the DataTable.DataView to the DataGridView.
Have a look at the DataView.Sort:
DataView.Sort Property:
http://msdn.microsoft.com/en-us/library/system.data.dataview.sort.aspx
On the other hand, you can do more advanced settings if you use the BindingSource. BindSource.Sort:
BindingSource.Sort Property:
http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.sort.aspx
On the other hand, when you want to sort, a column type is known necessarily. Suppose there is a string column and its values is: 100,5,7,25, if you sort is, it will shows: 100,25,5,7. But if it is an Integer column, it will be sorted as: 2,7,25,100. Maybe
this is why you got the firstwrong sorted item.
If you have any questions, please feel free to tell us.
Best RegardsNeddy Ren [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

HiI have an excel that have cells with some formulas. I trie to read those cells with open xml sdk code snippets, but i just get the text formula, not the value result of the formula. Does anybody know how to retrieve the value of a cell formula?Thanks

Cell cell;
cell.CellFormula.CalculateCell.Value = true;

Hi all,
I have a code from Han, which works great.
Sub InsertRows()
Const Firstrow = 2 'row 2
Const MyCol = 3 ' column C

Dim MyRow As Long
Dim RowCount As Long
MyRow = Firstrow 1
RowCount = 1

Do While Cells(MyRow, MyCol)
If Cells(MyRow, MyCol) = Cells(MyRow - 1, MyCol) Then
MyRow = MyRow 1
RowCount = RowCount 1
ElseIf RowCount 1 Then
Cells(MyRow, MyCol).Resize(RowSize:=RowCount) _
.EntireRow.Insert
' ***** Addition *****
'this section to add value on empty cells in newly created row.
Cells(MyRow - RowCount RowCount, MyCol - 1).Resize(RowSize:=RowCount) _
.Value = Inventory 'delete this to control value up.

'this section to add value on empty cells in newly created row.
Cells(MyRow - RowCount RowCount, MyCol 3).Resize(RowSize:=RowCount) _
.Value = Today()
' ***** End of addition *****
MyRow = MyRow RowCount 1
RowCount = 1
Else
MyRow = MyRow 1
End If
Loop
End Sub

However, i need to tweak it a little bit to fit my needs. Sometimes it gives me more rows than i needed.

My goal is
1) loop through column C to detect duplicate values in sequence.
2) if dupe value is found, check adjacent column to count unique values
3) Insert rows below based on unique values on adjacent colum

My ultimate goal

I hope that I am make sense.
Can anyone help me to tweak this code to fit for my needs? I've been trying to search online and finding different ways to achieve it.

Thank you.Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or Mark as Answer if it solves your question. This will help to contribute to the forum.

As far as I can tell, ura is not the same as usa, so I don't know why rows 31-32 are weird.
Previously, you explicitly requested NOT to insert rows on a single count. Next, you requested not to look at the simple row count, but at the count of unique routes. So I assumed that if there is a duplicate in column C but a unique route count of 1, you
wouldn't want to insert a row. Now, you want TWO rows inserted if the unique route count is 1.I find thatconfusing.
Perhaps the following comes closer to what you want:
Sub InsertRows()
Const Firstrow = 2 'row 2
Const MyCol = 3 ' column C

Dim MyRow As Long
Dim RowCount As Long
Dim RouteCount As Long
Dim UniqueRoutes As Collection

MyRow = Firstrow 1
RowCount = 1
Set UniqueRoutes = New Collection

Do While Cells(MyRow, MyCol)
If Cells(MyRow, MyCol) = Cells(MyRow - 1, MyCol) Then
MyRow = MyRow 1
RowCount = RowCount 1
Else
RouteCount = UniqueRoutes.Count
If RowCount 1 And RouteCount = 1 Then
RouteCount = 2
End If
If RouteCount 1 Then
Cells(MyRow, MyCol).Resize(RowSize:=RouteCount) _
.EntireRow.Insert
'this section to add value on empty cells in newly created row.
Cells(MyRow - RouteCount RouteCount, MyCol - 1).Resize(RowSize:=RouteCount) _
.Value = Inventory 'delete this to control value up.
'this section to add value on empty cells in newly created row.
Cells(MyRow - RouteCount RouteCount, MyCol 3).Resize(RowSize:=RouteCount) _
.Value = Today()
MyRow = MyRow RouteCount 1
Else
MyRow = MyRow 1
End If
Set UniqueRoutes = New Collection
RowCount = 1
End If
' Try to add the route to the UniqueRoutes collection
On Error Resume Next
UniqueRoutes.Add Item:=Cells(MyRow, MyCol 1).Value, _
Key:=CStr(Cells(MyRow, MyCol 1).Value)
On Error GoTo 0
Loop
End Sub

BTW, UniqueRoutes is a collection of items, not a number, so you cannot set UniqueRoutes to 2.

Regards, Hans Vogelaar

I am using Iterator which I think it only counts unique word so who can I count the all the words on the text

I am using Iterator which I think it only counts
unique word so who can I count the all the words on
the textI guess you want to count all the words because I don't fully understand your question
stringTokenizer?

StringTokenizer t = new StringTokenizer("Only a sample");
System.out.println(t.countTokens());

Hi,

We will be entering CR no's in one Cell of excell sheet with Comma Separation. My requirement is to count total no of such entries and count unique instances of the CR in that given list

For example I will have any entry as 162345,172387,185423, 162345.. In this case total count will be 4 and 3 are Unique. Also we may have sum up this across 3 Cells also

Can QlikView Support this request..

Thanks,Soma

Hi,

There is a function in scripting called 'SubField' which can be used to break a string which has delimited text.

Simply use this code to create a new field and then apply count on this field to get desired result:

T:LoadSubField(CR_NO,',') as CRFrom xyz;

Caros Amigos podem me ajudar neste codigo para que o mesmo me retorne no textbox3 valor em hora, tentei o format mais no deu certo segue o codigo para analise;

Public MatrizResultados As VariantPublic Total_Ocorrencias As Long
Private Sub btn_Procurar_Click() If Me.txt_Procurar.Text = Then MsgBox Digite um valor para a pesquisa Else Call ProcuraPersonalizada(Me.txt_Procurar.Text) End IfEnd Sub
Private Sub SpinButton1_Change()Dim Linha As LongDim TotalOcorrencias As Long
TotalOcorrencias = SpinButton1.Max + 1 Linha = MatrizResultados(SpinButton1.Value) Label_Registros_Contador.Caption = SpinButton1.Value + 1 de TotalOcorrencias TextBox1.Text = Plan1.Cells(Linha, 1).Value TextBox2.Text = Plan1.Cells(Linha, 2).Value TextBox3.Text = Plan1.Cells(Linha, 3).Value TextBox4.Text = Plan1.Cells(Linha, 4).Value TextBox5.Text = Plan1.Cells(Linha, 5).Value TextBox6.Text = Plan1.Cells(Linha, 6).Value TextBox7.Text = Plan1.Cells(Linha, 7).Value TextBox8.Text = Plan1.Cells(Linha, 8).Value TextBox9.Text = Plan1.Cells(Linha, 9).Value TextBox10.Text = Plan1.Cells(Linha, 10).Value TextBox11.Text = Plan1.Cells(Linha, 11).Value TextBox12.Text = Plan1.Cells(Linha, 12).Value TextBox13.Text = Plan1.Cells(Linha, 13).Value TextBox14.Text = Plan1.Cells(Linha, 14).Value TextBox15.Text = Plan1.Cells(Linha, 15).Value TextBox16.Text = Plan1.Cells(Linha, 16).Value TextBox17.Text = Plan1.Cells(Linha, 17).Value TextBox18.Text = Plan1.Cells(Linha, 18).Value
End Sub
Private Sub ProcuraPersonalizada(ByVal TermoPesquisado As String)Dim Busca As RangeDim Primeira_Ocorrencia As StringDim Resultados As String
'Executa a busca Set Busca = Plan1.Cells.Find(What:=TermoPesquisado, After:=Range(A1), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'Caso tenha encontrado alguma ocorrncia... If Not Busca Is Nothing Then Primeira_Ocorrencia = Busca.Address Resultados = Busca.Row 'Lista o primeiro resultado na variavel 'Neste loop, pesquisa todas as prximas ocorrncias para 'o termo pesquisado Do Set Busca = Plan1.Cells.FindNext(After:=Busca) 'Condicional para no listar o primeiro resultado 'pois j foi listado acima If Not Busca.Address Like Primeira_Ocorrencia Then Resultados = Resultados ; Busca.Row End If Loop Until Busca.Address Like Primeira_Ocorrencia MatrizResultados = Split(Resultados, ;) 'Atualiza dados iniciais no formulrio SpinButton1.Max = UBound(MatrizResultados) 'Valor maximo do seletor de registros 'habilita o seletor de registro SpinButton1.Enabled = True 'indicador do seletor de registros Label_Registros_Contador.Caption = 1 de UBound(MatrizResultados) + 1 'Box com o conteudo encontrado TextBox1.Text = Plan1.Cells(MatrizResultados(0), 1).Value TextBox2.Text = Plan1.Cells(MatrizResultados(0), 2).Value TextBox3.Text = Plan1.Cells(MatrizResultados(0), 3).Value TextBox4.Text = Plan1.Cells(MatrizResultados(0), 4).Value TextBox5.Text = Plan1.Cells(MatrizResultados(0), 5).Value TextBox6.Text = Plan1.Cells(MatrizResultados(0), 6).Value TextBox7.Text = Plan1.Cells(MatrizResultados(0), 7).Value TextBox8.Text = Plan1.Cells(MatrizResultados(0), 8).Value TextBox9.Text = Plan1.Cells(MatrizResultados(0), 9).Value TextBox10.Text = Plan1.Cells(MatrizResultados(0), 10).Value TextBox11.Text = Plan1.Cells(MatrizResultados(0), 11).Value TextBox12.Text = Plan1.Cells(MatrizResultados(0), 12).Value TextBox13.Text = Plan1.Cells(MatrizResultados(0), 13).Value TextBox14.Text = Plan1.Cells(MatrizResultados(0), 14).Value TextBox15.Text = Plan1.Cells(MatrizResultados(0), 15).Value TextBox16.Text = Plan1.Cells(MatrizResultados(0), 16).Value TextBox17.Text = Plan1.Cells(MatrizResultados(0), 17).Value TextBox18.Text = Plan1.Cells(MatrizResultados(0), 18).Value Else 'Caso nada tenha sido encontrado, exibe mensagem informativa SpinButton1.Enabled = False 'desabilita o seletor de registros Label_Registros_Contador.Caption = 'zera os resultados encontrados 'limpa os campos do formulrio TextBox1.Text = TextBox2.Text = TextBox3.Text = TextBox4.Text = TextBox5.Text = TextBox6.Text = TextBox7.Text = TextBox8.Text = TextBox9.Text = TextBox10.Text = TextBox11.Text = TextBox12.Text = TextBox13.Text = TextBox14.Text = TextBox15.Text = TextBox16.Text = TextBox17.Text = TextBox18.Text = MsgBox Nenhum resultado para ' TermoPesquisado ' foi encontrado.
End If End Sub
Private Sub UserForm_Initialize()
SpinButton1.Enabled = False Label_Registros_Contador.Caption = End Sub

Claudio obrigado pela dica, est funcionado... so mais uma duvida mais por curiosidade, se eu quiser que o textbox retorne a cor que esta na planilha como fao?EX; o textbox3 retorna valor em hora, esta celula esta com formatao condicional que preenche a mesma na cor verde se a hora for maior que 1 e vermelho se menor que 0(hora negativa), o textbox me retorna esta cor? a formatao da Celula independente se esteja em formatao condicional ?

Olá, tenho uma dúvida...eu tenho o seguinte código:
this.dataGridView1.Rows[1].Cells[0].Value = ( armazenaChaves);
this.dataGridView1.Rows[1].Cells[1].Value = (textBox3.Text);
this.dataGridView1.Rows[1].Cells[2].Value = (textBox4.Text);
this.dataGridView1.Rows[1].Cells[3].Value = (textBox5.Text);
this.dataGridView1.Rows[1].Cells[4].Value = (textBox2.Text);
esse código nao funciona pelo motivo de nao ter uma segunda linha na minha Data Grid...acho que eu preciso adicionar uma linha no dataGrid antes desse código, mas eu nao sei como. Como eu crio uma nova linha no grid view..para aceitar esses valores?

Para adicionar uma linha no dataGridView
dataGridView1.Rows.Add();

I would like to count the number of times two cells match in a single column with two rows of data in one formula. Cell located in A3 would return a value of 2 as their are two match between row 1 and row 2 in columns A and B but not C. Seems simple enough
but I can figure it out.

A B C

Wow, that is slick! Now I have to understand how that function is working. thank you so much for the solution.

Hi,

If I tell you how it works you may forget. If you work out how it works you'll never forget.

If you do get stuck then post back and I'll explain. Thanks for the feedback.

hola tengo este error, al momento de leer una grilla y pasar los valores a los parametros.
System.ArgumentOutOfRangeException: El índice estaba fuera del intervalo. Debe ser un valor no negativo e inferior al tamaño de la colección. Nombre del parámetro: index en
System.Collections.ArrayList.get_Item(Int32 index) en System.Web.UI.WebControls.GridViewRowCollection.get_Item(Int32 index) en _Default.validarOrganica()
y mi codigo es el sgte:
Private Sub validarOrganica()

Try

Dim sqlconn As String = Data Source=testing;Initial Catalog=Intranet;User ID=sa;Password=inicio123
Using conn As New SqlConnection(sqlconn)
Dim cmd As New SqlCommand
cmd.Connection = conn
'For Each row As GridViewRow In grvDatos.Rows

For i As Integer = 0 To grvDatos.Rows.Count

cmd.Parameters.Clear()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = SP_RRHH_USP_ORGANICA
cmd.Parameters.Add(@rut, SqlDbType.VarChar, 10)
cmd.Parameters(@rut).Value = grvDatos.Rows(i).Cells(1).Text
cmd.Parameters.Add(@dv, SqlDbType.Char, 1)
cmd.Parameters(@dv).Value = grvDatos.Rows(i).Cells(2).Text
cmd.Parameters.Add(@nom, SqlDbType.VarChar, 50)
cmd.Parameters(@nom).Value = grvDatos.Rows(i).Cells(3).Text
cmd.Parameters.Add(@apaterno, SqlDbType.VarChar, 50)
cmd.Parameters(@apaterno).Value = grvDatos.Rows(i).Cells(4).Text
cmd.Parameters.Add(@amaterno, SqlDbType.VarChar, 50)
cmd.Parameters(@amaterno).Value = grvDatos.Rows(i).Cells(5).Text
cmd.Parameters.Add(@id_empresa, SqlDbType.VarChar, 10)
cmd.Parameters(@id_empresa).Value = grvDatos.Rows(i).Cells(6).Text
cmd.Parameters.Add(@id_sucursal, SqlDbType.VarChar, 10)
cmd.Parameters(@id_sucursal).Value = grvDatos.Rows(i).Cells(7).Text
cmd.Parameters.Add(@id_cargo, SqlDbType.VarChar, 10)
cmd.Parameters(@id_cargo).Value = grvDatos.Rows(i).Cells(9).Text
cmd.Parameters.Add(@f_ingreso, SqlDbType.VarChar, 10)
cmd.Parameters(@f_ingreso).Value = grvDatos.Rows(i).Cells(10).Text

cmd.Parameters.Add(@jefe, SqlDbType.Int)
cmd.Parameters(@jefe).Value = grvDatos.Rows(i).Cells(12).Text
cmd.Parameters.Add(@idAreapertenencia, SqlDbType.VarChar, 10)
cmd.Parameters(@idAreapertenencia).Value = grvDatos.Rows(i).Cells(14).Text

cmd.Parameters.Add(@fnacimiento, SqlDbType.VarChar, 10)
cmd.Parameters(@fnacimiento).Value = grvDatos.Rows(i).Cells(15).Text
conn.Open()
'cmd.ExecuteNonQuery()
conn.Close()
lblmsj.Text = El Proceso se ha Ejecutado Correctamente.

gracias por responder, pero te cuento que también lo tenia así y me daba otro problema,( el procedimiento actualiza el rut del registro si existe y si no inserta) cuando pasaba los valores a los parámetros después de recorrer la grilla por ej al actualizar
los campos que se necesitaba de este proceso quedaban todos con el mismo valor, y si existian registros nuevos estos no se insertaban :/..
te dejo el SP:
USE [Intranet]
GO
/****** Object: StoredProcedure [dbo].[SP_RRHH_USP_ORGANICA_BACKUP] Script Date: 03/25/2014 17:18:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MAXIMO QUIROZ
-- Create date: 06/03/2014
-- Description: Procedimiento donde se valida el registro antes de ser
-- insertado. si el registro existe los campos se actualizan, si no se hace un Insert.
-- =============================================
ALTER PROCEDURE [dbo].[SP_RRHH_USP_ORGANICA_BACKUP]
@rut varchar(10),
@dv char(1),
@nom varchar(50),
@apaterno varchar(50),
@amaterno varchar(50),
@id_empresa varchar(10),
@id_sucursal varchar(10),
@id_cargo varchar(10),
@f_ingreso varchar(10),
@jefe int,
@idAreapertenencia varchar(10),
@fnacimiento varchar(10)


AS
BEGIN
IF NOT EXISTS(SELECT * from RRHH_PERSONA_BACKUP WHERE RUT=@rut)
INSERT INTO RRHH_PERSONA_BACKUP(rut,dv,nombre,ape_paterno,ape_materno,id_empresa,
id_sucursal,id_cargo,fecha_ingreso,fecha_termino,id_causal,jefe,id_area_pertenencia,
licencia,mail,fecha_nacimiento,fono,insert_update)VALUES(@rut,@dv,@nom,@apaterno,@amaterno,@id_empresa,@id_sucursal,
@id_cargo,@f_ingreso,null,null,@jefe,@idAreapertenencia,null,null,@fnacimiento,null,0)
ELSE
UPDATE RRHH_PERSONA_BACKUP SET id_empresa=@id_empresa,id_sucursal=@id_sucursal,id_cargo=@id_cargo,
jefe=@jefe,id_area_pertenencia=@idAreapertenencia, insert_update=1 WHERE RUT=@rut and id_empresa @id_empresa or (id_sucursal @id_sucursal)
or(id_cargo @id_sucursal) or(id_cargo@id_cargo) or (jefe@jefe) or(id_area_pertenencia @idAreapertenencia)
END
entonces no se que puede ser. si me puedes ayudar por favor.
ahí en la imagen sale un ejemplo de como me quedan algunos de los campos que supuesta mente se actualizan

I have data that repeats in cells of the same column. Some of the cells are blank. I want to count the number of unique cell entries but ignore the blank cells.
Example:
I have cells in column A as an examplewith the following
Jim
Jim
Tom
Tom
Dave
Dave
Ted
Barney
Leo
"blank cell"

I want to have MS excel count the # of unique enties, but ignore the blank cells(the answer should be 6)
How can I do this?

I got a total number of entries.
not a count of unique entries.

Is there a way/formula to do this, other than they way I'm doing it now, which is by using COUNTIF? In a table with 1000 rows for instance, let's say the values in the A column are what I'm keying off. I would like a count of all the cells in the A column that have the word "example" as their value. Right now for each unique value in a cell in the A column I am manually creating a COUNTIF(A1:A1000,"value i'm matching against) but if there are many unique values in the A column, it's quite laborious. Ideally I'd like to just have a table generated that gives me the top 5 or top 10 most occurring cell values in the A column in the table. What's the best way to do this?

So for instance, for a column like this:
1
1
1
2
2
4
5
I want a way to get back the number of times 1 appears in the list (3), the number of times 2 appears in the list (2), 4 (1), 5 (1), and so on. If I do a COUNTIF and there are thousands of rows, I have to manually put the matching string in each one.

How do I count cells in a column that contain both numerical and text data? I tried the count function but it doesn't include the cells that are just text. Thanks

How do I count cells in a column that contain both numerical and text data? I tried the count function but it doesn't include the cells that are just text. Thanks

Hi,
Use COUNTA
=COUNTA(A1:A10)

If this post answers your question, please mark it as the Answer.
Mike H

SQL select count(unique(cv_id))
from detailResume
where cat_id = 5
and regexp_like(lower(detailResume), Replace('Magda Gamal', ' ', '|')); 2 3 4

COUNT(UNIQUE(CV_ID))
--------------------
0

SQL set timing on
SQL /

COUNT(UNIQUE(CV_ID))
--------------------
0

Elapsed: 00:01:16.15

SQL select count(*)
from detailResume
where cat_id = 5
and lower(detailResume) like '%Magda Gamal%'; 2 3 4

COUNT(*)
----------
0

Elapsed: 00:00:10.12
SQL

SQL select count(unique(cv_id))
from detailResume
where cat_id = 5
and lower(detailResume) like '%Magda Gamal%'; 2 3 4

COUNT(UNIQUE(CV_ID))
--------------------
0

Elapsed: 00:00:15.41
SQL I am checking first query and seeing Oracle text query is taking 00:01:16.15 whereas oracle query with count(*) 00:00:10.12 and count(unique(cv_id)) 00:00:15.41

Why there is too much difference?

Best regards

Please observe below :

SQL select count(*)
from detailResume
where cat_id = 5
and lower(detailResume) like '%Magda Gamal%'; 2 3 4

COUNT(*)
----------
0

Elapsed: 00:00:10.12
SQL this will never be match : lower(detailResume) like '%Magda Gamal%';

Cheers,
Manik.

Hi everyone ,
my gridview is somethin like below
RoleName ViewRigth AddRigth
EditRight
A CheckBox
CheckBox CheckBox
and so on for 15 rows ,
i have written a for loop as
for ( int count=0 ; countgrid_role.Rows.Count ; count)
to loop through the rows of grid
and inside that i have taken a variable which has to contain the value of RoleName from the Grid for the particular count value(like 0 for A , 1 for B and so on)
thevariable is
string roleName = grid_role.Rows[count].Cells[count].Text;
can anyone plz tell me what am i doing wrong , coz this variable works fine for the first count (i.e when count=0) , but for the other count or loops , it dont take any values from the RoleName column and gives an exception like argumentOutOfBound Specified
argument was out of the range of valid values.
Parameter name: index

what should i do , inorder to solve this

You might get an answer on this forum, and if you do more power to you. However, since this is not a Lync server-specific issue you may have better luck at the link below. Since you refer to a gridview control I'm assuming this is a C# question.

Visual C#
General

I get some add-in for some indicator, which run very fast for calculation, but when I code a macro to perform similar tasks, which takes 5 seconds at least, and would like to know any approach to make macro run faster?

Does anyone have any suggestions?
Thanks in advance for any suggestions

Sub ABC()
Dim EMAslow As Double, EMAfAst As Double, ws As Worksheet, LR As Integer
Dim eMaF() As Double, eMaS() As Double, EMAdif(), emaPer(), Ave As Double, MacDper As Double, coUnt As Integer
Dim DataRange As Range
Dim ExPSlowWeight As Double
Dim ExPFastWeight As Double
Dim PerWeight As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' The below three lines are the MACD settings. ''
'' The Values can either be changed here or uncomment the inputbox lines to be prompted.''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
EMAslow = 26 'InputBox(Prompt:="Enter Macd Slow settings.", Title:="MACD SLOW", Default:="13")
EMAfAst = 12 'InputBox(Prompt:="Enter Macd Fast settings.", Title:="MACD Fast", Default:="5")
MacDper = 9 'InputBox(Prompt:="Enter Macd Period settings.", Title:="MACD Period", Default:="6")
ExPSlowWeight = 2 / (EMAslow + 1)
PerWeight = 2 / (MacDper + 1)
ExPFastWeight = 2 / (EMAfAst + 1)
'slow
LR = Cells(Rows.coUnt, "A").End(xlUp).Row
For Each DataRange In Range(Cells(2, "A"), Cells(LR, "A"))
coUnt = LR - DataRange.Row + 2
'fill the eMA slow Array
ReDim Preserve eMaS(2 To LR - EMAslow + 1)
If coUnt = LR - EMAslow + 1 Then
'get the first value which is the Simple Moving average
eMaS(coUnt) = Application.Average(Range(Cells(coUnt, "E"), Cells(LR, "E")))
'Cells(coUnt, "L") = eMaS(coUnt)
'ElseIf coUnt LR - EMAslow + 1 And Cells(coUnt, "E") "" Then
ElseIf coUnt LR - EMAslow + 1 Then
eMaS(coUnt) = (Cells(coUnt, "E") * ExPSlowWeight) + (eMaS(coUnt + 1) * (1 - ExPSlowWeight))
'Cells(coUnt, "L") = eMaS(coUnt)
End If
Next DataRange
'fast
For Each DataRange In Range(Cells(2, "A"), Cells(LR, "A"))
coUnt = LR - DataRange.Row + 2
'fill the eMA slow Array
ReDim Preserve eMaF(2 To LR)
If coUnt = LR - EMAfAst + 1 Then
'get the first value which is the Simple Moving average
eMaF(coUnt) = Application.Average(Range(Cells(coUnt, "E"), Cells(LR, "E")))
'Cells(coUnt, "M") = eMaF(coUnt)
'ElseIf coUnt LR - EMAfAst + 1 And Cells(coUnt, "E") "" Then
ElseIf coUnt LR - EMAfAst + 1 Then
eMaF(coUnt) = (Cells(coUnt, "E") * ExPFastWeight) + (eMaF(coUnt + 1) * (1 - ExPFastWeight))
'Cells(coUnt, "M") = eMaF(coUnt)
End If
Next DataRange
ReDim Preserve EMAdif(1 To UBound(eMaS))
For coUnt = 2 To UBound(eMaS)
EMAdif(coUnt) = eMaF(coUnt) - eMaS(coUnt)
'Cells(coUnt, "N") = EMAdif(coUnt)
Next coUnt
'MacD Period
Dim x As Integer, y As Integer, z As Integer, Avee As Double
y = UBound(EMAdif) - MacDper
For x = y To 2 Step -1
'get the SMA for first value
If x = y Then
For z = x To x + MacDper - 1 '(EMAslow + MacDper - 1)
Ave = Ave + EMAdif(z) * 2
Next z
ReDim emaPer(2 To UBound(EMAdif) - MacDper)
emaPer(x) = Ave / MacDper* 2
'Cells(x, "O") = emaPer(x)
ElseIf x y Then
emaPer(x) = (EMAdif(x - 1) * PerWeight) + (emaPer(x + 1) * (1+ PerWeight))
'Cells(x, "O") = emaPer(x)
End If
Next x
x = Empty: y = Empty: z = Empty
x = LBound(emaPer)
y = UBound(emaPer)
For z = x To y - 1
Cells(z, "ED") = EMAdif(z) - emaPer(z + 1)
Next z
End Sub

Thanks, to everyone very much for suggestions :

How can you do a total count of cells eg Counta (with text in) - but not include the rows that are grouped / hidden?

A1: Apple
A2: Pear
A3: Orange
(A4: Strawberry A5: Melon - Grouped and hidden)
A6: Lemon

Total = 4

Make sense?! Thank you!

Hi,

Try this, 103 emulates COUNTA and ignores hidden rows. Look in Excel help for more SUBTOTAL options.

=SUBTOTAL(103,A1:A6)

dear all,
how can i count unique value with subtotal list, like as under.
Col_A
1
3
1
4
3
3 as i want count.

http://office.microsoft.com/en-us/excel-help/count-unique-values-among-duplicates-HP010070481.aspx

I am trying to count the number of names that appear in a spreadsheet for a work schedule. Each Cell contains the name of one individual COUNTA only totals the number of filled cells. Not of a any unique values. COUNTIFS only works with numerical values. It would be great if COUNTIFS could work with text as well, but I can't seem to find a function that satisfies this... Nor could I find anything on these message boards.Say for instance, I needed to count the number of times "LANE" appeared in Row A, what would be the correct formula for this?Larger image in link...https://skitch.com/marckalish/gg48c/1st-quarter-2011-md-call-schedule-march-29-2 011-blp

Thank you!

Hi, i have a unbound datagridview that contain 8 columns i added data in it using the following code:
Dim i As Integer = DataGridViewX1.Rows.Count - 1
Me.DataGridViewX1.Rows.Add()
With Me.DataGridViewX1.Rows(i)
.Cells(id).Value = Format(i, 0000)
.Cells(design).Value = TextBoxX1.Text
.Cells(than).Value = TextBoxX2.Text
.Cells(lace).Value = TextBoxX3.Text
.Cells(color).Value = ComboBoxEx1.Text
.Cells(barcode).Value = ComboBoxEx1.Text
.Cells(received).Value = TextBoxX4.Text
.Cells(description).Value = TextBoxX5.Text
End With
when i click on the add button the data is added to the datagridview. Now what i want is to save this data that i added in unbounded datagridview to my database table.
Plz Help Me...

Thank You.

From DataGridView to SQL Server?
Imports System.Data.SqlClient
Public Class Form1
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim cmdBuilder As SqlCommandBuilder
Dim ds As New DataSet
Dim changes As DataSet
Dim sql As String
Dim i As Int32

Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
connetionString = Data Source=EXCEL-PC\EXCELDEVELOPER;Initial Catalog=AdventureWorksLT2012;Trusted_Connection=True;
connection = New SqlConnection(connetionString)
sql = Select * from SalesLT.Product
Try
connection.Open()
adapter = New SqlDataAdapter(Sql, connection)
adapter.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
connection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'NOTE: for this code to work, there must be a PK on the Table
Try
cmdBuilder = New SqlCommandBuilder(adapter)
changes = ds.GetChanges()
If changes IsNot Nothing Then
adapter.Update(changes)
End If
MsgBox(Changes Done)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

a simple table to demonstrate the behavior:

Hi,

Can you upload the file to SkyDrive and share the link of the uploaded file here.

Hi again,

Just realised that this thread.

Interactive report unique row count

Is asking the same question.

Will watch this also.

Cheers
Yog

Thanks Guys,

I feel I should jump to my DBA's defence here.

I totally agree with what you're saying. However, I probably unfairly laid all the blame with the DBA. The app I'm working on is part of a much wider set of applications that all, to a large extent, feed into a central "pot" of views. Changing these views is a big deal and involves all kinds of impact assessments that I don't even want to consider.

My DBA is involved in this process so it's probably unfair to just call him a DBA...

And, in case you're wondering, no, I'm not just grovelling to my DBA, he's not read this and would be tickled if he did!

Anyway, part of the development process is to use the Interactive reports to query ALL of the columns in a given view (and again there are loads of these views). To keep development under control it was agreed early on that changes to reported data should be handled centrally by a seperate development team, hence select * from view. I didn't actually say that it was the DBA that did this by the way.

Anyway, I'm not here to justify any of the above. Just didn't want to see a collegue hammered in a forum because I didn't explain myself properly.

Apologies all round!

Anyway Iulian, if the offer is still open I'd love to find out more. I will email you directly.

Cheers
Yog

i i had a datagrid view bind to my database table items

after i click on a button i want the product quantity to minus 1 when it is added to another datagridview2.
anyone can help me or hint?
' add item to receipt
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' get item info
Dim R As POS.POSDS.ItemsRow = Button1.Tag

Dim I As Integer
Dim ItemLoc As Integer = -1
For I = 0 To DGV2.Rows.Count - 1
If R.Barcode = DGV2.Rows(I).Cells(0).Value Then

ItemLoc = I
Exit For

End If
Next

' if item is not found, add it
If ItemLoc = -1 Then
DGV2.Rows.Add(R.Barcode, R.ItemName, R.BuyPrice, R.SellPrice, 1, R.SellPrice)

Else
' if item is already there increase its count
Dim ItemCount As Long = DGV2.Rows(ItemLoc).Cells(4).Value
ItemCount = 1
Dim NewPrice As Decimal = R.SellPrice * ItemCount
DGV2.Rows(ItemLoc).Cells(4).Value = ItemCount
DGV2.Rows(ItemLoc).Cells(5).Value = NewPrice

End If

TextBox1.Text =
TextBox1.Focus()

' total
Dim Sum As Decimal = 0
For I = 0 To DGV2.Rows.Count - 1
Sum = DGV2.Rows(I).Cells(5).Value
Next

TextBox4.Text = Sum
payment.Totaltb.Text = Sum

End Sub

i cant do tat. and tat will be too complicated for me.
because in my datagridview2(DGV2) my col is different from database. i added other column in as well which is result of DGV1.
but i wan to update dgv1 abt the stock quantity . where stock quantity column is not in dgv2

Hi,

I'd like to count the number of cells in a column with zero as the value (or another arbitrary number). Ideally, the results would be outputted as a list of counts. Can someone point me in the right direction? Thank you.

Regards,

Will

Something like this should get you close:

dt Current Data Table();

cols = dt Get Selected Columns();

counts = NewTable("Counts for "||char(dtGet Name),newColumn("Column"),newColumn("Count"));

for(i = 1, i = NItems(cols), i++,

r = dt Select Where( ascolumn(eval(cols[i])) == 0 );

counts AddRows({:Column = column(dt,cols[i]) Get Name, :Count = NItems(r)});

);

I didn't test this.

Is there a way for me to set up cells that store numbers representing a priority? For example, I'd like to specify 10 cells that are "priority" cells. Each of these cells can store a number, 1-10. Each unique number (e.g., "2") can only exist in one of these cells at any one time. If I change the number in a cell to "2", then whatever cell use to contain "2" becomes the old value of the cell that was just changed.

Does that make sense?

alephx01 wrote:
... If I change the number in a cell to "2", then whatever cell use to contain "2" becomes the old value of the cell that was just changed.

Is this the way you'd really want this to work? Its seems to me that, for assigning priorities, "bump-down" behavior would be more appropriate (less chaotic) than the exchange behavior:

+bump-down example changing the priority of the first item from 7 to 2+

7 2
8 8
2 3
3 4
4 5
5 6
6 7
0 0
1 1
9 9

This preserves the existing relative order of the unchanged items.

+exchange example changing the priority of the first item from 7 to 2+

7 2
8 8
2 7
3 3
4 4
5 5
6 6
0 0
1 1
9 9

Here the old 2 priority gets shoved rather arbitrarily to priority 7. This doesn't seem usually useful since assigning the first item to 2 doesn't mean the the old 2 is arbitrarily lower. The bump-down method works best when the list is mostly prioritized correctly (which become more and more true as more and more priorities are assigned).

This is not entirely and academic discussion. I'm trying to think of a way to solve the problem, but the exchange behavior is problematic (and perhaps even less desirable).

hi frnds.....
this is my scenario.........
for button_click
dglr.Rows.Add()
dglr.Rows(i).Cells(1).Value = Surveyno.Text
dglr.Rows(i).Cells(7).Value = TextBox3.Text + . + TextBox4.Text + TextBox5.Text
dglr.Rows(i).Cells(8).Value = ListBox1.Items.Count
For i = 0 To dglr.Rows.Count - 1
dglr.Rows(i).Cells(0).Value = TextBox1.Text + 1
TextBox1.Text = dglr.Rows(i).Cells(0).Value
Next
btnsave_click
cmd.Connection = con
con.Open()
For i = 0 To dglr.Rows.Count - 1
cmd.CommandText = insert into land values( ' DCODE.Text ',' mcode.Text ',' vcode.Text ',' dglr.Rows(i).Cells(0).Value ',' dglr.Rows(i).Cells(1).Value ',' dglr.Rows(i).Cells(2).Value ',@extent,' dglr.Rows(i).Cells(4).Value ',' dglr.Rows(i).Cells(5).Value ',' dglr.Rows(i).Cells(6).Value ',@lrextent,' dglr.Rows(i).Cells(8).Value ')
Dim p1, p2
p1 = cmd.Parameters.Add(@extent, OleDbType.Numeric)
p1.value = dglr.Rows(i).Cells(3).Value
p2 = cmd.Parameters.Add(@lrextent, OleDbType.Numeric)
p2.value = dglr.Rows(i).Cells(7).Value
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Next
con.Close()
 
now when i use this scenario.......once after save button is click.....the program works fine.........
next when i click button_click,i am getting exception as .......
index was out of range.must be non negative and less than the size of the collection.parameter name:index
 
please help me out........
thank u
in advance..

hi.......
thank u very very very much............i have solved my problem...........
 
thank u..........

I'm trying to input text into AppleWorks spreadsheet cells, but the text gets truncated at 255 characters, even after I've adjusted the row height and column width (and also used the wrap function). My DOS is 10.2.8. I've scoured the help manual, but can't find anything on character count. Can anyone help with this?

freelancer60,

If you are entering more than 255 characters into a spread sheet cell, you might benefit from moving to a database. Older versions of Filemaker can be found for as $5. The numeric fields are limited to 255 characters but the text fields can handle large files. What is your end product?

Jim

Hi,

I have a list of names in column 'A', there ae several hundered of them, in column 'B' i have a list of text values, they are 'billable', 'shared contract', 'already paid'. In column 'C' is the manager name.each name in 'A' has one of these text values
next to them, for example,A B C
bill billable Jake
June Shared contractAndrew
Harry Billable Andrew
greg BillableJake
All names occur more than once, and can have the same text value in column B, what i would like is a formula that would return the total number of UNIQUE names in column 'A'based on meeting criteria in B and C, e.g. the name Jake in column 'C' and 'Billable'
in column B

Thanks in advance

Hello,

The following code does not work Dim RowNumber As Long, RefCellControl As Long, CountIndex As Integer
RefCellControl = ClearMin.ValueCells(1, K).SelectActiveCell.Value = RefCellControl
CountIndex = 0 RowNumber = 3 Cells(RowNumber, G).Select ActiveCell.Value = Me.ClearMin Set RefCell = Me.ClearMin For E_CellsIndex = 1 To Me.RNumber Cells(E_CellsIndex, E).Select Set curCell = ActiveCell If curCell = RefCell Then CountIndex = CountIndex + 1ElseEnd If
Next
Cells(RowNumber, H).SelectActiveCell.Value = CountIndexwhen I replace RefCell with the value 0.02 which is the value of variable on form text box ClearMinif statment will work and will return the correct count of cells where the value is less than 0.02Does anyone knows why?

Are you attempting to compare string values when you really want to compare the values of those strings converted to doubles?

Hi,
The intent of the code is to open an xls file -minimize it , search for keywords .
there are columns like test case name ,priority(simple ,medium and high) with associated keywords.If keywords are found, it shld save the test case name - Priority in a text box.
When i run this code i am getting err in

txtDisplay.Text = txtDisplay.Text
Operator
'' is not defined for string and type 'Range'.
I
tried replacing with still am gettign the same err Operator not defined.

Also the variable vtemp, if i dont declare it to null vtemp= ,i am getting an err stating that vtemp is used before assinging
a value.
Pls advice

xlBook.Worksheets(2).Cells(iRowIndex, 3) --
Simple

Private
Sub cmdScriptsSearch_Click(ByVal
sender As System.Object,
ByVal e
As System.EventArgs)
Handles cmdScriptsSearch.Click

'Declare variables locally to search for scripts in the Excel

Dim vSimpleCount, vComplexCount, vMediumCount
As
Integer

Dim vtemp
As
String
vtemp =

Dim iRowIndex, iColumnIndex, iSearchText
As
Integer

Dim xlBook
As
Object
frmAssesment.Label2.Visible =
False
frmAssesment.txtBScriptsCount.Visible =
False

'Store the Script Excel path in the Objext
xlBook = GetObject(
C:\Documents and Settings\WAW165\Desktop\Impact assessment sheet.xls)
xlBook.Application.Visible =
True
xlBook.Windows(1).Visible =
True
xlBook.Application.Windowstate = Excel.XlWindowState.xlMinimized
vSimpleCount = 0
vMediumCount = 0
vComplexCount = 0

'Label3.Visible = True
ProgressBar1.Visible =
True
Button2.Enabled =
False
cmdScriptsSearch.Enabled =
False

'Search for Scripts in the Excel

For iRowIndex = 3
To 168
ProgressBar1.Value = (iRowIndex / 168) * 100

For iColumnIndex = 6
To 16

For iSearchText = 1
To 30

If ((xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value)
)
Then

'Searching for the Keyword in the Excel

If ((xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value) = avFoundKeywordds(iSearchText))
Then

If (vtemp (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value))
Then

If ((xlBook.Worksheets(2).Cells(iRowIndex, 16).Value) =
Simple)
Then
vtemp = (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)

'Storing the Simple Scripts
txtDisplay.Text = txtDisplay.Text
xlBook.Worksheets(2).Cells(iRowIndex, 3)
--
Simple

'Increment the Simple Scripts Count
vSimpleCount = vSimpleCount 1

End
If

If ((xlBook.Worksheets(2).Cells(iRowIndex, 16).Value) =
Medium)
Then
vtemp = (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)

'Storing the Medium Scripts
txtDisplay.Text = txtDisplay.Text
xlBook.Worksheets(2).Cells(iRowIndex, 3)
--
Medium

'Increment the Medium Scripts Count
vMediumCount = vMediumCount 1

End
If

If ((xlBook.Worksheets(2).Cells(iRowIndex, 16).Value) =
High)
Then
vtemp = (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)

'Storing the Complex Scripts
txtDisplay.Text = txtDisplay.Text
xlBook.Worksheets(2).Cells(iRowIndex, 3)
--
High

'Increment the Complex Scripts Count
vComplexCount = vComplexCount 1

End
If

End
If

End
If

End
If

Next iSearchText

Next iColumnIndex

Next iRowIndex

'Store the Total no of Scripts
txtScriptsCount.Text = vComplexCount vMediumCount vSimpleCount
frmAssesment.txtScriptsCount.Text = txtScriptsCount.Text
frmAssesment.txtBScriptsCount.Text = txtScriptsCount.Text
txtSimple.Text = vSimpleCount
txtMedium.Text = vMediumCount
txtComplex.Text = vComplexCount
frmAssesment.Show()

frmAssesment.Label2.Visible =
False
frmAssesment.txtBScriptsCount.Visible =
False

Me.Hide()

End
Sub

The Problem is the concatenation of with

xlBook.Worksheets(2).Cells(iRowIndex, 3)

Hello All,

I have 4 separate sub-programs that write 2D arrays (grids; I use these words interchangeably) with unique numerical attributes per array cell that I require for an overarching program that will be used to analyze their components together and complete a decision-making
task useful for mapping movement paths over a spatially-referenced grid in other software.

I would like to know how to (or what specific methods are available to) read the attributes of a specific set of cells for their unique numerical values?

What I want to do is reference the cells in memory using indexed array-positions.

For example (imagine a grid matrix with 9 cells, similar to a tic-tac-toe matrix; the center cell being the cell of first location or positioning), the cells would be referenced as follows:

Center cell = (i, j)
Northernmost cell = (i-1, j)
NW cell= (i-1, j-1)
NE = (i-1, j1)
West = (i, j-1)
East = (i, j1)
Southernmost = (i1, j)
SW = (i1, j-1)
SE = (i1, j1)
Again, the goal here is to use the computer to read the attributes of these cells for future decision-making. Said attributes would then be adjusted
with a multiplier, and the greatest numerical value would be chosen as the next location moved to in the grid. All of these steps (in an iteration loop) would then be written to a text file in a consecutive list.

Does anyone have any suggestions on how to read those specific cells using such array-position references?

Thank you for your help in advance. Any suggestions or specific help would be much appreciated.

Sincerely,
ADAndrewDen

This might be too late - not been on line since last Monday.
I'm puzzled by reference to startLocation as though it was a singlevalue- if it is an entry in a 2D array it must have two (the row and the column coordinate). Without seeing the declarations I don't know how you are using one value.
Here's a modified version of the pseudo-code I posted before. It is reasonably close to C# but lacks declarations. As before the loop conditions might need adjusting for cells on the borders of the array.
startLocation_i = something
startLocation_j = something
max = grid[startLocation_i, startLocation_j]
for i = startLocation_i; i = startLocation_i1; i {
for j = startLocation_j-1; startLocation_j = s1; j {
if grid[i, j] max {
max = grid[i,j]
row = i
col = j
}
}
}
//At this point max contains the value of the cell with largest value
// row and col have the indexes for that cell, so set startLocation_* from row, col.
startLocation_i = row
startLocation_j = col
Regards David R
---------------------------------------------------------------
The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
Every program eventually becomes rococo, and then rubble. - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

I am using the following formula to count for unique values in a table column:
=SUM(IF(FREQUENCY(MATCH(A5:A11,A5:A11,0),MATCH(A5:A11,A5:A11,0))0,1))
With the current formula, I have to manually change the range each time I add information. I would like to be able to set the range (a5:a100). The current formula will not work if there are blank cells in the range. How can I have count for unique values
minus the blank cells?
Thanks!

I am using the following formula to count for unique values in a table column:
=SUM(IF(FREQUENCY(MATCH(A5:A11,A5:A11,0),MATCH(A5:A11,A5:A11,0))0,1))
With the current formula, I have to manually change the range each time I add information. I would like to be able to set the range (a5:a100). The current formula will not work if there are blank cells in the range. How can I have count for unique values
minus the blank cells?
Thanks!

You're working with a relatively small range so in that case I would use this normally entered formula:
=SUMPRODUCT((A5:A100"")/COUNTIF(A5:A100,A5:A100""))

And, if the data happens to be numbers it's even easier.
=SUM(--(FREQUENCY(A5:A100,A5:A100)0))
--
Biff
Microsoft Excel MVP

Hola mi duda es la siguiente, tengo un datagridview en el cual al presionar un boton me manda los datos de las celdas a unos labels, el problema me surge cuando no existe ningun dato en alguna fila.
Este codigo ocupo para mandar los datos a los labels
lbl1.Text = DataGridView3.Rows(0).Cells(0).Value
lblT1.Text = DataGridView3.Rows(0).Cells(1).Value

lbl2.Text = DataGridView3.Rows(1).Cells(0).Value
lblT2.Text = DataGridView3.Rows(1).Cells(1).Value

lbl3.Text = DataGridView3.Rows(2).Cells(0).Value
lblT3.Text = DataGridView3.Rows(2).Cells(1).Value

lbl4.Text = DataGridView3.Rows(3).Cells(0).Value
lblT4.Text = DataGridView3.Rows(3).Cells(1).Value

lbl5.Text = DataGridView3.Rows(4).Cells(0).Value
lblT5.Text = DataGridView3.Rows(4).Cells(1).Value

me arroja error cuando en la fila 3 por ejemplo no hay ningun dato, el error que tira es porque ese fila no existe cuando no hay ningun dato. Espero su ayuda gracias.

Ahora si funciono, gracias te pasaste

How do I create a formula that will count only non-blank cells in a column that meets a specific criteria based ona value/text being = to a specific criteria?
For example, I have a range of data A3:D25. I only want to count the non-blankcells in D3:D25 if the value in A3:A25 is A2with the answer being in A1. I've done this with sumsbefore using an array formula, but I can't get it to work with counting.

Hi,
Try this in a1
=SUMPRODUCT((D3:D25"")*(A3:A25=A2))

If this post answers your question, please mark it as the Answer.
Mike H

I am counting a Column of cells that will never contain (and, because of flowing text,
can never contain) values, by format color. NO Conditionals (since there will never be a cell value) and no other 'Value' assigned to the cell, so none of the 'COUNT' nor 'COUNTIF' work.

I've seen other posts that talk about a Macro and that is fine, if we can count fill color orfill patterned cells.

Everything I've tried hasn't worked (from special cell format codes to Conditional Formats to Macros to assign a number to a counter when a format is applied to a cell in another cell). HELP : )

Thx! Doug

Hi
FirstVette,

you have to use the below macro to get your cell counted
Code:

Function sum_color(myRange As Range, myColor As Range, Optional myType = 0) As Double
Dim summ As Double
Dim clr As Long
clr = myColor.Interior.Color

summ = 0
For Each c In myRange
If clr = c.Interior.Color Then
If myType = 1 Then
summ = summ + c.Text
ElseIf myType = 0 Then
summ = summ + 1
End If
End If
Next
sum_color = summ
End Function

then try the following function as well

Try the following

To Count: =Sum_color(Range,Color Range,0)
To Sum : =Sum_color(Range,Color Range,1)
Default is Count: =Sum_color(Range,Color Range)

tested sheet using count cell color

finally you have to save your sheet with .xlsm extension

Gratefully,