-
RELEVANCY SCORE 3.77
DB:3.77:How Can I Count Unique Text Cells That May Also Contain Blenk Cells 97
I have data that looks like the following in a given column
Mary
Dave
Mary
Mary
Tom
Dave
Jim
Barney
"Blank Cell"
LeoI want to be albe to count the number of unique text occurances and also ingnore the blank cells. The answer shjold be 6
DB:3.77:How Can I Count Unique Text Cells That May Also Contain Blenk Cells 97
Dear Ron,
Will you please explain me the calculationsteps of your formula.
Best Regards,
Mohammed Nafi. -
RELEVANCY SCORE 3.74
DB:3.74:Count Of Unique Text-Value Cells In One Column That Also Have Specific Text Values In Two Other Columns 9m
I have a worksheet that has 28 columns, with data in almost 11,000 rows.
Column D is "name" which is a text string
Column X has text values of either Yes or No
Column AA has a 4 character text string (e.g. ABCD - only 6 possible values in this column, representing business units)I need to count the number of unique values in D for each unique value in AA, e.g. ABCD has 5 unique names, EFGH has 30 unique names, etc. This is UniqueNamesPerBusinessUnit
Next, I need to count the number of unique values in D that also have the value Yes in X, for each value in AA. This is UniquePositiveNamesPerBusinessUnit
This will allow me to then get a "score" percentage of UniquePositiveNamesPerBusinessUnit/UniqueNamesPerBusinessUnit
I'm more than willing to make intermediate calculations in separate cells - one for each business unit for UniqueNames and then UniquePositiveNames?
I've tried samples that use matches within frequency, but only ever get #N/A values when copying the formula from the office site examples, even just to get a unique list of names, but I need to also consider the extra parameters of business unit, and then
the Yes/No in column X, and at that point my eyes glaze over.Ideas?
DB:3.74:Count Of Unique Text-Value Cells In One Column That Also Have Specific Text Values In Two Other Columns 9m
Thank you so much! That works perfectly.
-
RELEVANCY SCORE 3.40
DB:3.40:How Do I Count The Number Of Non-Blank Cells Between Blank Cells a7
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:
DB:3.40:How Do I Count The Number Of Non-Blank Cells Between Blank Cells a7
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 -
RELEVANCY SCORE 3.34
DB:3.34:Creating A Combined Formula xx
I am trying to count the number of cells in the column thatcontain a date (only those that have started have anything entered)which I can accomplish by using this formula =COUNTIFS(J2:J2248,"0") how ever, I need to only count the rows that have unique
values in a second column which I can use this formula for =SUM(IF(FREQUENCY(MATCH(B2:B2248,B2:B2248,0),MATCH(B2:B2248,B2:B2248,0))0,1)).My question is how do I combine the formulas to count for both criteria at one time.
The second column has text data that sometimes appears in more than one row so I need to be able to count the start dates for only unique second column data so as not to double count
DB:3.34:Creating A Combined Formula xx
As an array formula, confirmed with Ctrl+Shift+Enter:
=SUM(IF(FREQUENCY(IF(J2:J22480,MATCH(B2:B2248,B2:B2248,0)),MATCH(B2:B2248,B2:B2248,0))0,1))
-
RELEVANCY SCORE 3.32
DB:3.32:Count The Numbers Within An Array Entered As Text With Comma Separators 3c
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 arrayCan 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 stuckWho has the answer?
Thank you
Ron1Cebu
DB:3.32:Count The Numbers Within An Array Entered As Text With Comma Separators 3c
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 -
RELEVANCY SCORE 3.16
DB:3.16:Using Grids ms
I am trying to hspan the submit button in this code:
def formGrid: Grid = Grid {
rows: [
GridRow { cells: [Label { text: "Name:" }, TextBox {}] }
GridRow { cells: [Label { text: "Value:" }, TextBox {}] }
GridRow { cells: [Button { text: "Submit" }]}
]
}How do I do that?DB:3.16:Using Grids ms
I am trying to hspan the submit button in this code:
def formGrid: Grid = Grid {
rows: [
GridRow { cells: [Label { text: "Name:" }, TextBox {}] }
GridRow { cells: [Label { text: "Value:" }, TextBox {}] }
GridRow { cells: [Button { text: "Submit" }]}
]
}How do I do that? -
RELEVANCY SCORE 3.08
DB:3.08:Excel 2007 Vba - Rte 91 Object Variable Or With Block Variable Not Set sx
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 LongRem 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 = 2Application.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 = 2Rem 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 WithApplication.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:=FalseApplication.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 = strExtCleanApplication.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
LoopRem 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).ValueRem 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
LoopRem once restoral count process is complete remove any duplicate ticket entries.
Sheets(strResCount).Select
ActiveSheet.ListObjects(1).DataBodyRange.RemoveDuplicates Columns:=2, Header:=xlNoStop
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 WithRem 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).ValueRem 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 WithApplication.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 WithApplication.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 IfApplication.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 WithApplication.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).SelectApplication.StatusBar = (10 of 12) Associate tab process is Format((lngLookUpRow - 2) / (lngLookUpLRow - 2), Percent) Complete:
lngLookUpRow = lngLookUpRow 1
LoopApplication.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 SubFunction 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 FunctionI'm new, please help. Begging for forgiveness in advance.
DB:3.08:Excel 2007 Vba - Rte 91 Object Variable Or With Block Variable Not Set sx
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.
-
RELEVANCY SCORE 3.07
DB:3.07:Get Every Nth Row From Datagridview dz
Hi all,
I have two DataGridViews, one displays months running for 25 years, the other displays years for 25 years.
I need to display some of the columns from dgvMonths to dgvYears, but, as implied, I need to show the corresponding year to the right month.
For example Month 12 would be showed in Year 1, and Month 24 would be shown in Year 2 - if I display the start of year it will confuse the clients quite a bit, as they won't quite know what's going on as well as what their position will be at the end of
the particular year.
I'm using the code below at the moment, but it's not right because at the end of year 1 (month 12) it shows month 13 instead of month 12.Dim count As Integer
count = (dgvYears.Rows.Count - 1) * 12
Dim x As Integer = 0
For i = 0 To count Step 12
dgvYears.Rows(x).Cells(1).Value = dgvMonths.Rows(i).Cells(32).Value
dgvYears.Rows(x).Cells(2).Value = dgvMonths.Rows(i).Cells(10).Value
dgvYears.Rows(x).Cells(3).Value = dgvMonths.Rows(i).Cells(17).Value
dgvYears.Rows(x).Cells(4).Value = 0 'Leases
dgvYears.Rows(x).Cells(5).Value = dgvMonths.Rows(i).Cells(20).Value
dgvYears.Rows(x).Cells(6).Value = dgvMonths.Rows(i).Cells(23).Value
dgvYears.Rows(x).Cells(7).Value = dgvMonths.Rows(i).Cells(26).Value
dgvYears.Rows(x).Cells(8).Value = dgvMonths.Rows(i).Cells(29).Value
dgvYears.Rows(x).Cells(9).Value = dgvMonths.Rows(i).Cells(31).Value
dgvYears.Rows(x).Cells(10).Value = dgvMonths.Rows(i).Cells(30).Value
x = x 1
NextDB:3.07:Get Every Nth Row From Datagridview dz
All I had to do was comment out x = x1 and it works perfectly.
Thank you!!
Verraine -
RELEVANCY SCORE 3.02
DB:3.02:Text Converted Into Value 97
All my text cells in my excel sheet have been converted into #Value :( The numbers appear the same thankfully :)
DB:3.02:Text Converted Into Value 97
It could be due to a corruption in the workbook.
-
RELEVANCY SCORE 3.01
DB:3.01:Counta Text Exclude Cell With Formulas Or Errors s1
I am counting a range of cells contain TEXT. This is derived from a formula which includes IFERROPR. I want to modify CONTA to only count cells containing text, exclude cells containing formulas.
Thank youDB:3.01:Counta Text Exclude Cell With Formulas Or Errors s1
Thanks, does the trick...
Phil
-
RELEVANCY SCORE 2.98
DB:2.98:Date In Vba Form Comes Into Worksheet As Text c7
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).RowlPart = 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 SubDB:2.98:Date In Vba Form Comes Into Worksheet As Text c7
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
-
RELEVANCY SCORE 2.98
DB:2.98:Display Largest Value In A Text Object 9m
Hello,I'm testing to visualize my IIS website log-files with Qlikview. What I am struggling with is to create a text object to display the day with the highest amount of unique client visits.
Below expression I use in a chart with date as my dimension which is limited to output the largest 10 values.
Count(Distinct(Hostname))DB:2.98:Display Largest Value In A Text Object 9m
Try like this=FirstSortedValue( aggr(Date, Date), aggr( -count(distinct Hostname), Date))
to get the Date, and
=max(aggr( count(distinct Hostname), Date))
to get the max count.
edit:
and for your last request
=count({Date = {'$(=today())'}} distinct Hostname)
or
=count(distinct if(Date = today(), Hostname))
-
RELEVANCY SCORE 2.97
DB:2.97:Como Separar Nomes Datagridview zj
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ódigoPrivate 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 = 190End 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 = 05402838860DataGridView1.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 ZANARDIDataGridView1.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 ZanardiEnd Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim CPF, NomeResponsavel, NomeCrianca As StringFor 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 vbNewLineFor L = 0 To DataGridView1.Rows.Count - 1
If CPF = DataGridView1.Rows(L).Cells(0).Value Then
TextBox1.Text = TextBox1.Text _
BENEFICIARIO: NomeCrianca vbNewLineEnd If
Next
TextBox1.Text = vbNewLine
Next
End Sub
E faz isso
E o que eu quero seria isso
Obrigada
DB:2.97:Como Separar Nomes Datagridview zj
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.
TipoImports System.Windows.Forms
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Text
Imports System.Text.RegularExpressionsPublic 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 = 150End 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 = 05402838860DataGridView1.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 ZANARDIDataGridView1.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 ZanardiDataGridView1.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/2011End Sub
Private Class Responsavel
Friend CPF As String
Friend Nome As String
Friend dtn As String
Friend Beneficiarios() As StringFriend 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 FunctionFriend Sub Adicionar(ByRef Beneficiario As String)
ReDim Preserve Me.Beneficiarios(0 To Me.Beneficiarios.Length)
Me.Beneficiarios(Me.Beneficiarios.Length - 1) = Beneficiario
End SubFriend 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 = TextEnd 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 RowsalvarComo.CheckFileExists = False
salvarComo.Filter = Documentos de texto (*.txt)|*.txt
caminho = salvarComo.ShowDialog
Arquivo = salvarComo.FileNamefluxoTexto = 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 REnd Using
fluxoTexto.Close()
End SubEnd Class
-
RELEVANCY SCORE 2.97
DB:2.97:Error En Conversion Al Pasar De Un Gridview A Textbox jc
Estimados,
Mi problema es el siguiente, estoy pasando datos de una fila del DataGridView a controles TextBox en otro form.
Con el siguiente codigo realizo el traspaso:Private Sub dgvCertificados_CellClick(ByVal sender
As Object,
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
Handles dgvCertificados.CellClickfrmMostrar.txtRut.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(6).Value()
frmMostrar.txtDV.Text =
Me.dgvCertificados.Rows(e.RowIndex).Cells(7).Value()frmMostrar.txtFecNac.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(4).Value()
frmMostrar.txtApat.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(1).Value()
frmMostrar.txtAmat.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(2).Value()
frmMostrar.txtNombre.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(3).Value()
frmMostrar.txtComuna.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(8).Value()
frmMostrar.txtNumRes.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(9).Value()
frmMostrar.txtFecRes.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(10).Value()
frmMostrar.txtDiag1.Text =
Me.dgvCertificados.Rows(e.RowIndex).Cells(11).Value()frmMostrar.txtDiag2.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(12).Value()
frmMostrar.txtDiag3.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(13).Value()
frmMostrar.txtDiag4.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(14).Value()
frmMostrar.txtDiscFisica.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(15).Value()
frmMostrar.txtDiscSensorial.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(16).Value()
frmMostrar.txtDiscMental.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(17).Value()
frmMostrar.txtDiscNoEsp.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(18).Value()
frmMostrar.txtObs.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(22).Value()
frmMostrar.txtSubcom.Text = Me.dgvCertificados.Rows(e.RowIndex).Cells(21).Value()
Me.Close()
End Sub
El error que me muestra es el siguiente:
Alguna sugerencia???
SaludosDB:2.97:Error En Conversion Al Pasar De Un Gridview A Textbox jc
Estimado, funciono perfecto la solución.
Saludos
-
RELEVANCY SCORE 2.95
DB:2.95:How To Count Unique Strings Within A Cell Range? sc
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.HomeCookNDB:2.95:How To Count Unique Strings Within A Cell Range? sc
Thank you very much, Hans! This perfect. I guess I needed to understand COUNTIF first.HomeCookN
-
RELEVANCY SCORE 2.95
DB:2.95:Insert Into p7
Trying to insert an Excel Spreadsheet into a Access Database.
Populating to a blank database with fields of Index(Primary Key and Unique), DatePaid, WhatPaid, AmtPaid, and Total.
The connection and recordset work just the SQL statement is wrong:
For k= 1 to Sheets(2013).Cells(Rows.Count, A).End(xlUp).Row - 4
SQLstr = INSERT INTO [table1]([Index],[DatePaid],[WhatPaid],[AmtPaid],[Total]) VALUES (Sheets(2013).Cells(k 4, 1).Value, Sheets(2013).Cells(k 4, 2).Value, Sheets(2013).Cells(k 4, 3).Value, Sheets(2013).Cells(k
4, 4).Value, Sheets(2013).Cells(k 4, 5).Value);
Next kDB:2.95:Insert Into p7
Date values have to be enclosed in # and formatted in mm/dd/yyyy or yyyy-mm-dd format.
Assuming that DatePaid and DateRec are date/time fields:
For k = 1 To Sheets(2013).Cells(Rows.Count, A).End(xlUp).Row - 4
SQLstr = INSERT INTO [table1] ([Index],[DatePaid],[WhatPaid], _
[AmtPaid],[Total],[AmtRec],[WhatRec],[DateRec]) _
VALUES ( _
Sheets(2013).Cells(k 4, 1).Value , # _
Format(Sheets(2013).Cells(k 4, 2).Value, yyyy-mm-dd) #, ' _
Sheets(2013).Cells(k 4, 3).Value ', _
Sheets(2013).Cells(k 4, 4).Value , _
Sheets(2013).Cells(k 4, 5).Value , _
Sheets(2013).Cells(k 4, 6).Value , ' _
Sheets(2013).Cells(k 4, 7).Value ', # _
Format(Sheets(2013).Cells(k 4, 8).Value, yyyy-mm-dd) #);
cn.Execute CommandText:=SQLstr, Options:=adExecuteNoRecords
Next kRegards, Hans Vogelaar
-
RELEVANCY SCORE 2.94
DB:2.94:Need To Count Data In One Column Based On Conditions Of Another Column. dx
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.DB:2.94:Need To Count Data In One Column Based On Conditions Of Another Column. dx
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
-
RELEVANCY SCORE 2.94
DB:2.94:Execution Time Too High ! ds
Hello All,PFB the code for a search button where when a user enters a text in the text box ,it takes the keyword compares it with an xls which has 1600 rows and 16 columns with keywords.
If the keywords match, it has to full the 3rd column where the name is specified.
Its running but its taking more than 20-25 mins for processing.
I knw searching 1600 rows and 16 columns will take time but pls advice if i can make this process
a lil faster.
Pls Advice
AbiPrivate
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
IntegerDim vtemp
As
String
vtemp =Dim iRowIndex, iColumnIndex, iSearchText
As
IntegerDim 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 sheet1.xls)'xlBook.Application.Visible = False
'xlBook.Windows(1).Visible = False
xlBook.Application.Windowstate = Excel.XlWindowState.xlMinimized' Dim a, b As String
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 1717
ProgressBar1.Value = (iRowIndex / 1717) * 100For iColumnIndex = 6
To 16For iSearchText = 1
To 30'a = xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value
'b = avFoundKeywordds(iSearchText)
If ((CStr(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 (a = b) Then
If (vtemp (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value))
ThenIf ((xlBook.Worksheets(2).Cells(iRowIndex, 16).Value) =
Simple)
Then
vtemp = (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)'MsgBox(vtemp)
TextBox1.Text = vtemp'Storing the Simple Scripts
' txtDisplay.Text = txtDisplay.Text xlBook.Worksheets(2).Cells(iRowIndex, 3) Simple
TextBox2.Text = TextBox2.Text TextBox1.Text
--Priority-Simple vbNewLine'Increment the Simple Scripts Count
vSimpleCount = vSimpleCount 1End
IfIf ((xlBook.Worksheets(1).Cells(iRowIndex, 16).Value) =
Medium)
Then
vtemp = (xlBook.Worksheets(1).Cells(iRowIndex, 3).Value)
TextBox1.Text = vtemp'Storing the Medium Scripts
' txtDisplay.Text = txtDisplay.Text xlBook.Worksheets(2).Cells(iRowIndex, 3) Medium
TextBox2.Text = TextBox2.Text TextBox1.Text
--Priority-Medium vbNewLine'Increment the Medium Scripts Count
vMediumCount = vMediumCount 1End
IfIf ((xlBook.Worksheets(1).Cells(iRowIndex, 16).Value) =
Complex)
Then
vtemp = (xlBook.Worksheets(1).Cells(iRowIndex, 3).Value)
TextBox1.Text = vtemp'Storing the Complex Scripts
' txtDisplay.Text = txtDisplay.Text xlBook.Worksheets(2).Cells(iRowIndex, 3) High
TextBox2.Text = TextBox2.Text TextBox1.Text
--Priority-Complex vbNewLine'Increment the Complex Scripts Count
vComplexCount = vComplexCount 1End
IfEnd
IfEnd
IfEnd
IfNext 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 =
FalseMe.Hide()
End
Sub
EndClass
DB:2.94:Execution Time Too High ! ds
Thanks.. It helped. I need to export the values which is in a textbox to an excel.
I like creating a new xls where it should just ask for the file name and location .Thanks
Abi -
RELEVANCY SCORE 2.93
DB:2.93:Can A Formula Count Cells That Contain A Text String But Also Varying Additional Content zm
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?DB:2.93:Can A Formula Count Cells That Contain A Text String But Also Varying Additional Content zm
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).
-
RELEVANCY SCORE 2.93
DB:2.93:Count Visible Cells 1x
How can count visible cells?
Something similar to the sum visible cells.
Using macros.Thank you all again.
DB:2.93:Count Visible Cells 1x
Also check for the limit
http://www.rondebruin.nl/specialcells.htmRegards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Rick Rothstein"; "MVP - Excel" wrote in message news:*** Email address is removed for privacy ***...You single line solution is very nice, but should have some error trapping in case
Okay, fair point. Either of these two should work (not one-liners, but non-looping still the same) depending on how you like to handle errors....
Sub VisiCount()
Dim VisibleCount As Long
On Error Resume Next
VisibleCount = Selection.SpecialCells(xlCellTypeVisible).Count
Err.Clear
MsgBox VisibleCount
End Subor...
Sub VisiCount()
Dim VisibleCount As Long
On Error GoTo Whoops
VisibleCount = Selection.SpecialCells(xlCellTypeVisible).Count
Whoops: MsgBox VisibleCount
End SubNOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
-
RELEVANCY SCORE 2.93
DB:2.93:Excel Row Count In Vb.Net mf
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 advanceajay
DB:2.93:Excel Row Count In Vb.Net mf
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 inExcel for Developers forum.
Thank you for your understanding!
Best regards, -
RELEVANCY SCORE 2.92
DB:2.92:I Am Trying To Count Cells That Contain Certain Text, And I Need It To Be Case Sensitive. mp
In the range the cells might contain the text: lk CD, or LK mw, rh LK, etc, etc. I am trying to use a formula to count cells within the range that contain the lower case "lk". In another count I want to count the cells that contain the upper case "LK".
As noted above, some cells might contain additional text along with the upper or lower case text I am trying to countDB:2.92:I Am Trying To Count Cells That Contain Certain Text, And I Need It To Be Case Sensitive. mp
Thank you Lars, I think I understand. I am going to play with the formula with different arguments and hopefully understand it even better. I appreciate you taking your time to explain it to me. The Evaluate Formula seems like it will be helpful for
this and other formulas where I need help. -
RELEVANCY SCORE 2.92
DB:2.92:Count How Many Cells In A Row Have Data xd
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.
DB:2.92:Count How Many Cells In A Row Have Data xd
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
-
RELEVANCY SCORE 2.91
DB:2.91:Sales Order - Delivery Code... Help! :'( 1a
Hey I am becoming desprate, I have asked about this before with no response which means that it must be something with my code, so here is my code in hopes that someone can find my error.Thanks for the help...
Ok, Here is the situation:
I pull a sales order into a DataGridView.
This allows for the assigning of Serial Numbers from a UDT I created to manage serial numbers until they are ready for release.
I assign the serial numbers.
When the Delivery is to be created I flip through the rows of the DGV and pull the assosiated row from the SO to apply it and the assosiated serial number with in the Delivery.
bThe following code works great for 1-1 copies./b
The problem comes when a Row has been deleted from within SAP from the SO or When the user removes a row from the DGV that they don't want copied into the Delivery.
When this is tryed I get an error stating:
"-10 :: [DLN1.WhsC ode][line: 0]. 'Item'ACC-USB-LCD-MX233 ' with system serial 2 is not in stock"
The weird thing is I don't have that item anywhere in my sales order or in the XML export from the Delivery object.
It is not even the first Item in our item table.
What I do is grab the rows from the Sales Order based on LineNum.
If a row is not needed I ignore that row in the Sales Order by its LineNum.
I was wondering if this is a SAP BUG but I have not seen anyone else with this problem and can't seem to get SAP to respond to my Support Notes.
Here's my code:
if more info is needed please let me know, thanks
Dim soDoc As SAPbobsCOM.Documents
Dim dnDoc As SAPbobsCOM.DocumentssoDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders)
dnDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes)Try
Dim rsData As SAPbobsCOM.Recordset
Dim iTemp As IntegeriTemp = Integer.Parse(txtSalesOrder.Text)
rsData = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
rsData.DoQuery("Select DocEntry From ORDR where DocNum='" + iTemp.ToString + "'")soDoc.GetByKey(Integer.Parse(rsData.Fields.Item("DocEntry").Value))
dnDoc.CardCode = soDoc.CardCode
dnDoc.Comments = "Based On Sales Order " soDoc.DocNum ". For Notes see Sales Order."
dnDoc.DocDate = Today
dnDoc.ContactPersonCode = soDoc.ContactPersonCode
dnDoc.DocCurrency = soDoc.DocCurrency
dnDoc.DocDueDate = Today
dnDoc.DocObjectCode = SAPbobsCOM.BoObjectTypes.oDeliveryNotes
dnDoc.SalesPersonCode = soDoc.SalesPersonCode
If soDoc.DocumentsOwner 0 Then
dnDoc.DocumentsOwner = soDoc.DocumentsOwner
End If' For each item
' - Check to see if Item is in stock
' - - If not then make sure it is ok to continue without the item
' - Check to see if Item is Serialised
'- - If Item is serialised make sure there is a Serial number assisgned to it.
' - - If there is no serial number assigned Tell the user and stop creationDim count As Short
Dim items As SAPbobsCOM.Itemsitems = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems)
count = 0
While count dgvMat.Rows.Count
Dim lCount
lCount = 0
Do Until soDoc.Lines.LineNum = Integer.Parse(dgvMat.Rows(count).Cells("LineNum").Value.ToString)
soDoc.Lines.SetCurrentLine(lCount)
lCount += 1
Loop' MessageBox.Show(soDoc.Lines.LineNum.ToString + " :: " + soDoc.Lines.ItemCode.ToString + " :: " + dgvMat.Rows.Count.ToString + " :: " + count.ToString)
' MessageBox.Show(dgvMat.Rows(count).Cells("ItemCode").Value.ToString)
items.GetByKey(dgvMat.Rows(count).Cells("ItemCode").Value.ToString)If (items.InventoryItem = SAPbobsCOM.BoYesNoEnum.tNO) Or ((items.QuantityOnStock - Double.Parse(dgvMat.Rows(count).Cells("Quantity").Value.ToString)) 0) Then
' MessageBox.Show("Got here, Starting Base line :: " + count.ToString)
dnDoc.Lines.BaseEntry = soDoc.DocEntry
dnDoc.Lines.BaseLine = soDoc.Lines.LineNum
dnDoc.Lines.Quantity = (Double.Parse(dgvMat.Rows(count).Cells("Quantity").Value.ToString))
dnDoc.Lines.BaseType = SAPbobsCOM.BoObjectTypes.oOrders
dnDoc.Lines.LineTotal = (soDoc.Lines.Price * (Double.Parse(dgvMat.Rows(count).Cells("Quantity").Value.ToString))) ' * (dnDoc.Lines.Quantity / soDoc.Lines.Quantity)
' dnDoc.Lines.AccountCode = soDoc.Lines.AccountCode
dnDoc.Lines.Address = soDoc.Lines.Address
dnDoc.Lines.Currency = soDoc.Lines.Currency
dnDoc.Lines.ItemCode = soDoc.Lines.ItemCode
dnDoc.Lines.ItemDescription = soDoc.Lines.ItemDescription
dnDoc.Lines.WarehouseCode = soDoc.Lines.WarehouseCode
dnDoc.Lines.TaxCode = soDoc.Lines.TaxCodeDim qCount As Short
'Check if the item is managed by a serial number
If (items.ManageSerialNumbers = SAPbobsCOM.BoYesNoEnum.tYES) Then
qCount = 0'Check to see if its the last item in the list
If count + 1 dgvMat.RowCount Then
'Check to see if the following Row is the same as the current row
If dgvMat.Rows(count + 1).Cells("LineNum").Value = dgvMat.Rows(count).Cells("LineNum").Value Then
'Flip through the like rows
While dgvMat.Rows(count + 1).Cells("LineNum").Value = dgvMat.Rows(count).Cells("LineNum").Value
'For each simular Row, flip through the quantity
While qCount dgvMat.Rows(count).Cells("Quantity").Value
'Serial Number
Try
' Adds serial Numbers
rsData.DoQuery("SELECT SysSerial FROM OSRI WHERE IntrSerial = '" + dgvMat.Rows(count).Cells("SerialNumber").Value.ToString + "'")
dnDoc.Lines.SerialNumbers.InternalSerialNumber = dgvMat.Rows(count).Cells("SerialNumber").Value.ToString
dnDoc.Lines.SerialNumbers.SystemSerialNumber = rsData.Fields.Item("SysSerial").Value.ToString
dnDoc.Lines.SerialNumbers.BaseLineNumber = soDoc.Lines.BaseLineMessageBox.Show(soDoc.Lines.ItemCode.ToString + "::" + dgvMat.Rows(count).Cells("SerialNumber").Value.ToString + "::" + rsData.Fields.Item("SysSerial").Value.ToString)
Catch
MessageBox.Show("Error with Serial Number for Item: " + dgvMat.Rows(count).Cells("ItemCode").Value.ToString)
Exit Sub
End Try' If dgvMat.Rows.Count - 1 count Then
'dnDoc.Lines.SerialNumbers.Add()
' End If
'For each Quantity greater then 1, add the extra rows of serial numbers
If qCount + 1 dgvMat.Rows(count).Cells("Quantity").Value Then
dnDoc.Lines.SerialNumbers.Add()
End If'Increment the row count
count += 1
'Increment the Quantity Count
qCount += 1
End While
'if out of rows then leave the while loop
If count + 1 = dgvMat.RowCount Then
Exit While
End If
End While
End If
End If'if Quantity has not been counted then process the serial numbers
If Not qCount 0 Then
' For each Quantity, add a serial number
While qCount dgvMat.Rows(count).Cells("Quantity").Value.ToString
'Serial Number
Try
' Add the serial Numbers
rsData.DoQuery("SELECT SysSerial FROM OSRI WHERE IntrSerial = '" + dgvMat.Rows(count).Cells("SerialNumber").Value.ToString + "'")
dnDoc.Lines.SerialNumbers.InternalSerialNumber = dgvMat.Rows(count).Cells("SerialNumber").Value.ToString
dnDoc.Lines.SerialNumbers.SystemSerialNumber = rsData.Fields.Item("SysSerial").Value.ToString
dnDoc.Lines.SerialNumbers.BaseLineNumber = soDoc.Lines.BaseLineMessageBox.Show(soDoc.Lines.ItemCode.ToString + "::" + dgvMat.Rows(count).Cells("SerialNumber").Value.ToString + "::" + rsData.Fields.Item("SysSerial").Value.ToString)
Catch ex As Exception
MessageBox.Show("Error with Serial Number for Item: " + dgvMat.Rows(count).Cells("ItemCode").Value.ToString + " :: " + ex.Message)
Exit Sub
End Try'For each Quantity greater then 1, add the extra rows of serial numbers
If qCount + 1 dgvMat.Rows(count).Cells("Quantity").Value Then
dnDoc.Lines.SerialNumbers.Add()
End If' For each quantity advance the row count
If qCount + 1 dgvMat.Rows(count).Cells("Quantity").Value Then
count += 1
End If'increment the quantity count
qCount += 1
End While
End If
Else
qCount = 0If count + 1 dgvMat.RowCount Then
If dgvMat.Rows(count + 1).Cells("LineNum").Value = dgvMat.Rows(count).Cells("LineNum").Value ThenWhile dgvMat.Rows(count + 1).Cells("LineNum").Value = dgvMat.Rows(count).Cells("LineNum").Value
While qCount dgvMat.Rows(count).Cells("Quantity").Value
If (qCount + 1) dgvMat.Rows(count).Cells("Quantity").Value Then
count += 1
End If
qCount += 1
End While
End While
End If
End IfIf Not qCount 0 Then
While qCount dgvMat.Rows(count).Cells("Quantity").Value
If (qCount + 1) dgvMat.Rows(count).Cells("Quantity").Value Then
count += 1
End If
qCount += 1
End While
End If
End IfElse
MessageBox.Show("There are not enough of item '" + dgvMat.Rows(count).Cells("ItemCode").Value.ToString + "' to fill this order.")
Exit Sub
End If
If count dgvMat.RowCount - 1 Then
dnDoc.Lines.Add()
End If
count += 1
End WhileDim lretvel, errcode As Long
Dim errmsg As String
Dim oDoc As SAPbobsCOM.Documents'oDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes)
' wrtiefile(dnDoc.GetAsXML)
dnDoc.SaveXML("C:Jer's FilesMusicDelivery.txt")
'MessageBox.Show("Stop Here")
' oDoc = oCompany.GetBusinessObjectFromXML("C:Jer's FilesMusicDelivery.txt", 0)
'dnDoc = oDoc
lretvel = dnDoc.Add()If lretvel 0 Then
oCompany.GetLastError(errcode, errmsg)
MessageBox.Show(errcode.ToString + " :: " + errmsg)
Else
MessageBox.Show("Success")
End IfCatch ex As Exception
MessageBox.Show(ex.Message)
End TryDB:2.91:Sales Order - Delivery Code... Help! :'( 1a
The solution to this problem incase anyone else runs into it is. Within the Serial Number Object under the Delivery there is a baselineNumber field which needs to be set to the Delivery not the sales order.dnDoc.Lines.SerialNumbers.BaseLineNumber = dnDoc.LineNum
-
RELEVANCY SCORE 2.91
DB:2.91:Dataview.Findrows xz
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;
}
DB:2.91:Dataview.Findrows xz
Доброе утро. Который раз я неогу отправить ответ, аш с пятницы. Вот очередная попытка. Буду краток. Ошибку исправил и вот кирпичик кода, который не нужен был, все работает:
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;
}
Если что не так напишите. Спасибо.
Удачи все. -
RELEVANCY SCORE 2.91
DB:2.91:Trabalhando Com 2 Documentos Do Word No Vba Do Excel 17
Pessoal,
Estou tentando transportar informações recortadas de um arquivo do Word para outro, mas tenho problemas para abrir o segundo.
Já tentei também trabalhar com 2 arquivos abertos, mas deve estar ocorrendo algum erro da minha parte ao declarar os objetos.
Não posso criar uma sub diferente pois tem que trabalhar no mesmo Loop.
Estou utilizando a vinculação inicial para o Microsoft Word.
Peço a ajuda de vocês para entender este problema. Segue o código abaixo:
Sub WordEarlyBinding()
Dim wdApp As Word.Application
Dim wdDoc As Document
Dim linha As Integerlinha = 2
Do While Plan1.Cells(linha, 2).Value
Set wdApp = New Word.Application
wdApp.Visible = 1
Set wdDoc = wdApp.Documents.Open(Plan1.Cells(linha, 2).Value)Plan1.Cells(5, 9).Value = wdDoc.Name
With wdApp
.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.MoveRight Unit:=wdCell
Plan1.Cells(3, 9).NumberFormat = 000
Plan1.Cells(3, 9).Value = Format(.Selection.Text, 000)With .Selection
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
End WithPlan1.Cells(4, 9).Value = .Selection.Text
.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocumentWith .Selection
.HomeKey Unit:=wdStory
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
End WithPlan1.Cells(2, 9).Value = Trim(.Selection.Text)
With .Selection
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.Delete Unit:=wdCharacter, Count:=1
.Text = Plan1.Cells(2, 8).Value
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveDown Unit:=wdLine, Count:=3
.MoveLeft Unit:=wdCell
.MoveLeft Unit:=wdCell
.Text = Plan1.Cells(3, 8).Value
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.Text = Plan1.Cells(3, 8).Value
.MoveDown Unit:=wdLine, Count:=2
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveDown Unit:=wdLine, Count:=2
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.Text = Plan1.Cells(4, 8).Value
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.MoveRight Unit:=wdCell
.Text = Plan1.Cells(4, 8).ValueEnd With
With .Selection.Find
.ClearFormatting
.Text = step 1
.Replacement.Text =
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute
End WithWith .Selection
.EndKey Unit:=wdStory, Extend:=wdExtend
.Cut
.Text = Plan1.Cells(5, 8).Value Plan1.Cells(7, 8).Value Right(wdDoc.Name, Len(wdDoc.Name) - 5)
End With
End WithwdApp.Documents(Plan1.Cells(linha, 2).Value).Save
wdApp.Quit
Set wdApp = Nothing
Set wdDoc = NothingDim wdApp1 As Word.Application
Dim wdDoc1 As Document
Set wdApp1 = New Word.ApplicationSetwdApp1.Visible = 1
wdDoc1 = wdApp1.Documents.Open(Plan1.Cells(8, 6).Value)
With wdApp1With .Selection
.HomeKey Unit:=wdStory
.MoveRight Unit:=wdCell
.Text = Plan1.Cells(3, 9).Value
.MoveDown Unit:=wdLine, Count:=3
.Text = Plan1.Cells(4, 9).Value
.MoveDown Unit:=wdLine, Count:=3
.Text = Plan1.Cells(5, 9).Value
.MoveDown Unit:=wdLine, Count:=3
.Text = Plan1.Cells(2, 9).Value
.MoveDown Unit:=wdLine, Count:=4
.PasteEnd With
End With
MsgBox 'aqui é o ponto de parada para depuração
Set wdApp1 = Nothing
Set wdDoc1 = NothingwdApp1.Quit
linha = linha 1
Loop
End Sub
A primeira parte funciona perfeitamente. Quando ele passa para abrir a nova parte é que trava com a msg Erro em tempo de exec 13 - Tipos incompatíveis.DB:2.91:Trabalhando Com 2 Documentos Do Word No Vba Do Excel 17
Você precisa compreender as noções básicas da criação de objectos, principalmente quando são externos à sua aplicação.
Quando Cria um objecto como o Word com a instrução:
Set wdApp = New Word.Application
wdApp é apenas uma variavel que aponta para a identificação da aplicação, e atraves dela pode obter controlo sobre a aplicação.
Pode por exemplo criar eabrirdocumentos, quantos quizer:
wdApp.Documents.Add
wdApp.Documents.Open FileName
Mas com a instrução:
Set wdApp = Nothing
Você perde o contacto com a aplicação, mas ela continua aberta. Essa instrução nem é necessária, pois assim que a variável for destruida no final do alcance dela, irá acontecer a mesma coisa.A unica forma de fechar a aplicação Word é antes perder o controlo sobre o Objecto Word fazer:
wdApp.Quit
Se por um acaso perder o controlo da aplicação, pode tentar recuperá-lo:
Set wdApp = GetObject(, Word.Application)
Por isso eu sei que irá dar erroa seguinte sequência de instruções:
Set wdApp = New Word.Application
'....
Set wdApp = Nothing
wdApp.Quit ' Runtime Error 91 -
RELEVANCY SCORE 2.91
DB:2.91:Datagridview With Bound And Unbound Calculated Columns Wont Display a1
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
TryDim 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)
Catchr.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)DB:2.91:Datagridview With Bound And Unbound Calculated Columns Wont Display a1
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
LoopTextBox1.Text = LookItUp(SELECT SUM(Chairs) FROM ISDs)
TextBox2.Text = SeatsUsed
TextBox3.Text = Integer.Parse(TextBox1.Text) - Integer.Parse(TextBox2.Text) -
RELEVANCY SCORE 2.90
DB:2.90:Loop Through Rows To Determine Time And Day Difference Two Dates 8m
Hi,
Im trying to find the time and day difference between two dates. My code is as follows:For v = 0 To Sheet2.Columns.Range(AT3).End(xlDown).Row
Sheet2.Cells(3 v, 48) = DateDiff(d, Sheet2.Cells(3 v, 46).Value, Sheet2.Cells(3 v, 47).Value) Days
Sheet2.Cells(3 v, 49) = DateDiff(h, Sheet2.Cells(3 v, 46).Value, Sheet2.Cells(3 v, 47).Value) Hours
Sheet2.Cells(3 v, 50) = DateDiff(n, Sheet2.Cells(3 v, 46).Value, Sheet2.Cells(3 v, 47).Value) MinutesIf Sheet2.Cells(3 v, 46).Text And Sheet2.Cells(3 v, 47).Text Then
Date1 = CDate(Sheet2.Cells(3 v, 46).Text)
Date2 = CDate(Sheet2.Cells(3 v, 47).Text)For Days = Date1 To Date2
DayNames = DayNames Format(Days, dddd)
Sheet2.Cells(3 v, 51) = DayNames
NextEnd If
Next v
The above works out the time difference in Days, Hours Minutes correctly, however when trying to output the Day Names i.e. Monday, Tuesday, Wednesday the For Loop keeps the original value from the previous row and adds it on thus I receive skewed
results.Could someone please help on how to iterate through each loop correctly to output the day names between two dates?
Thanks for the help,
JasDB:2.90:Loop Through Rows To Determine Time And Day Difference Two Dates 8m
Hi,
Ive managed to solve it, I placed a DayNames = after the End If and now it works.
Thanks,
Jas -
RELEVANCY SCORE 2.89
DB:2.89:Creating A Numerical Count Of A Range Of Cells That Achieve A Target Value cc
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 MarkDB:2.89:Creating A Numerical Count Of A Range Of Cells That Achieve A Target Value cc
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 CountIf you want to see data for number 9 only in the row labels, filter the row labels on number 9.
Hope this helps.
-
RELEVANCY SCORE 2.89
DB:2.89:List Of Unique Text Values ff
I'm having trouble getting the formula I need.
I have a list of cost center codes in column H - cells H5 to H47 with a named range of List. This range can have multiples of the same value and does include blank cells. I need a formula to return a unique list into a different range of cells - currently
10 rows as column H would typically not have more than 10 (but the formula should account for more than 10).Currently, the formula's I find return a 0 for the "blank" cells, then in my results range (of 10 rows), I get #N/A for any those rows after I run out of "unique" values. How can I get a unique list of values, keeping the 0 and #N/A results out of my results
range. Is that something that is possible to do?DB:2.89:List Of Unique Text Values ff
Hi,
You may refer to my solution at the following link -
http://www.ashishmathur.com/dynamically-extract-unique-values-with-no-conditions/Hope this helps.
-
RELEVANCY SCORE 2.89
DB:2.89:Question About Datagridview mx
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++;
}
}DB:2.89:Question About Datagridview mx
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. -
RELEVANCY SCORE 2.89
DB:2.89:Count Unique Values Based On Another Column 11
I need to count the uniqueNAMES where the TEXT contains a "2" or "II"
NAMES and TEXT are the columns/named data.This is returning #VALUE
=SUM(IF(FREQUENCY(IF((AM_PRT_PH="*1*"),MATCH(AMEND_PROT,AMEND_PROT,0)),""),ROW(AMEND_PROT)-MIN(ROW(AMEND_PROT))+1),1)Thanks!
DB:2.89:Count Unique Values Based On Another Column 11
Hi,
try this array formula (Ctrl+Shift+Enter)
=SUMPRODUCT(1*(FREQUENCY(IF(ISNUMBER(MATCH($B$4:$B$12,$B$16:$B$17,0)),MATCH($C$4:$C$12,$C$4:$C$12,0)),ROW($C$4:$C$12)-ROW($C$3))0))
Versions are in range B4:B12
Names are in range C4:C12
B16 has 1 and b17 has IThe above formula will work as long as there are no blanks in range B4:B12. It is also possible to take care of that scenario but have not done so just to contain the size of the formula.
Hope this helps.
-
RELEVANCY SCORE 2.87
DB:2.87:Bound Datagridview Sorting Problem 11
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 SubDB:2.87:Bound Datagridview Sorting Problem 11
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. -
RELEVANCY SCORE 2.86
DB:2.86:Three Columns A B C, I Want To Count And Sum For Unique Occurance And Unique Value, How Would I Do That.. dz
A B C
x 2 No
x1 Yes
x2 Yes
x2 Yes
I want to count forA whereB occurs but c is the answer(text)..
so it would be for x 2 occurs 3 times but Yes is only twice.. looking for the return value as 2..DB:2.86:Three Columns A B C, I Want To Count And Sum For Unique Occurance And Unique Value, How Would I Do That.. dz
yes that works, but can this be done with COUNTA functions also?
-
db: Intermittent (yet constant) signal loss
db: Deconfirmation of Scheduled Line stock
db: 9800 Touch screen not working
db: How do I keep my podcast downloads after sync to iPhone with podcast app?
db: CA2000 with try...catch...finally block
db: WAAS solution elements (new stuff for me)
db: Nicht erkennbares Datenbankformat
db: Formating Hard drive
-
RELEVANCY SCORE 2.85
DB:2.85:Read Value Cells With Formulas cz
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
DB:2.85:Read Value Cells With Formulas cz
Cell cell;
cell.CellFormula.CalculateCell.Value = true; -
RELEVANCY SCORE 2.85
DB:2.85:Datagridview Is Very Slow When Using Cellformatting sz
how to solve this issue.
it is very slow
this the code i usePrivate Sub dataGridView2_CellFormatting(ByVal sender As Object, ByVal e As DataGridViewCellFormattingEventArgs) Handles DataGridView2.CellFormatting
For i As Integer = 0 To Me.DataGridView2.Rows.Count - 1
Dim colCount As Integer = DataGridView2.Columns.CountIf Me.DataGridView2.Rows(i).Cells(2).Value Then
If TextBox88.Text Then
If Me.DataGridView2.Rows(i).Cells(3).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(3).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(3).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(3).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(4).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(4).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(4).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(4).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(5).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(5).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(5).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(5).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(6).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(6).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(6).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(6).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(7).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(7).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(7).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(7).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(8).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(8).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(8).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(8).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(9).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(9).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(9).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(9).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(10).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(10).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(10).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(10).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(11).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(11).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(11).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(11).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(12).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(12).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(12).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(12).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(13).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(13).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(13).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(13).Style.BackColor = Color.RedElse
End If
If Me.DataGridView2.Rows(i).Cells(14).Value = (Abs(((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(14).Style.BackColor = Color.LightGreen
ElseIf Me.DataGridView2.Rows(i).Cells(14).Value = Abs((((TextBox88.Text) * (Me.DataGridView2.Rows(i).Cells(2).Value)) / 100) - (Me.DataGridView2.Rows(i).Cells(2).Value)) Then
Me.DataGridView2.Rows(i).Cells(14).Style.BackColor = Color.RedElse
End If
End If
End If
Next
End Sub
thank you
DB:2.85:Datagridview Is Very Slow When Using Cellformatting sz
Cat,
That's difficult to follow (particularly given this new forum style), but let me point out a few things.
First of all, you're using the the CellFormatting event so each time you scroll through the DGV, it fires again. Normally that's fine - that's what you want it to do - but not the way you're doing it. Each time it's going all the way through the entire datatable
which is pointless because you can't see all of it at once.
Havea look at this code snippet:
http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.cellformatting.aspx?cs-save-lang=1cs-lang=vb#code-snippet-2
You can see there a better way to handle it. It only changes the cells that are presently being displayed.
Lastly, when you put together a long chain of If/Then statements, the way you have it it's checking each one which is also needless. Consider using ElseIf so that once it finds the one that fits, it exits out of the If/Then.Please call me Frank :) -
RELEVANCY SCORE 2.84
DB:2.84:Excel Vba Loop Through Column To Detect Duplicate Values In Sequence And Add New Rows Based On Unique Value On Adjacent Column. 99
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 SubHowever, 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 columMy 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.
DB:2.84:Excel Vba Loop Through Column To Detect Duplicate Values In Sequence And Add New Rows Based On Unique Value On Adjacent Column. 99
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 CDim MyRow As Long
Dim RowCount As Long
Dim RouteCount As Long
Dim UniqueRoutes As CollectionMyRow = Firstrow 1
RowCount = 1
Set UniqueRoutes = New CollectionDo 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 SubBTW, UniqueRoutes is a collection of items, not a number, so you cannot set UniqueRoutes to 2.
Regards, Hans Vogelaar
-
RELEVANCY SCORE 2.84
DB:2.84:Value In Cells Without Formulas 9d
When I opened an excel spredsheet that I have been using for the last 8 months all the cells that had text in them suddenly showed #value!. If I type in the cells the text will display. I obviously changed something somewhere but I have no idea where to
begin. ThanksDB:2.84:Value In Cells Without Formulas 9d
When I opened an excel spredsheet that I have been using for the last 8 months all the cells that had text in them suddenly showed #value!. If I type in the cells the text will display. I obviously changed something somewhere but I have no idea where to
begin. Thanks -
RELEVANCY SCORE 2.84
DB:2.84:Hou Can I Count The Words On A Text md
I am using Iterator which I think it only counts unique word so who can I count the all the words on the text
DB:2.84:Hou Can I Count The Words On A Text md
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()); -
RELEVANCY SCORE 2.83
DB:2.83:Help Needed-Counting Merged Cells 8z
HELP?! Does anyone know how to create the following 2 formuals?
1. Count merged cells (comprised of 3 cels) in a column containing both numbers and text
2. Count blank merged cells (comprised of 3 or 2 cells) in a column.DB:2.83:Help Needed-Counting Merged Cells 8z
With a couple of minor modification it worked WONDERFULLY for what I need!!! Thank you soooooo much.
This is such a great site, and everyone here are so helpful!!!
Thank you, thank you, thank you!!! -
RELEVANCY SCORE 2.83
DB:2.83:Unique Record Selection 1j
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
DB:2.83:Unique Record Selection 1j
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;
-
RELEVANCY SCORE 2.83
DB:2.83:Problemas Con Ciclo For 7j
Tengo el siguiente codigo:
Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.TickProgressBar1.Maximum = DataGridView1.Rows.Count - 1
For a As Integer = 0 To DataGridView1.Rows.Count - 1
ProgressBar1.Value = a
If Not DBNull.Value.Equals(DataGridView1.Rows(a).Cells(3).Value) Then
If DataGridView1.Rows(a).Cells(3).Value = FLE-MTTO Or DataGridView1.Rows(a).Cells(3).Value = OFF-MTTO Or DataGridView1.Rows(a).Cells(3).Value = SER-MTTO Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-PROG Or DataGridView1.Rows(a).Cells(3).Value = OFF-PROG Or DataGridView1.Rows(a).Cells(3).Value = SER-PROG Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-MAT Or DataGridView1.Rows(a).Cells(3).Value = OFF-MAT Or DataGridView1.Rows(a).Cells(3).Value = SER-MAT Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-CALIDAD Or DataGridView1.Rows(a).Cells(3).Value = OFF-CALIDAD Or DataGridView1.Rows(a).Cells(3).Value = SER-CALIDAD Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-ALMACEN Or DataGridView1.Rows(a).Cells(3).Value = OFF-ALMACEN Or DataGridView1.Rows(a).Cells(3).Value = SER-ALMACEN Or DataGridView1.Rows(a).Cells(3).Value = SER-RESURTIMIENTO Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-DISENO Or DataGridView1.Rows(a).Cells(3).Value = OFF-DISENO Or DataGridView1.Rows(a).Cells(3).Value = SER-DISENO Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-REVELADO Or DataGridView1.Rows(a).Cells(3).Value = OFF-REVELADO Or DataGridView1.Rows(a).Cells(3).Value = SER-REVELADO Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-ESPECS Or DataGridView1.Rows(a).Cells(3).Value = OFF-ESPECS Or DataGridView1.Rows(a).Cells(3).Value = SER-ESPECS Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-TINTAS Or DataGridView1.Rows(a).Cells(3).Value = OFF-TINTAS Or DataGridView1.Rows(a).Cells(3).Value = SER-TINTAS Or _
DataGridView1.Rows(a).Cells(3).Value = FLE-HERRA Or DataGridView1.Rows(a).Cells(3).Value = OFF-HERRA Or DataGridView1.Rows(a).Cells(3).Value = SER-HERRA Then
DataGridView1.CurrentCell = DataGridView1.Rows(a).Cells(3)
For b As Integer = 0 To dgv.Rows.Count - 1
If Trim(dgv.Rows(b).Cells(0).Value) Trim(DataGridView1.Rows(a).Cells(0).Value) Then
dgv.Rows.Add(DataGridView1.Rows(a).Cells(0).Value)
Exit For
End If
NextEnd If
End If
Nextcorreo()
ProgressBar1.Value = 0
Me.LABOR_TICKETTableAdapter.FillBy(Me.Labor_Ticket._LABOR_TICKET)
Me.NotificadorTableAdapter.Fill(Me.Notificador._notificador)
Label1.Text = Transacciones : DataGridView1.Rows.Count - 1
Label2.Text = Fecha: Date.Now.DateEnd Sub
Es una tabla de datos llena de transacciones de operaciones, cada que haya un paro se tiene que anexar a una tabla nueva. La tabla funciona con un timer que cada determinado tiempo vuelve a buscar si hay mas paros para agregar, pero en caso de que el paro
ya exista no lo debe de agregar.
Por eso el codigo del for que pasa por todas las transacciones ingresadas y cuando vuelva a buscar que cheque si ya estan ingresadas, y si no, que las ingrese. El ciclo lo he modificado mil veces y me sigue agregando otra vez todas las transacciones.
Lo que quiero esq cada vez que corra el codigo, agregue solo los que no estaban agregados.DB:2.83:Problemas Con Ciclo For 7j
Muchisimas gracias, me funciono a la perfeccion
-
RELEVANCY SCORE 2.83
DB:2.83:Form De Pesquisa Em Planilha E Textbox dx
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 SubDB:2.83:Form De Pesquisa Em Planilha E Textbox dx
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 ?
-
RELEVANCY SCORE 2.82
DB:2.82:Exclude Hidden Cells In A Subtotal 103 Counta Not Working 3z
=SUBTOTAL(103,F5:BRL5) is my current formula. I have tried 3 103, but it counts hidden cells either way.
When i add value (text or #'s) to the range, I want it counted. But when I hide it, I want that count removed.DB:2.82:Exclude Hidden Cells In A Subtotal 103 Counta Not Working 3z
Let me try to do better with my description.
In column_A: numbered down, col_B: random numbers, col_C: text names, col-D: sum of range (excluding hidden columns), col_E+ is the range: text numbers example-(off,1/2,5h,...). So if data in enter in range E-BRL, it is counted in col_D. Then if a column
is hidden in the range, it is removed from the count in col_D.
I'm working with your last recommendation to see how I can make it work. -
RELEVANCY SCORE 2.82
DB:2.82:Adicionar Nova Linha Ao Data Grid k3
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?DB:2.82:Adicionar Nova Linha Ao Data Grid k3
Para adicionar uma linha no dataGridView
dataGridView1.Rows.Add(); -
RELEVANCY SCORE 2.82
DB:2.82:Count From Other Excel zx
I just want count the number of cells from another excel without opening the destination excel.
Once if i close and destination an use the formula again count value is not coming..
can anyone help.DB:2.82:Count From Other Excel zx
Your welcome, if that helped please mark the question as answered, thank you
-
RELEVANCY SCORE 2.81
DB:2.81:Array Formula To Count Cells Of Equal Value In Two Rows c7
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
DB:2.81:Array Formula To Count Cells Of Equal Value In Two Rows c7
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.
-
RELEVANCY SCORE 2.81
DB:2.81:Count Cells With Either Of Two Text Values 31
How do you count cells that have one of two specific text values, and filter out the rest. For example, how do you count cells with "rice" or "wheat" in a range that may also contain "soybeans", "barley" and "oats"?
I know how to count one value (all the cells with "rice") but not two.
I've tried using an OR operator with Countif, which doesn't work. Help.
DB:2.81:Count Cells With Either Of Two Text Values 31
Try:=COUNTIF(A1:A10,"rice")+COUNTIF(A1:A10,"wheat")
Alternatively try:=SUM(COUNTIF(A1:A10,"rice"),COUNTIF(A1:A10,"wheat")) -
RELEVANCY SCORE 2.81
DB:2.81:El Ndice Estaba Fuera Del Intervalo. Debe Ser Un Valor No Negativo E Inferior Al Tamao De La Coleccin fz
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.RowsFor 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).Textcmd.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).Textcmd.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.DB:2.81:El Ndice Estaba Fuera Del Intervalo. Debe Ser Un Valor No Negativo E Inferior Al Tamao De La Coleccin fz
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 -
RELEVANCY SCORE 2.80
DB:2.80:Getting A Grid Row Value In To A Variable pc
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 thisDB:2.80:Getting A Grid Row Value In To A Variable pc
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 -
RELEVANCY SCORE 2.80
DB:2.80:Counting The Quantity Of Unique Text Values When Some Cells May Be Blank km
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?DB:2.80:Counting The Quantity Of Unique Text Values When Some Cells May Be Blank km
I got a total number of entries.
not a count of unique entries. -
RELEVANCY SCORE 2.79
DB:2.79:Count Unique Value From A Field In Source Schemas In A Map kk
Hi
How can icount unique value from a field in source schemas in a map?
I have a source schema in map, and i have multiple records, and i have to count unique values from a filed in this source schemas.
regards
DanielDB:2.79:Count Unique Value From A Field In Source Schemas In A Map kk
Daniel,
There is only one Detail element in each Detaljer element so I think you want to count the unique ECode in Details so you need not to use the foreach loop as this line will count all the unique ECode under element Detail.
xsl:variable name=demo select=count(//Detail[not(ECode=preceding-sibling::Detail/ECode)]/ECode) /Please mark the post answered your question as answer, and mark other helpful posts as helpful, it'll help other users who are visiting your thread for the similar problem, Regards -Rohit Sharma (http://rohitt-sharma.blogspot.com/) -
RELEVANCY SCORE 2.79
DB:2.79:Counting Numnber Of Cells In A Table That Have The Same Value a8
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?
DB:2.79:Counting Numnber Of Cells In A Table That Have The Same Value a8
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. -
RELEVANCY SCORE 2.79
DB:2.79:Count All Cells That Contain Text 79
I have a spreadsheet that has a large number of entries. It is a catalog of where I have stored wine in wine racks. Some of the wine slots are empty. I would like to count the number of bottles of wine that I have. How can I count only cells that contain
text (like the name of the wine or winery) and omit those that are blank?DB:2.79:Count All Cells That Contain Text 79
=COUNTA(A1:A100) will count cells with text and numbers but will exclude blank cells.
To exclude numbers =COUNTA(range) - COUNT(range)
Note that a formula like =IF(A10=3,"OK","") may look blank but is not and will be included in COUNTA
best wishes -
RELEVANCY SCORE 2.78
DB:2.78:Count Function For Text Not Just Numerical Data kf
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
DB:2.78:Count Function For Text Not Just Numerical Data kf
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 -
RELEVANCY SCORE 2.78
DB:2.78:Hashset cz
i have
HashSet unique = new HashSet(arrayList);
int numberOfUnique = uniqueWords.size();to count an array unique's cells
but i donno how to use the cells it separate out. can anyone show me ?
i don't think hasSet has the get or [num] method.DB:2.78:Hashset cz
In this way a HashSet can be used as a transitional data structure to filter out doubles from an ArrayList,
arrayList = new ArrayList(new HashSet(arrayList)); -
RELEVANCY SCORE 2.78
DB:2.78:Re: Tired Search Queries kp
SQL select count(unique(cv_id))
from detailResume
where cat_id = 5
and regexp_like(lower(detailResume), Replace('Magda Gamal', ' ', '|')); 2 3 4COUNT(UNIQUE(CV_ID))
--------------------
0SQL set timing on
SQL /COUNT(UNIQUE(CV_ID))
--------------------
0Elapsed: 00:01:16.15
SQL select count(*)
from detailResume
where cat_id = 5
and lower(detailResume) like '%Magda Gamal%'; 2 3 4COUNT(*)
----------
0Elapsed: 00:00:10.12
SQLSQL select count(unique(cv_id))
from detailResume
where cat_id = 5
and lower(detailResume) like '%Magda Gamal%'; 2 3 4COUNT(UNIQUE(CV_ID))
--------------------
0Elapsed: 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.41Why there is too much difference?
Best regards
DB:2.78:Re: Tired Search Queries kp
Please observe below :
SQL select count(*)
from detailResume
where cat_id = 5
and lower(detailResume) like '%Magda Gamal%'; 2 3 4COUNT(*)
----------
0Elapsed: 00:00:10.12
SQL this will never be match : lower(detailResume) like '%Magda Gamal%';Cheers,
Manik. -
RELEVANCY SCORE 2.77
DB:2.77:How To Return Unique Value From Xquery For By Searching List Of Values km
Hi
My response is ProductList and ItemList. Based on the passed in Item/Type the xquery should check the following conditions are return unique value
If passed in value exists then
return "exists"
else if (count(item)5) then
return "maxLimit"
else
"notExist"ProductIdentificationResponse
!-- LIST OF PRODUCTS --
ProductList
Product
!-- LIST OF ITEMS --
ItemList
Item
TypeITM22/Type
Value5555/Value
/ItemItem
TypeITM22/Type
Value6666/Value
/ItemItem
TypeITM22/Type
Value7777/Value
/Item/ItemList
/Product
/ProductList
/ProductIdentificationResponseI have return the xquery as follows but not working
for $x in $ProductIdentificationResponse1/ProductList/Product/ItemList/Item
return
if ($passedParam/text() = $x/Value/text()) then
( "Exists" )
else if (count( $ProductIdentificationResponse1/ProductList/Product/ItemList/Item) 5) then
("maxLimit")
else
("notExist")
};Any help will be appreciated
Thank you
Edited by: user11310683 on Sep 2, 2011 4:25 AM
DB:2.77:How To Return Unique Value From Xquery For By Searching List Of Values km
Thank you.... As you said its namespace issue
-
RELEVANCY SCORE 2.77
DB:2.77:Getting A Gridview Row Value Into A String xz
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: indexwhat should i do , inorder to solve this
DB:2.77:Getting A Gridview Row Value Into A String xz
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 -
RELEVANCY SCORE 2.76
DB:2.76:Excel Countif Value Not Displayed 19
I am using COUNTIF to determine the number of cells that contain a string of text characters.
The string I am searching for is: "* UNKNOWN * Seg 9: 0 feet from [South-West Corner (Pole#29)]"
COUNTIF returns zero, when I count 12 manually.
I have tried using "~" before the "*".
If I search for "* UNKNOWN *" the Function Arguments window shows the Formula Result as 204, (204 may be a correct value) but that value displays as zero after I click OK. I am looking to count a more specific text string.If I search for "* UNKNOWN * Seg 9: 0" the Function Arguments window shows 0.
How do I successfully count the number of times a strings of text characters show in a spreadsheet?
DB:2.76:Excel Countif Value Not Displayed 19
Hi,
Counting the visible rows in a filtered range is straightforward and we can use the formula below.
In the formula column C is the column that contains the text string we're looking for so change that to suit keeping all references to it the same number of rows..
Note we don't use the wildcard characters (*) in this formula , we find the substring by using SEARCH
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C15,ROW(C2:C15)-MIN(ROW(C2:C15)),,1))*(ISNUMBER(SEARCH("the greatUNKNOWN * Seg 9: 0 feet from [South-West Corner (Pole#29)]",C2:C15))))
-
RELEVANCY SCORE 2.76
DB:2.76:How To Make Macro Run Faster mk
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 suggestionsSub 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 SubDB:2.76:How To Make Macro Run Faster mk
Thanks, to everyone very much for suggestions :
-
RELEVANCY SCORE 2.76
DB:2.76:Vba Excel - Count Rows With Unique Value mf
Hi all,
DB:2.76:Vba Excel - Count Rows With Unique Value mf
what if I have dynamic range? e.g. not just a1:a5 but sometimes I need for range a1:a13 or a1:a1909 etc. Thanks.
solved.
-
RELEVANCY SCORE 2.76
DB:2.76:Count Total Text Cells But Not Include The Grouped Rows s9
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: LemonTotal = 4
Make sense?! Thank you!
DB:2.76:Count Total Text Cells But Not Include The Grouped Rows s9
Hi,
Try this, 103 emulates COUNTA and ignores hidden rows. Look in Excel help for more SUBTOTAL options.
=SUBTOTAL(103,A1:A6)
-
RELEVANCY SCORE 2.76
DB:2.76:Risposta Al Thread Spiegazioni Dovute E Relativi Thread Associali 7f
Vorrei intervenire nella discussione generale riferita al thread Spiegazioni dovute e relativi thread per comunicare e presentare la soluzione da me trovata utilizzando le informazioni ricevute nei vari thread che mi sono servite sia come momento di riflessione
che come strada trovata e che riporto a seguire.
Nello screenshot viene presenta la videata contenente due datagrid, la prima è quella che si ottiena in risposta alla query, mentre la seconda è quella ottenuta dopo aver manipolato i dati con il codice presentato successiamente allo screenshot.Come si può vedere coincidono ed è la stessa che si ottiene con il codice attualmente commentato nell'allegato.
Public Class VendutoPP
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Dim NRec As Boolean = True
Dim QryxPP As String =
Vincoli(0) = CDate(DateTimePicker1.Value)
Vincoli(1) = CDate(DateTimePicker2.Value)
Dim Mylist As List(Of ClassePerPeriodo) = New List(Of ClassePerPeriodo)
QryxPP = Select Codice As CodForn, descrizione As Descrizione, ordinato As Ordinati, prnacq As PrzNetto, Count(Vendetta.keydett) As Venduto,
QryxPP = QryxPP Sum(PrezzoVendita*.79)/Count(Vendetta.keydett) As PmedVend, Sum(PrezzoVendita*.79)-prnacq As MargOp, Sum(PrezzoVendita*.79) As IncassoNetto,
QryxPP = QryxPP Sum(PrezzoVendita*.79)/Count(Vendetta.keydett)/Prnacq*100 As RicMed, ordinato-Count(Vendetta.keydett) As Giacenza
QryxPP = QryxPP From Articoli, Dettaglio , Vendetta, Vendita
QryxPP = QryxPP Where Vendetta.keydett = Dettaglio.keydet And Dettaglio.keyart = Articoli.keyart And (data Between @P0 And @P1) And Vendita.IdVen = Vendetta.IdVen
QryxPP = QryxPP Group By Articoli.Codice, Articoli.Descrizione, Ordinato, prnacq
RowSel = True
VendutoNelPeriodo.DataSource = QryConParam(QryxPP, Vincoli, RowSel)
If Not RowSel Then Exit Sub
'Nuova Versione
'Prelievo le intestazioni di colonna
Dim Intestaz(1000)
For jj As Integer = 0 To VendutoNelPeriodo.Columns.Count - 1
Dim ss As String = VendutoNelPeriodo.Columns(jj).Name
Intestaz(jj) = ss
Next
'Creo il vettore del contenuto
Dim ListaPippo As List(Of ClasseTry) = New List(Of ClasseTry)
For jj As Integer = 0 To VendutoNelPeriodo.Rows.Count - 2
For kk As Integer = 0 To VendutoNelPeriodo.Columns.Count - 1
ListaPippo.Add(New ClasseTry With {.Cella = VendutoNelPeriodo.Rows(jj).Cells(kk).Value})
Next
Next
Dim NumCols As Integer = VendutoNelPeriodo.Columns.Count
Dim NumRows As Integer = ListaPippo.Count / NumCols
'Aggiungo una DataGrid
Dim rr As Integer = DataGridView1.Rows.Count
Dim NuovaTabella As New DataTable
'Trasferisco le intestazioni e.......
DataGridView1.ColumnCount = NumCols
For jj As Integer = 0 To NumCols - 1
DataGridView1.Columns(jj).Name = Intestaz(jj)
Next
'Dim NewRow As String() = New String() {Pippo}
'....e i contenuti
Dim righe As Integer = DataGridView1.Rows.Count
Dim colon As Integer = DataGridView1.Columns.Count
For jj As Integer = 0 To NumRows - 1
Dim Pippo(NumRows)
For kk As Integer = 0 To NumCols - 1
Dim ii As Integer = (jj) * NumCols kk
Pippo(kk) = ListaPippo(ii).Cella
'---Manipolo il formalismo
If kk 4 And kk 9 Then
Pippo(kk) = Int(100 * Pippo(kk)) / 100
End If
Next
DataGridView1.Rows.Add(Pippo)
Next
ResizeGrid(DataGridView1, 1, 10, 10)
'---Completo il Report
Dim TotPz As Integer = 0
Dim ScrAcq As Integer = 0
Dim InCaToNe As Integer = 0
Dim RiMeNe As Integer = 0
For jj As Integer = 0 To DataGridView1.RowCount - 1
TotPz = DataGridView1.Rows(jj).Cells(4).Value 'Totale Pezzi Venduti
Label8.Text = TotPz
ScrAcq = DataGridView1.Rows(jj).Cells(3).Value 'Costo di Acquisto del Venduto (Scarico Acquisto)
Label9.Text = ScrAcq
InCaToNe = DataGridView1.Rows(jj).Cells(7).Value 'IncassoTotaleNetto
Label10.Text = InCaToNe
RiMeNe = Int(100 * (InCaToNe - ScrAcq) / ScrAcq) 'RIcaricoMEdioNetto
Label11.Text = RiMeNe
Label15.Text = InCaToNe - ScrAcq 'Incasso Lordo nel Periodo
Next
Label13.Text = DataGridView1.RowCount
'Fine Nuova Versione
''-----------------------------
'For index As Integer = 0 To VendutoNelPeriodo.Rows.Count - 2
' Mylist.Add(New ClassePerPeriodo With { _
' .CodForn = VendutoNelPeriodo.Rows(index).Cells(0).Value, _
' .Descrizione = VendutoNelPeriodo.Rows(index).Cells(1).Value, _
' .Ordinato = VendutoNelPeriodo.Rows(index).Cells(2).Value, _
' .PrzNetto = VendutoNelPeriodo.Rows(index).Cells(3).Value, _
' .Venduto = VendutoNelPeriodo.Rows(index).Cells(4).Value, _
' .PmedVend = Int(100 * VendutoNelPeriodo.Rows(index).Cells(5).Value) / 100, _
' .MargOp = Int(100 * VendutoNelPeriodo.Rows(index).Cells(6).Value) / 100, _
' .IncassoNetto = Int(100 * VendutoNelPeriodo.Rows(index).Cells(7).Value) / 100, _
' .RicMed = Int(100 * VendutoNelPeriodo.Rows(index).Cells(8).Value) / 100, _
' .Giacenza = VendutoNelPeriodo.Rows(index).Cells(9).Value})
'Next
''-----------------------------
'VendutoNelPeriodo.DataSource = Mylist
'VendutoNelPeriodo.DataSource = ListaPippo
'ResizeGrid(VendutoNelPeriodo, 1, 20, 10)
'Label13.Text = VendutoNelPeriodo.RowCount
'Dim TotPz As Integer = 0
'Dim ScrAcq As Integer = 0
'Dim InCaToNe As Integer = 0
'Dim RiMeNe As Integer = 0
'For jj As Integer = 0 To VendutoNelPeriodo.RowCount - 1
' TotPz = VendutoNelPeriodo.Rows(jj).Cells(4).Value 'Totale Pezzi Venduti
' Label8.Text = TotPz
' ScrAcq = VendutoNelPeriodo.Rows(jj).Cells(3).Value 'Costo di Acquisto del Venduto (Scarico Acquisto)
' Label9.Text = ScrAcq
' InCaToNe = VendutoNelPeriodo.Rows(jj).Cells(7).Value 'IncassoTotaleNetto
' Label10.Text = InCaToNe
' RiMeNe = Int(100 * (InCaToNe - ScrAcq) / ScrAcq) 'RIcaricoMEdioNetto
' Label11.Text = RiMeNe
' Label15.Text = InCaToNe - ScrAcq 'Incasso Lordo nel Periodo
'Next
Panel1.Visible = True
End Sub
Private Sub DateTimePicker1_TextChanged(sender As Object, e As System.EventArgs) Handles DateTimePicker1.TextChanged
Label2.Visible = True
DateTimePicker2.Visible = True
Button2.Visible = True
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Label1.Visible = False
DateTimePicker1.Visible = True
End Sub
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
Private Sub Button4_Click_1(sender As System.Object, e As System.EventArgs) Handles Button4.Click
VendutoNelPeriodo.Visible = True
End Sub
Private Sub Button2_ContextMenuStripChanged(sender As Object, e As System.EventArgs) Handles Button2.ContextMenuStripChanged
End Sub
End Class
Questa soluzione mi permette di eliminare (attualmente) sette classi specializzate per query diverse, il guadagno, credo, non è banale in tutti i sensi.
Cercando di affinare ulteriormente ho tentato di fare l'operazione sulla stessa datagrid della griglia, ma non mi è stato permesso dal sistema. L'operazione tentata consisteva in un Remove a un Add sulla stessa griglia, vorrei provare con una sostituzione
o una riscrittura della riga ma non sono riuscito atrovare un modo.
Spero con questo di essermi chiarito, ma non mi dispiacerebbe sapere se, vista la vostra esperienza e conoscenza del sistema e della sua architettura, se questa mia proposta di soluzione è migliorabile. In particolare non mi dispiacerebba sapere se esiste
una soluzione alla domanda sopra posta.
Grazie matyldaDB:2.76:Risposta Al Thread Spiegazioni Dovute E Relativi Thread Associali 7f
Vorrei intervenire nella discussione generale riferita al thread Spiegazioni dovute e relativi thread per comunicare e presentare la soluzione da me trovata utilizzando le informazioni ricevute nei vari thread che mi sono servite sia come momento di riflessione
che come strada trovata e che riporto a seguire.
Nello screenshot viene presenta la videata contenente due datagrid, la prima è quella che si ottiena in risposta alla query, mentre la seconda è quella ottenuta dopo aver manipolato i dati con il codice presentato successiamente allo screenshot.Come si può vedere coincidono ed è la stessa che si ottiene con il codice attualmente commentato nell'allegato.
Public Class VendutoPP
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Dim NRec As Boolean = True
Dim QryxPP As String =
Vincoli(0) = CDate(DateTimePicker1.Value)
Vincoli(1) = CDate(DateTimePicker2.Value)
Dim Mylist As List(Of ClassePerPeriodo) = New List(Of ClassePerPeriodo)
QryxPP = Select Codice As CodForn, descrizione As Descrizione, ordinato As Ordinati, prnacq As PrzNetto, Count(Vendetta.keydett) As Venduto,
QryxPP = QryxPP Sum(PrezzoVendita*.79)/Count(Vendetta.keydett) As PmedVend, Sum(PrezzoVendita*.79)-prnacq As MargOp, Sum(PrezzoVendita*.79) As IncassoNetto,
QryxPP = QryxPP Sum(PrezzoVendita*.79)/Count(Vendetta.keydett)/Prnacq*100 As RicMed, ordinato-Count(Vendetta.keydett) As Giacenza
QryxPP = QryxPP From Articoli, Dettaglio , Vendetta, Vendita
QryxPP = QryxPP Where Vendetta.keydett = Dettaglio.keydet And Dettaglio.keyart = Articoli.keyart And (data Between @P0 And @P1) And Vendita.IdVen = Vendetta.IdVen
QryxPP = QryxPP Group By Articoli.Codice, Articoli.Descrizione, Ordinato, prnacq
RowSel = True
VendutoNelPeriodo.DataSource = QryConParam(QryxPP, Vincoli, RowSel)
If Not RowSel Then Exit Sub
'Nuova Versione
'Prelievo le intestazioni di colonna
Dim Intestaz(1000)
For jj As Integer = 0 To VendutoNelPeriodo.Columns.Count - 1
Dim ss As String = VendutoNelPeriodo.Columns(jj).Name
Intestaz(jj) = ss
Next
'Creo il vettore del contenuto
Dim ListaPippo As List(Of ClasseTry) = New List(Of ClasseTry)
For jj As Integer = 0 To VendutoNelPeriodo.Rows.Count - 2
For kk As Integer = 0 To VendutoNelPeriodo.Columns.Count - 1
ListaPippo.Add(New ClasseTry With {.Cella = VendutoNelPeriodo.Rows(jj).Cells(kk).Value})
Next
Next
Dim NumCols As Integer = VendutoNelPeriodo.Columns.Count
Dim NumRows As Integer = ListaPippo.Count / NumCols
'Aggiungo una DataGrid
Dim rr As Integer = DataGridView1.Rows.Count
Dim NuovaTabella As New DataTable
'Trasferisco le intestazioni e.......
DataGridView1.ColumnCount = NumCols
For jj As Integer = 0 To NumCols - 1
DataGridView1.Columns(jj).Name = Intestaz(jj)
Next
'Dim NewRow As String() = New String() {Pippo}
'....e i contenuti
Dim righe As Integer = DataGridView1.Rows.Count
Dim colon As Integer = DataGridView1.Columns.Count
For jj As Integer = 0 To NumRows - 1
Dim Pippo(NumRows)
For kk As Integer = 0 To NumCols - 1
Dim ii As Integer = (jj) * NumCols kk
Pippo(kk) = ListaPippo(ii).Cella
'---Manipolo il formalismo
If kk 4 And kk 9 Then
Pippo(kk) = Int(100 * Pippo(kk)) / 100
End If
Next
DataGridView1.Rows.Add(Pippo)
Next
ResizeGrid(DataGridView1, 1, 10, 10)
'---Completo il Report
Dim TotPz As Integer = 0
Dim ScrAcq As Integer = 0
Dim InCaToNe As Integer = 0
Dim RiMeNe As Integer = 0
For jj As Integer = 0 To DataGridView1.RowCount - 1
TotPz = DataGridView1.Rows(jj).Cells(4).Value 'Totale Pezzi Venduti
Label8.Text = TotPz
ScrAcq = DataGridView1.Rows(jj).Cells(3).Value 'Costo di Acquisto del Venduto (Scarico Acquisto)
Label9.Text = ScrAcq
InCaToNe = DataGridView1.Rows(jj).Cells(7).Value 'IncassoTotaleNetto
Label10.Text = InCaToNe
RiMeNe = Int(100 * (InCaToNe - ScrAcq) / ScrAcq) 'RIcaricoMEdioNetto
Label11.Text = RiMeNe
Label15.Text = InCaToNe - ScrAcq 'Incasso Lordo nel Periodo
Next
Label13.Text = DataGridView1.RowCount
'Fine Nuova Versione
''-----------------------------
'For index As Integer = 0 To VendutoNelPeriodo.Rows.Count - 2
' Mylist.Add(New ClassePerPeriodo With { _
' .CodForn = VendutoNelPeriodo.Rows(index).Cells(0).Value, _
' .Descrizione = VendutoNelPeriodo.Rows(index).Cells(1).Value, _
' .Ordinato = VendutoNelPeriodo.Rows(index).Cells(2).Value, _
' .PrzNetto = VendutoNelPeriodo.Rows(index).Cells(3).Value, _
' .Venduto = VendutoNelPeriodo.Rows(index).Cells(4).Value, _
' .PmedVend = Int(100 * VendutoNelPeriodo.Rows(index).Cells(5).Value) / 100, _
' .MargOp = Int(100 * VendutoNelPeriodo.Rows(index).Cells(6).Value) / 100, _
' .IncassoNetto = Int(100 * VendutoNelPeriodo.Rows(index).Cells(7).Value) / 100, _
' .RicMed = Int(100 * VendutoNelPeriodo.Rows(index).Cells(8).Value) / 100, _
' .Giacenza = VendutoNelPeriodo.Rows(index).Cells(9).Value})
'Next
''-----------------------------
'VendutoNelPeriodo.DataSource = Mylist
'VendutoNelPeriodo.DataSource = ListaPippo
'ResizeGrid(VendutoNelPeriodo, 1, 20, 10)
'Label13.Text = VendutoNelPeriodo.RowCount
'Dim TotPz As Integer = 0
'Dim ScrAcq As Integer = 0
'Dim InCaToNe As Integer = 0
'Dim RiMeNe As Integer = 0
'For jj As Integer = 0 To VendutoNelPeriodo.RowCount - 1
' TotPz = VendutoNelPeriodo.Rows(jj).Cells(4).Value 'Totale Pezzi Venduti
' Label8.Text = TotPz
' ScrAcq = VendutoNelPeriodo.Rows(jj).Cells(3).Value 'Costo di Acquisto del Venduto (Scarico Acquisto)
' Label9.Text = ScrAcq
' InCaToNe = VendutoNelPeriodo.Rows(jj).Cells(7).Value 'IncassoTotaleNetto
' Label10.Text = InCaToNe
' RiMeNe = Int(100 * (InCaToNe - ScrAcq) / ScrAcq) 'RIcaricoMEdioNetto
' Label11.Text = RiMeNe
' Label15.Text = InCaToNe - ScrAcq 'Incasso Lordo nel Periodo
'Next
Panel1.Visible = True
End Sub
Private Sub DateTimePicker1_TextChanged(sender As Object, e As System.EventArgs) Handles DateTimePicker1.TextChanged
Label2.Visible = True
DateTimePicker2.Visible = True
Button2.Visible = True
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Label1.Visible = False
DateTimePicker1.Visible = True
End Sub
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
Private Sub Button4_Click_1(sender As System.Object, e As System.EventArgs) Handles Button4.Click
VendutoNelPeriodo.Visible = True
End Sub
Private Sub Button2_ContextMenuStripChanged(sender As Object, e As System.EventArgs) Handles Button2.ContextMenuStripChanged
End Sub
End Class
Questa soluzione mi permette di eliminare (attualmente) sette classi specializzate per query diverse, il guadagno, credo, non è banale in tutti i sensi.
Cercando di affinare ulteriormente ho tentato di fare l'operazione sulla stessa datagrid della griglia, ma non mi è stato permesso dal sistema. L'operazione tentata consisteva in un Remove a un Add sulla stessa griglia, vorrei provare con una sostituzione
o una riscrittura della riga ma non sono riuscito atrovare un modo.
Spero con questo di essermi chiarito, ma non mi dispiacerebbe sapere se, vista la vostra esperienza e conoscenza del sistema e della sua architettura, se questa mia proposta di soluzione è migliorabile. In particolare non mi dispiacerebba sapere se esiste
una soluzione alla domanda sopra posta.
Grazie matylda -
RELEVANCY SCORE 2.75
DB:2.75:Count Unique Values Only x9
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.DB:2.75:Count Unique Values Only x9
http://office.microsoft.com/en-us/excel-help/count-unique-values-among-duplicates-HP010070481.aspx
-
RELEVANCY SCORE 2.75
DB:2.75:Need To Improve The Performance Of A Loop- Vb.Net 1c
Hello All,PFB the code for a search button where when a user enters a text in the text box ,it takes the keyword compares it with an xls which has 1600 rows and 16 columns with keywords.
If the keywords match, it has to full the 3rd column where the name is specified.
Its running but its taking more than 20-25 mins for processing.
I knw searching 1600 rows and 16 columns will take time but pls advice if i can make this process
a lil faster.
Pls Advice
AbiPrivate
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
IntegerDim vtemp
As
String
vtemp =Dim iRowIndex, iColumnIndex, iSearchText
As
IntegerDim 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 sheet1.xls)'xlBook.Application.Visible = False
'xlBook.Windows(1).Visible = False
xlBook.Application.Windowstate = Excel.XlWindowState.xlMinimized' Dim a, b As String
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 1717
ProgressBar1.Value = (iRowIndex / 1717) * 100For iColumnIndex = 6
To 16For iSearchText = 1
To 30'a = xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value
'b = avFoundKeywordds(iSearchText)
If ((CStr(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 (a = b) Then
If (vtemp (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value))
ThenIf ((xlBook.Worksheets(2).Cells(iRowIndex, 16).Value) =
Simple)
Then
vtemp = (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)'MsgBox(vtemp)
TextBox1.Text = vtemp'Storing the Simple Scripts
' txtDisplay.Text = txtDisplay.Text xlBook.Worksheets(2).Cells(iRowIndex, 3) Simple
TextBox2.Text = TextBox2.Text TextBox1.Text
--Priority-Simple vbNewLine'Increment the Simple Scripts Count
vSimpleCount = vSimpleCount 1End
IfIf ((xlBook.Worksheets(1).Cells(iRowIndex, 16).Value) =
Medium)
Then
vtemp = (xlBook.Worksheets(1).Cells(iRowIndex, 3).Value)
TextBox1.Text = vtemp'Storing the Medium Scripts
' txtDisplay.Text = txtDisplay.Text xlBook.Worksheets(2).Cells(iRowIndex, 3) Medium
TextBox2.Text = TextBox2.Text TextBox1.Text
--Priority-Medium vbNewLine'Increment the Medium Scripts Count
vMediumCount = vMediumCount 1End
IfIf ((xlBook.Worksheets(1).Cells(iRowIndex, 16).Value) =
Complex)
Then
vtemp = (xlBook.Worksheets(1).Cells(iRowIndex, 3).Value)
TextBox1.Text = vtemp'Storing the Complex Scripts
' txtDisplay.Text = txtDisplay.Text xlBook.Worksheets(2).Cells(iRowIndex, 3) High
TextBox2.Text = TextBox2.Text TextBox1.Text
--Priority-Complex vbNewLine'Increment the Complex Scripts Count
vComplexCount = vComplexCount 1End
IfEnd
IfEnd
IfEnd
IfNext 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 =
FalseMe.Hide()
End
Sub
EndClass
DB:2.75:Need To Improve The Performance Of A Loop- Vb.Net 1c
Hi Abi
As you do not appear to be using the VSTO technology - this looks like a WinForms app? - it might be a good idea to move your question to the Excel for Developers forum where you'll find a lot more application specialists who might have some ideas how to
improve your code.
It will help everyone to know which version of Excel you're targeting. Especially since you appear to not be interacting with the user in the Excel interface, but in your WinForm(?). It doesn't appear that these are Office Open XML files, which
would mean you could access them much more efficiently, but...
Please let me know if you want me to move your message to the specialized Excel forum.Cindy Meister, VSTO/Word MVP -
RELEVANCY SCORE 2.74
DB:2.74:Using Numbers To Count Text Or Words In A Row Or Column mm
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
-
RELEVANCY SCORE 2.74
DB:2.74:Finding Max Value With Text Containing Numbers kc
I have a row of cells containing text with numbers e.g. Run 1, Run 2, Run 3,........Run 20. I am trying to calculate the max run number for a specific range of the cells. I have tried using VALUE to convert the cells to numbers, but I keep getting #VALUE
returned. Please help.DB:2.74:Finding Max Value With Text Containing Numbers kc
Glad that this helped you. Request you to mark relevant response as Answer so that thread can be closed and others can also benefit.
-
RELEVANCY SCORE 2.74
DB:2.74:Save Data From Unbound Datagridview To Database Table. 19
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.
DB:2.74:Save Data From Unbound Datagridview To Database Table. 19
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 Int32Sub 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 SubSub 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 ClassKnowledge is the only thing that I can give you, and still retain, and we are both better off for it.
-
RELEVANCY SCORE 2.74
DB:2.74:Count Coloredshaded Cells xm
I am using Excel 2003. I need to be able to count the number of cells that do not have any value, text whatsoever in them but are shaded green. I need to count how many cells out of a certain range are shaded green. How can I accomplish this? I cannot
seem to find a COUNT function that will do this.DB:2.74:Count Coloredshaded Cells xm
But how do I get it to recalculate automatically? Sorry, I should have been more specific.Hi,
That's difficult, you would need to force recalculation by capturing an event.
To change the colour of a cell you have to select it and we have the Worksheet_SelectionChange event but that's no good because you change it after you've selected it. In any case if we used that event the worksheet would be IMO calculating far to often.
We could use the double_click event but that; once again IMO, is no easier than tapping F9.
I wouldn't do this, I'd use F9 but if you must then right click your sheet tab, view code and paste this in on the right. You will need to remember to select another cell after changing the colour to force calculation.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub -
RELEVANCY SCORE 2.74
DB:2.74:Autofilter Search Stops Working When Empty Cells Are After 10000th Unique Value x7
a simple table to demonstrate the behavior:
DB:2.74:Autofilter Search Stops Working When Empty Cells Are After 10000th Unique Value x7
Hi,
Can you upload the file to SkyDrive and share the link of the uploaded file here.
-
RELEVANCY SCORE 2.73
DB:2.73:Word Properties d9
Bu Kodlarla DataGridview'de Word Dosyasının Property Gösterebiliyorum
Peki dataGridView1'den Örneğin Yazarını Değiştirdim Diyelim Nasıl Kayıtlı Kalmasını Sağlayabilirim Bu Değişikliğin
private FileInfo SelectedFileInfo;
private void listView1_SelectedIndexChanged_1(object sender, EventArgs e)
{
if (listViewFileInfoList.SelectedItems.Count 0)
{
string FileName = listViewFileInfoList.SelectedItems[0].SubItems[0].Text;
string Path = listViewFileInfoList.SelectedItems[0].SubItems[1].Text;
string LastWriteTime = listViewFileInfoList.SelectedItems[0].SubItems[2].Text;
string Extension = listViewFileInfoList.SelectedItems[0].SubItems[6].Text;
string IsReadOnly = listViewFileInfoList.SelectedItems[0].SubItems[4].Text;
string CreationTime = listViewFileInfoList.SelectedItems[0].SubItems[5].Text;SelectedFileInfo = new FileInfo(Path \\ FileName);
if (SelectedFileInfo.Extension == .doc || SelectedFileInfo.Extension == .docx)
{DSOFile.OleDocumentProperties doc = new DSOFile.OleDocumentProperties();
doc.Open(SelectedFileInfo.FullName, false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);
dataGridView1.Rows.Clear();
DataGridViewRow row1 = new DataGridViewRow();
row1.CreateCells(dataGridView1);
row1.Cells[0].Value = Konu;
row1.Cells[1].Value = doc.SummaryProperties.Subject;
dataGridView1.Rows.Add(row1);
DataGridViewRow row2 = new DataGridViewRow();
row2.CreateCells(dataGridView1);
row2.Cells[0].Value = Kategorisi;
row2.Cells[1].Value = doc.SummaryProperties.Category;
dataGridView1.Rows.Add(row2);
DataGridViewRow row3 = new DataGridViewRow();
row3.CreateCells(dataGridView1);
row3.Cells[0].Value = Yazar;
row3.Cells[1].Value = doc.SummaryProperties.Author;
dataGridView1.Rows.Add(row3);
DataGridViewRow row4 = new DataGridViewRow();
row4.CreateCells(dataGridView1);
row4.Cells[0].Value = Son Kaydeden;
row4.Cells[1].Value = doc.SummaryProperties.LastSavedBy;
dataGridView1.Rows.Add(row4);
dataGridView1.Update();
DataGridViewRow row5 = new DataGridViewRow();
row5.CreateCells(dataGridView1);
row5.Cells[0].Value = Yorumlar;
row5.Cells[1].Value = doc.SummaryProperties.Comments ;
dataGridView1.Rows.Add(row5);
DataGridViewRow row6 = new DataGridViewRow();
row6.CreateCells(dataGridView1);
row6.Cells[0].Value = Revizyon Numarası;
row6.Cells[1].Value = doc.SummaryProperties.RevisionNumber;
dataGridView1.Rows.Add(row6);
DataGridViewRow row7 = new DataGridViewRow();
row7.CreateCells(dataGridView1);
row7.Cells[0].Value = Şirket;
row7.Cells[1].Value = doc.SummaryProperties.Company;
dataGridView1.Rows.Add(row7);
DataGridViewRow row8 = new DataGridViewRow();
row8.CreateCells(dataGridView1);
row8.Cells[0].Value = Son Kaydedilme Tarihi;
row8.Cells[1].Value = doc.SummaryProperties.DateLastSaved;
dataGridView1.Rows.Add(row8);
DataGridViewRow row9 = new DataGridViewRow();
row9.CreateCells(dataGridView1);
row9.Cells[0].Value = Sayfa Sayısı;
row5.Cells[1].Value = doc.SummaryProperties.PageCount;
dataGridView1.Rows.Add(row9);
DataGridViewRow row0 = new DataGridViewRow();
row0.CreateCells(dataGridView1);
row0.Cells[0].Value = Boyut;
row0.Cells[1].Value = doc.SummaryProperties.Version Byte;
dataGridView1.Rows.Add(row0);Alıntı Onursorhan Cevaplabullet Konu: DSOFile Gönderim Zamanı: Bugün Saat 15:39 DSOFile İle İlgili Bilgisi Olan Varmı ! puplik void komut() { Gonsol.Wıratlayn(Wrak Wrak); }
DB:2.73:Word Properties d9
Onur Bey merhaba;
Buradaaynı içerikli sorunuza yanıtınızı bulduğunuzu belirtmişsiniz, bu yüzden soruyu çözüldü olarak işaretliyorum
SaygılarMicrosoft bu servisi kullanıcılarına yardım etme, Microsoft ürünleri ve teknolojileriyle ilgili bilgi bankasını genişletme amacıyla ücretsiz sunmaktadır.
Bu içerik olduğu gibi benim tarafımdan hazırlanmış olup Microsoft tarafından herhangi bir sorumluluk üstlenildiği anlamına gelmez.
Facebook Üzerinden Takip Et!
Twitter'da Takip Et! -
RELEVANCY SCORE 2.73
DB:2.73:Re: Count A Distinct Column Value In An Interactive Report s3
Hi again,
Just realised that this thread.
Interactive report unique row count
Is asking the same question.
Will watch this also.
Cheers
YogDB:2.73:Re: Count A Distinct Column Value In An Interactive Report s3
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 -
RELEVANCY SCORE 2.73
DB:2.73:How Do I Count The Unique Text Entries In A Column zm
I want to count thenumber of unique text entries in a column how do I do that?
DB:2.73:How Do I Count The Unique Text Entries In A Column zm
Or, if you will need to do this on different documents, you can copy this script into AppleScript Editor, select the cells in whichever document you are working on, and run. No need to set up the formulas and extra column again.SG--select range of cells, run; provides count of distinct valuestell application "Numbers" to tell the front document to tell active sheet set selectedTable to (the first table whose class of selection range is range) tell selectedTable to tell the selection range set distinctValues to {} repeat with aCell in its cells tell aCell if its value is not in distinctValues then copy its value to the end of distinctValues end tell end repeat return count of distinctValues end tellend tell--end of script
-
RELEVANCY SCORE 2.73
DB:2.73:Datagrid View Item -1 kp
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.TagDim 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 ThenItemLoc = I
Exit ForEnd 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
NextTextBox4.Text = Sum
payment.Totaltb.Text = Sum
End SubDB:2.73:Datagrid View Item -1 kp
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 -
RELEVANCY SCORE 2.73
DB:2.73:Another Sorted Datagridview Question jx
When the datagridview is NOT sorted this catches all records.When any column is sorted it misses records.Can anyone help to correct this and explain why it happens.1 private void btnGo_Click(object sender, EventArgs e) 2 { 3 //Change the grade codes enmass 4 if(string.IsNullOrEmpty (this.txtGradeFrom .Text ) || (string .IsNullOrEmpty (this.txtGradeTo .Text ))) {return;} 5 int Count = 0; 6 7 8 for (int x = 0; x (this.dGV .Rows.Count); x++) 9 { 10 if (this.dGV.Rows[x].Cells[Column3].Value.ToString() == this.txtGradeFrom.Text) 11 { 12 this.dGV.Rows[x].Cells[Column3].Value = this.txtGradeTo.Text; 13 Count++; 14 } 15 } 16 17 BD.MyMsgBox.Info(Count.ToString() + records changed ); 18 this.txtGradeFrom.Clear(); 19 this.txtGradeTo.Clear(); 20 } 21
DB:2.73:Another Sorted Datagridview Question jx
Hi Can-Ann,
When you change a cell's value of a Sort column, it will resort according to the new value. This actually will cause problem when you use a for statement to change the value of a sort column. Since we cannot stop sorting, the easiest way to avoid this is before you change the value of a sort column, you need to set sort on another column, after that, set the sort column back. Here is an example.public partial class Form1 : Form
{
private DataTable dtSource = new DataTable();
public Form1()
{
InitializeComponent();
// Create a DataTable for test
dtSource.Columns.Add(Column1, typeof(int));
dtSource.Columns.Add(Column2, typeof(string));
dtSource.Columns.Add(Column3, typeof(string));
dtSource.Rows.Add(1, Col1Item1, Col2Item1);
dtSource.Rows.Add(2, Col1Item3, Col2Item2);
dtSource.Rows.Add(3, Col1Item2, Col2Item3);
this.dataGridView1.DataSource = dtSource;
// Set Column1 as the sort column
dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}
private void button1_Click(object sender, EventArgs e)
{
// Set Column2 as the sort column
dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Ascending);
// Put for statement to modify values of Column1
// Set Column1 back to the sort column
dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}
}
-
RELEVANCY SCORE 2.73
DB:2.73:Counting Number Of Cells In A Column With A Given Value fx
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
DB:2.73:Counting Number Of Cells In A Column With A Given Value fx
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.
-
RELEVANCY SCORE 2.73
DB:2.73:Problem With Loop Macro 7c
Hello again:
I am having a problem with a macro that involves looping through two lists of text, the master list and search list, and identifying the duplicates as "duplicates" in columns next to each. The columns are designated as F, G, H and I or 6, 7,8 and 9. The lists
are of unequal length and the duplicates are in different rows. The loop works correctly for identifying the duplicates in the column next to the search list, column I or 9, but does not for identifying the master list duplicate in the column, g or 6, next
to the master list. I have tried what seems an infinite combination of methods but they either show "duplicates" in all the master list rows or the identical search list rows. The code for the macro is as follows:
Some of my trials are commented out.'count master list items
mListCount = Sheets("sheet2").Cells(Cells.Rows.Count, "q").End(xlUp).Row
mCtr = mListCount
'count search list items
sListCount = Sheets("sheet2").Cells(Cells.Rows.Count, "w").End(xlUp).Row
sCtr = sListCount
' Loop through the "master" list.
For j = 4 To mCtr
For Each c In Sheets("sheet2").Range(Cells(j, 7), Cells(mCtr, 7))
' Loop through all records in the search list.
For sCtr = 4 To sListCount
' identify duplicates
If c = Sheets("sheet2").Cells(sCtr, 8).Value Then
' If match is true then identify it in columns 9 and 6
Sheets("sheet2").Cells(sCtr, 9) = "duplicate"
' Sheets("sheet2").Cells(j, 6) = "duplicate" 'PUTS IT IN EVERY ROW OF MASTER LIST
' Sheets("sheet2").Cells(j, 6) = "duplicate" 'PUTS IT IN SAME ROW AS SEARCH LIST
End If
' If Sheets("sheet2").Cells(sCtr, 9).Value = "duplicate" Then
' Sheets("sheet2").Cells(j, 6) = "duplicate"
' End If
Next sCtr
' If Sheets("sheet2").Cells(sCtr, 9) = "duplicate" Then
' Sheets("sheet2").Cells(j, 6) = "duplicate"
' End If
Next
' If Sheets("sheet2").Cells(j, 6) = "duplicate" Then
' Sheets("sheet2").Cells(j, 6) = "duplicate"
'End If
NextAny help in resolving how to put the "duplicate" in the correct row next to the search column is greatly appreciated.
Lemorse
DB:2.73:Problem With Loop Macro 7c
Andreas,
I tried to run your macro but ran into compile and type mismatch problems. I will fool around with it and try to be more specific on the problems. I'll get back to you.
-
RELEVANCY SCORE 2.73
DB:2.73:"Priority" Cells pm
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?
DB:2.73:"Priority" Cells pm
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 9This 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 9Here 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).
-
RELEVANCY SCORE 2.72
DB:2.72:Amigos, Como Preencher Um Formulrio (Planilha Fopag) Utilizando-Se Somente De Linhas Filtradas De Uma Planilha (Dados)? No Caso Uma Folha De Pagamentos. 7d
Não tenho curso de vba, fico adaptando exemplos achados em fóruns.
Segue exemplo: mas não está funcionando. Muito obrigado!
================================================================
Sub Fopag()'
Dim x, i As IntegerWindows(FOPAG_ATUAL.xlsm).Activate
Application.ScreenUpdating = False
' Definir número de linhas
LS = Worksheets(DADOS).Cells(1, 16384)
For x = 0 To LS Step 10
i = 2
With Worksheets(DADOS)
While Not IsEmpty(.Cells(i, 1).Value)
If .Cells(i, 1).Rows.Hidden = False Then
i = i 1
End If
WendSheets(FOPAG).Select
Cells(2, 3) = .Cells(2 x, 5)
Cells(3, 2) = .Cells(2 x, 2) - .Cells(2 x, 3)
Cells(4, 5) = .Cells(2 x, 6)
Cells(5, 5) = .Cells(2 x, 7)
Cells(6, 5) = .Cells(2 x, 8)
Cells(7, 5) = .Cells(2 x, 9)
Cells(11, 5) = .Cells(2 x, 4)Cells(2, 11) = .Cells(3 x, 5)
Cells(3, 10) = .Cells(3 x, 2) - .Cells(3 x, 3)
Cells(4, 13) = .Cells(3 x, 6)
Cells(5, 13) = .Cells(3 x, 7)
Cells(6, 13) = .Cells(3 x, 8)
Cells(7, 13) = .Cells(3 x, 9)
Cells(11, 13) = .Cells(3 x, 4)Cells(15, 3) = .Cells(4 x, 5)
Cells(16, 2) = .Cells(4 x, 2) - .Cells(4 x, 3)
Cells(17, 5) = .Cells(4 x, 6)
Cells(18, 5) = .Cells(4 x, 7)
Cells(19, 5) = .Cells(4 x, 8)
Cells(20, 5) = .Cells(4 x, 9)
Cells(24, 5) = .Cells(4 x, 4)Cells(15, 11) = .Cells(5 x, 5)
Cells(16, 10) = .Cells(5 x, 2) - .Cells(5 x, 3)
Cells(17, 13) = .Cells(5 x, 6)
Cells(18, 13) = .Cells(5 x, 7)
Cells(19, 13) = .Cells(5 x, 8)
Cells(20, 13) = .Cells(5 x, 9)
Cells(24, 13) = .Cells(5 x, 4)Cells(28, 3) = .Cells(6 x, 5)
Cells(29, 2) = .Cells(6 x, 2) - .Cells(6 x, 3)
Cells(30, 5) = .Cells(6 x, 6)
Cells(31, 5) = .Cells(6 x, 7)
Cells(32, 5) = .Cells(6 x, 8)
Cells(33, 5) = .Cells(6 x, 9)
Cells(37, 5) = .Cells(6 x, 4)Cells(28, 11) = .Cells(7 x, 5)
Cells(29, 10) = .Cells(7 x, 2) - .Cells(7 x, 3)
Cells(30, 13) = .Cells(7 x, 6)
Cells(31, 13) = .Cells(7 x, 7)
Cells(32, 13) = .Cells(7 x, 8)
Cells(33, 13) = .Cells(7 x, 9)
Cells(37, 13) = .Cells(7 x, 4)Cells(41, 3) = .Cells(8 x, 5)
Cells(42, 2) = .Cells(8 x, 2) - .Cells(8 x, 3)
Cells(43, 5) = .Cells(8 x, 6)
Cells(44, 5) = .Cells(8 x, 7)
Cells(45, 5) = .Cells(8 x, 8)
Cells(46, 5) = .Cells(8 x, 9)
Cells(50, 5) = .Cells(8 x, 4)Cells(41, 11) = .Cells(9 x, 5)
Cells(42, 10) = .Cells(9 x, 2) - .Cells(9 x, 3)
Cells(43, 13) = .Cells(9 x, 6)
Cells(44, 13) = .Cells(9 x, 7)
Cells(45, 13) = .Cells(9 x, 8)
Cells(46, 13) = .Cells(9 x, 9)
Cells(50, 13) = .Cells(9 x, 4)Cells(54, 3) = .Cells(10 x, 5)
Cells(55, 2) = .Cells(10 x, 2) - .Cells(10 x, 3)
Cells(56, 5) = .Cells(10 x, 6)
Cells(57, 5) = .Cells(10 x, 7)
Cells(58, 5) = .Cells(10 x, 8)
Cells(59, 5) = .Cells(10 x, 9)
Cells(63, 5) = .Cells(10 x, 4)Cells(54, 11) = .Cells(11 x, 5)
Cells(55, 10) = .Cells(11 x, 2) - .Cells(11 x, 3)
Cells(56, 13) = .Cells(11 x, 6)
Cells(57, 13) = .Cells(11 x, 7)
Cells(58, 13) = .Cells(11 x, 8)
Cells(59, 13) = .Cells(11 x, 9)
Cells(63, 13) = .Cells(11 x, 4)
End With
ExecuteExcel4Macro PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)Next x
Application.ScreenUpdating = True
End SubDB:2.72:Amigos, Como Preencher Um Formulrio (Planilha Fopag) Utilizando-Se Somente De Linhas Filtradas De Uma Planilha (Dados)? No Caso Uma Folha De Pagamentos. 7d
Olá Meu amigo,
Realmente está um pouco confuso, por favor verifique se é isto que você pretende, copiar somente os dados que estão visíveis de um autofiltro.
Sub copiar()
'Ativa o Arquivo
Windows(FOPAG_ATUAL.xlsm).Activate
'Seleciona a planilha de Origem dos dados
Sheets(Dados).Select
'Copia somente as células visiveis
Range(A1).SpecialCells(xlCellTypeVisible).Copy
'Seleciona a planilha de destino
Sheets(FOPAG).Select
'Determina o local onde serão colados os dados
Range(B2).PasteSpecial xlPasteValuesAndNumberFormats
'Apaga a linha de cabeçahlho que foi copiada
ActiveCell.EntireRow.Delete
End Sub -
RELEVANCY SCORE 2.72
DB:2.72:Exception On Datagridview d1
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..DB:2.72:Exception On Datagridview d1
hi.......
thank u very very very much............i have solved my problem...........
thank u.......... -
RELEVANCY SCORE 2.72
DB:2.72:Export Specific Excel Cells To Access am
Hi,
I'm putting together a submission form in excel which will update a database in access.I'm currently trying to usea macro on the submit buttonto do it but and while i understand themajority of itimnotsure how to establish the connection through the macro.
Essentially im a VBA n00b and haven't completed a project like this before now.
So i guess my question is how would i go about establishing this through an excel based Macro?
This is my code for submitting the data into Access
rs.Open Sql, cn, adOpenDynamic, adLockOptimistic
With rs
.AddNew
.Fields(Date).Value = Trim(Cells(50, 1).Text)
.Fields(Agent).Value = Trim(Cells(50, 2).Text)
.Fields(Request).Value = Trim(Cells(50, 3).Text)
.Fields(Date1).Value = Trim(Cells(50, 4).Text)
.Fields(Date2).Value = Trim(Cells(50, 5).Text)
.Fields(Start).Value = Trim(Cells(50, 6).Text)
.Fields(End).Value = Format(Cells(50, 7).Value, hh:mm)
.Fields(RDO).Value = Trim(Cells(50, 8).Text)
.Fields(Call Type).Value = Trim(Cells(50, 9).Text)
.Update
End WithIf rs.State = adStateOpen Then rs.Close
If cn.State = adStateOpen Then cn.CloseSet rs = Nothing
Set cn = NothingMsgBox Your request has been submitted. vbNewLine vbNewLine Thanks..., vbOKOnly, John Smith
On Error Resume Next
Call Email
Range(e11:e18).ClearContentsEnd Sub
But what needs to go before it to establish the connection?
Also how would i find any SQL addressesrequired for the connection to be made?Please help!
DB:2.72:Export Specific Excel Cells To Access am
Hi Sixxthson,
According to your description, since this is SQL Server Forums, we need to verify that you want to export data from the excel cells to Access database or SQL Server database by using VB. If you copy data from an Excel spreadsheet into an Access database,
you need to use ADO connection object to open the database.
For more information, see:
http://www.vb-helper.com/howto_excel_to_access.html
In addition, as other post, since the issue regards VB, I suggest you post the question in the VB forums .
It is appropriate and more experts will assist you.
Regards,
Sofiya LiSofiya Li
TechNet Community Support -
RELEVANCY SCORE 2.72
DB:2.72:Character Counts For Spreadsheet Cells 8a
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?
DB:2.72:Character Counts For Spreadsheet Cells 8a
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
-
RELEVANCY SCORE 2.72
DB:2.72:Macro Filter 3s
Hi, I found this macro in the web for filtering database, with some variation to the ranges on the declarations to match my data.
Sub MyQuery()
Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String
Dim MyRow As Integer, LastDataRow As Integer, DataRng As String
Dim CritRow As Integer, CritRng As String, RightCol As Integer
Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer' the source data MUST be in a worksheet called 'Data'
' *** MODIFY AND SET YOUR OWN RANGES ON THE FOLLOWING DECLARATIONS ***
' cell Data!E2 contains the last row number of data [=COUNT(E4:E100)+3]
LastDataRow = Worksheets("Data").Range("E2").Value
' range of column headers for Data table
DataRng = "A3:E3"
' range of cells for Criteria table
CritRng = "I2:M5"
' range of headers for Results table
ResultsRng = "I9:M9"
MaxResults = 1000 ' any value higher than the number of possible results
' **************** END OF DECLARATIONS *********************
' fix the data range to incorporate the last row
TopRow = Range(DataRng).Row
LeftCol = Range(DataRng).Column
RightCol = LeftCol + Range(DataRng).Columns.Count - 1
DataRng = Range(Cells(TopRow, LeftCol), Cells(LastDataRow, RightCol)).Address
' fix the results range to incorporate the last row
TopRow = Range(ResultsRng).Row
LeftCol = Range(ResultsRng).Column
RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1
ResultsRng = Range(Cells(TopRow + 1, LeftCol), Cells(MaxResults, RightCol)).Address
Range(ResultsRng).ClearContents ' clear any previous results but not headers
ResultsRng = Range(Cells(TopRow, LeftCol), Cells(MaxResults, RightCol)).Address
' fix the criteria range and identify the last row containing any items
TopRow = Range(CritRng).Row
BottomRow = TopRow + Range(CritRng).Rows.Count - 1
LeftCol = Range(CritRng).Column
RightCol = LeftCol + Range(CritRng).Columns.Count - 1
CritRow = 0
For MyRow = TopRow + 1 To BottomRow
For MyCol = LeftCol To RightCol
If Cells(MyRow, MyCol).Value "" Then CritRow = MyRow
Next
Next
If CritRow = 0 Then
MsgBox "No Criteria detected"
Else
CritRng = Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)).Address
Debug.Print "DataRng, CritRng, ResultsRng: ", DataRng, CritRng, ResultsRng
Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _
Unique:=False
End If
End Sub
My initial data, criteria and result range is on thesame spreadsheet and thus not much issue. However, when I change the criteria and result range to show on another sheet with the following changes, error came up, Run time error '13' Type Mismatch.
' *** MODIFY AND SET YOUR OWN RANGES ON THE FOLLOWING DECLARATIONS ***
' cell Data!E2 contains the last row number of data [=COUNT(E4:E100)+3]
LastDataRow = Worksheets("Data").Range("E2").Value
' range of column headers for Data table
DataRng = Worksheets("Data").Range("A3:E3")
' range of cells for Criteria table
CritRng = Worksheets("Result").Range("B2:F5")
' range of headers for Results table
ResultsRng = Worksheets("Result").Range("B9:F9")
MaxResults = 1000 ' any value higher than the number of possible results
' **************** END OF DECLARATIONS *********************Finally much trial and error, I added Names to the new added sheets and changes to the macro with the new Names, it worked.My question here is that why does the initial change produce the error and why does
using Names in the macro work for this case then? Thanks....
' *** MODIFY AND SET YOUR OWN RANGES ON THE FOLLOWING DECLARATIONS ***
' cell Data!E2 contains the last row number of data [=COUNT(E4:E100)+3]
LastDataRow = Worksheets("Data").Range("E2").Value
' range of column headers for Data table
DataRng = "DataHeader"
' range of cells for Criteria table
CritRng = "Criteria"
' range of headers for Results table
ResultsRng = "Result"
' any value higher than the number of possible results
MaxResults = 1000
' **************** END OF DECLARATIONS *********************DB:2.72:Macro Filter 3s
Hi Shane,
Thanks for your response. I am learning how to write code and thus searching for codes that I found to beuseful functions and which I might possibly use in future. I was playing around with this and encountered some problems which I managed to work out
at the end. So just kind of looking what did I do wrong when I first modified it. I like your statement that writing from scratch is better and on my personally note, I also get satisfaction when I did it from scratch rather than copy and modified it. So I
will need to work extra hard to learn VB.
What I hope to do in future is to develop a userform that allows me to input information which will be transferred to say sheet 'Data'. The field title will be across column and each row will belong to 1 entry. Information might be change along the way and
upon a clicking event, depending on certain words within a column, that row of information will be deleted and transferred to either 1 of 2 sheets say 'Good' and 'Bad'.
I would like to have a filter function for all 3 sheets so that the end result will be displayed on say sheet 'Display'. The search criteria preferrably be flexible as in say 'Amount' must bein a certain range and / or only records where amount, date and
description must match the search criteria.
In additional, I like to show charts in a dashboard format on the records in all 3 sheets in a separate sheet, which are dynamic in nature. This should not pose a problem as I have some experience with dynamic charts.
All these is a big project for me who just started out learning VB, but I believed will get the hang of it. Thanks again for your feedback.When I really get startedon workingin this personal project and encountered any issue, will post in this forum for
ideas/solutions. -
RELEVANCY SCORE 2.71
DB:2.71:Count Unique Names Meeting A Condition 9c
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 BThanks in advance
-
RELEVANCY SCORE 2.71
DB:2.71:Can I Count The Cells In A Column That Have Text In Them 9p
I want to be able to have a count of how many cells in a specific column have any text in them at all. If in column C i have say cells C5, C7, C8, C9, and C11 with some text in them i want a specific cell, say C20 to have the total, in this case it would
be 5. Is this possible?DB:2.71:Can I Count The Cells In A Column That Have Text In Them 9p
Hey Christop 1900,
Greetings of the day.... yes it is possible where you may add the formula as mentioned below:
=COUNTA(C1:C19)
=5
what count A would do is count the number of non blank cells.
Hope this helps friend take care and keep excel...ling
Kamat -
RELEVANCY SCORE 2.71
DB:2.71:If Variable = Variable Does Not Work zf
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?DB:2.71:If Variable = Variable Does Not Work zf
Are you attempting to compare string values when you really want to compare the values of those strings converted to doubles?
-
RELEVANCY SCORE 2.70
DB:2.70:C# And Excel fp
How can I get number of cells containing formulas,constants,cells with Datetime datatype,cells with Text datatype,cells with comments.It should be in c#.I have to get count of cells.
Please help meDB:2.70:C# And Excel fp
Hi,
I think that a c# forum might be better for your question.
Maybe
http://stackoverflow.com/questions/15828/reading-excel-files-from-chas some nice information that already helps you.
With kind regards,
Konrad -
RELEVANCY SCORE 2.70
DB:2.70:Question With String Concatenation In Visual Basic 2005 k8
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 intxtDisplay.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) --
SimplePrivate
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
IntegerDim vtemp
As
String
vtemp =Dim iRowIndex, iColumnIndex, iSearchText
As
IntegerDim 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) * 100For iColumnIndex = 6
To 16For iSearchText = 1
To 30If ((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))
ThenIf (vtemp (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value))
ThenIf ((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 1End
IfIf ((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 1End
IfIf ((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 1End
IfEnd
IfEnd
IfEnd
IfNext 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 =
FalseMe.Hide()
End
SubDB:2.70:Question With String Concatenation In Visual Basic 2005 k8
The Problem is the concatenation of with
xlBook.Worksheets(2).Cells(iRowIndex, 3)
-
RELEVANCY SCORE 2.70
DB:2.70:Range Of Cells 9f
Does anyone know how I would calculate the following:
I have a range of cells headed Thur,Fri,Sat,Sun,Mon,Tues,Wed. I also have a total column that must add numbers (hours) that are entered into the cells except any value in Sat,Sun. The calculation must also ignore totalling any text value in any of the
cells.DB:2.70:Range Of Cells 9f
I'm not sure what you are trying to calculate, the number of hours to be attributed to saturdays and sundays, taking into account how many weekday hours have been worked, something like that?
I inserted two columns just after Fri (so M and N become Sat and Sun)
Then I wrote this formula below O/T1 (now column O):=IF(M10-IF(SUM(H10:L10)40,40-SUM(H10:L10),0)0,0,M10-IF(SUM(H10:L10)40,40-SUM(H10:L10),0))
and in column P:
=N10-(40-SUM(H10:L10))+(M10-O10)
Have a look whether that works for you.
-
RELEVANCY SCORE 2.70
DB:2.70:2d Arrays: How To Read Specific Array Cell Attributes For Their Numerical Value? 3x
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,
ADAndrewDenDB:2.70:2d Arrays: How To Read Specific Array Cell Attributes For Their Numerical Value? 3x
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. -
RELEVANCY SCORE 2.70
DB:2.70:Count Filtered Visible Cells And Paste Value To Another Sheet As Number In Vba sm
Hi All,
I have a excel workbook which contains 24000 records and about 8 columns. I would like to filtered on 4 criteria's and count number of visible rows and paste the value(number) to another sheet. I have the following code which filters and give the numbers
but I don't know how to copy this value and paste it into another sheet. Please help me.Thanks
Abdul
Sub Macro1()
'
' Macro1 Macro
'
'
Dim Myval As Integer
Dim x As Integer
Sheets("data").Activate
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("data").Range(Cells(2, 1).Address ":" Cells(finalrow, 8).Address).AutoFilter Field:=1, Criteria1:= _
"Project"
Worksheets("data").Range(Cells(2, 1).Address ":" Cells(finalrow, 8).Address).AutoFilter Field:=2, Criteria1:="West"
Worksheets("data").Range(Cells(2, 1).Address ":" Cells(finalrow, 8).Address).AutoFilter Field:=3, Criteria1:= _
"xyz"
Worksheets("data").Range(Cells(2, 1).Address ":" Cells(finalrow, 8).Address).AutoFilter Field:=4, Criteria1:="Actual"
Myval = Worksheets("data").Range(Cells(2, 1).Address ":" Cells(finalrow, 8).Address).Columns(3).SpecialCells(xlCellTypeVisible).Count
MsgBox "Visible Rows " Myval
Worksheets("data").ShowAllData
End Sub -
RELEVANCY SCORE 2.70
DB:2.70:Count Unique Values In Column cx
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!DB:2.70:Count Unique Values In Column cx
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 -
RELEVANCY SCORE 2.69
DB:2.69:Change Format To Number Has No Effect On Already-Input Cells f7
Hello everyone
I have a number of cells containing numerical information that were input by hand. The cell type must have been General or Text initially because when selecting several cells, I only see Count in the bottom bar, and not Sum, Average etc. The problem is that
setting the cell type for all these cells to Number does not solve the problem, and only if I manually overtype each cell with the same value, only then I get those values in the bottom bar. Clear Formatting for these cells also did not help.Can anyone suggest anything? Thanks!
DB:2.69:Change Format To Number Has No Effect On Already-Input Cells f7
Thanks, both your suggestions worked!
-
RELEVANCY SCORE 2.69
DB:2.69:Seleccionar Dato De Datagridview Y Omitir Si Fila No Existe 7z
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).Valuelbl2.Text = DataGridView3.Rows(1).Cells(0).Value
lblT2.Text = DataGridView3.Rows(1).Cells(1).Valuelbl3.Text = DataGridView3.Rows(2).Cells(0).Value
lblT3.Text = DataGridView3.Rows(2).Cells(1).Valuelbl4.Text = DataGridView3.Rows(3).Cells(0).Value
lblT4.Text = DataGridView3.Rows(3).Cells(1).Valuelbl5.Text = DataGridView3.Rows(4).Cells(0).Value
lblT5.Text = DataGridView3.Rows(4).Cells(1).Valueme 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.
DB:2.69:Seleccionar Dato De Datagridview Y Omitir Si Fila No Existe 7z
Ahora si funciono, gracias te pasaste
-
RELEVANCY SCORE 2.69
DB:2.69:How Can I Import Data From Excel Sheet To Textbox Datagridview ? jd
I'm already exported data of ( Label-Textbox-DataGridView ) .. the question is how i can import the exported excel file to the same control .Notice: I'm using OpenFileDialogue
The Export code :
Dim rowsTotal, colsTotal As Short
Dim I, j, iC As Short
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp As New Excel.Application
Try
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add
Dim excelWorksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = True
rowsTotal = DataGridView1.RowCount - 1
colsTotal = DataGridView1.Columns.Count - 1
With excelWorksheet
.Cells.Select()
.Cells.Delete()
For iC = 0 To colsTotal
.Cells(11, iC 1).Value = DataGridView1.Columns(iC).HeaderText
NextFor I = 0 To rowsTotal
For j = 0 To colsTotal
.Cells(I 12, j 1).value = DataGridView1.Rows(I).Cells(j).FormattedValue
Next j
Next I
.Rows(1:200).Font.FontStyle = BOLD
.Rows(1:200).Font.Size = 12
.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1, 1).select().Cells(1, 1) = Label9.Text
.Cells(1, 2) = TextBox1.Text
.Cells(2, 1) = Label10.Text
.Cells(2, 2) = TextBox2.Text
.Cells(3, 1) = Label11.Text
.Cells(3, 2) = DateTimePicker1.Text
.Cells(4, 1) = Label12.Text
.Cells(4, 2) = TextBox4.Text
.Cells(5, 1) = Label13.Text
.Cells(5, 2) = TextBox5.Text
.Cells(6, 1) = Label14.Text
.Cells(6, 2) = DateTimePicker2.Text
.Cells(7, 1) = Label15.Text
.Cells(7, 2) = TextBox7.Text
.Cells(8, 1) = Label16.Text
.Cells(8, 2) = TextBox8.Text
.Cells(10, 1) = Label17.Text
.Cells(10, 2) = DateTimePicker3.Text
.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1, 1).select()
End With
Catch ex As Exception
MsgBox(Export Excel Error ex.Message)
Finally
'RELEASE ALLOACTED RESOURCES
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
xlApp = Nothing
End Try
PLZ HelpDB:2.69:How Can I Import Data From Excel Sheet To Textbox Datagridview ? jd
Lot of times discussed this scenario in MSDN and pls look at the following links and it have detailed info with solutions..
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/54ab2d19-6808-4b32-a970-573ce7644c07
http://social.msdn.microsoft.com/forums/en-US/winforms/thread/ed045a59-a5ea-4421-a2d4-bea18bbf1c8c/
http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/b2c12af8-3afe-4f32-bc2a-7e4116fb1adeIf a post answers your question, please click Mark As Answer
on that post and Mark as Helpful -
RELEVANCY SCORE 2.69
DB:2.69:Counting Cells Based On Specific Criteria 38
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.DB:2.69:Counting Cells Based On Specific Criteria 38
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 -
RELEVANCY SCORE 2.69
DB:2.69:Exception..... a7
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..DB:2.69:Exception..... a7
The problem is that the i in the button_click is not valid when you click the button after clicking the save button, I think you have an unbound DataGridView and want to add a new row with value for each cells from the controls when clicking the button, right? You should change the value of the variable i to Rows.Count -1 before assigning values for the cells.However, for this kind of scenario, I would recommand you to bind the DataGridView, you can use the DataSource Configuration Wizard to assign a table in the database for the DataGridView without even writing a single line of code, and for saving the changes, you can just call the TableAdapter.Update() method to save the changes back to the database, the data binding mechanism will make you life easy, just go HERE and learn more.
-
RELEVANCY SCORE 2.69
DB:2.69:Count Cells By Color That Will Never Contain Values x3
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
DB:2.69:Count Cells By Color That Will Never Contain Values x3
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 Functionthen 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,
-
RELEVANCY SCORE 2.69
DB:2.69:What Is The Easiest Way To Count The Number Of Unique Records In A Set. Ie Don't Count Records That Have A Duplicate Value In A Specific Field? 1x
What is the easiest way to count the number of unique records in a set. Ie don't count records that have a duplicate value in a specific field?DB:2.69:What Is The Easiest Way To Count The Number Of Unique Records In A Set. Ie Don't Count Records That Have A Duplicate Value In A Specific Field? 1x
In the list build from field of a database duplicates are omitted, multiple spaces are replaced by single pace and list values are seppareted with carriage return. The time to response is acceptable for smaller databases. Databases over 2 GB are still rare.I think that solution at http://help.filemaker.com/app/answers/detail/a_id/6186 could be faster. The principle is quasi similar i.e. self list versus self relation.