• 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"
    Leo

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

    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?

  • 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 array

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

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

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

    Who has the answer?

    Thank you

    Ron1Cebu

    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 Long

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Stop

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

    Rem clean up code (if needed) ...

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

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

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

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

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

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

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

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

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

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

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

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

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

    lngLookUpRow = lngLookUpRow 1
    Loop

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

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

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

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

    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
    Next

    DB: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 you

  • 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).Row

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

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

    DB: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ódigo

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

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

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

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

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

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

    End Sub

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

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

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

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

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

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

    End If

    Next

    TextBox1.Text = vbNewLine

    Next

    End Sub

    E faz isso

    E o que eu quero seria isso

    Obrigada

    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.
    Tipo

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

    Public Class Form1

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

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

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

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

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

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

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

    End Sub

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

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

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

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

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

    End Sub


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

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

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

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

    End Using
    fluxoTexto.Close()
    End Sub

    End Class

  • 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.CellClick

    frmMostrar.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???
    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.HomeCookN

    DB: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 k

    DB: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 k

    Regards, 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
    Abi

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

    'Declare variables locally to search for scripts in the Excel

    Dim vSimpleCount, vComplexCount, vMediumCount
    As
    Integer

    Dim vtemp
    As
    String
    vtemp =

    Dim iRowIndex, iColumnIndex, iSearchText
    As
    Integer

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

    'Store the Script Excel path in the Objext
    xlBook = GetObject(
    C:\Documents and Settings\WAW165\Desktop\Impact assessment 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) * 100

    For iColumnIndex = 6
    To 16

    For 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))
    Then

    If ((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 1

    End
    If

    If ((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 1

    End
    If

    If ((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 1

    End
    If

    End
    If

    End
    If

    End
    If

    Next iSearchText

    Next iColumnIndex

    Next iRowIndex

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

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

    Me.Hide()

    End
    Sub
    End

    Class

    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.htm

    Regards 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 Sub

    or...

    Sub VisiCount()
    Dim VisibleCount As Long
    On Error GoTo Whoops
    VisibleCount = Selection.SpecialCells(xlCellTypeVisible).Count
    Whoops: MsgBox VisibleCount
    End Sub

    NOTE: 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 advance

    ajay

    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 in

    Excel 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 count

    DB: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.Documents

    soDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders)
    dnDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes)

    Try
    Dim rsData As SAPbobsCOM.Recordset
    Dim iTemp As Integer

    iTemp = 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 creation

    Dim count As Short
    Dim items As SAPbobsCOM.Items

    items = 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.TaxCode

    Dim 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.BaseLine

    MessageBox.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.BaseLine

    MessageBox.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 = 0

    If count + 1 dgvMat.RowCount Then
    If dgvMat.Rows(count + 1).Cells("LineNum").Value = dgvMat.Rows(count).Cells("LineNum").Value Then

    While 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 If

    If 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 If

    Else
    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 While

    Dim 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 If

    Catch ex As Exception
    MessageBox.Show(ex.Message)
    End Try

    DB: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 Integer

    linha = 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 With

    Plan1.Cells(4, 9).Value = .Selection.Text
    .ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

    With .Selection
    .HomeKey Unit:=wdStory
    .MoveRight Unit:=wdCell
    .MoveRight Unit:=wdCell
    End With

    Plan1.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).Value

    End 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 With

    With .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 With

    wdApp.Documents(Plan1.Cells(linha, 2).Value).Save

    wdApp.Quit

    Set wdApp = Nothing
    Set wdDoc = Nothing

    Dim wdApp1 As Word.Application
    Dim wdDoc1 As Document
    Set wdApp1 = New Word.ApplicationSet

    wdApp1.Visible = 1
    wdDoc1 = wdApp1.Documents.Open(Plan1.Cells(8, 6).Value)
    With wdApp1

    With .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
    .Paste

    End With

    End With

    MsgBox 'aqui é o ponto de parada para depuração

    Set wdApp1 = Nothing
    Set wdDoc1 = Nothing

    wdApp1.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
    Try

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

    SeatsUsed = SeatsUsed + Convert.ToInt16(CountUsed)
    Catch

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

    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

    Loop

    TextBox1.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) Minutes

    If 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
    Next

    End 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,
    Jas

    DB: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 Mark

    DB: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 Count

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

    Hope this helps.

  • 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 I

    The 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 Sub

    DB: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..

  • 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 use

    Private 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.Count

    If 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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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.Red

    Else

    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 Sub

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

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

    My ultimate goal

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

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

    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 C

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

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

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

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

    Regards, Hans Vogelaar

  • 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. Thanks

    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. 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.Tick

    ProgressBar1.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
    Next

    End If
    End If
    Next

    correo()
    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.Date

    End 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 Sub

    DB: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.Rows

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

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

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

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

    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 this

    DB: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?

  • 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
    Daniel

    DB: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 4

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

    SQL set timing on
    SQL /

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

    Elapsed: 00:01:16.15

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

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

    Elapsed: 00:00:10.12
    SQL

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

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

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

    Why there is too much difference?

    Best regards

    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 4

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

    Elapsed: 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
    /Item

    Item
    TypeITM22/Type
    Value6666/Value
    /Item

    Item
    TypeITM22/Type
    Value7777/Value
    /Item

    /ItemList
    /Product
    /ProductList
    /ProductIdentificationResponse

    I 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

  • 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: index

    what 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 suggestions

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

    DB: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: Lemon

    Total = 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 matylda

    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 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
    Abi

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

    'Declare variables locally to search for scripts in the Excel

    Dim vSimpleCount, vComplexCount, vMediumCount
    As
    Integer

    Dim vtemp
    As
    String
    vtemp =

    Dim iRowIndex, iColumnIndex, iSearchText
    As
    Integer

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

    'Store the Script Excel path in the Objext
    xlBook = GetObject(
    C:\Documents and Settings\WAW165\Desktop\Impact assessment 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) * 100

    For iColumnIndex = 6
    To 16

    For 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))
    Then

    If ((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 1

    End
    If

    If ((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 1

    End
    If

    If ((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 1

    End
    If

    End
    If

    End
    If

    End
    If

    Next iSearchText

    Next iColumnIndex

    Next iRowIndex

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

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

    Me.Hide()

    End
    Sub
    End

    Class

    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 Int32

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

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

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

  • 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
    Yog

    DB: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.Tag

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

    ItemLoc = I
    Exit For

    End If
    Next

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


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

    End If

    TextBox1.Text =
    TextBox1.Focus()

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

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

    End Sub

    DB: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
    Next

    Any 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 9

    This preserves the existing relative order of the unchanged items.

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

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

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

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

  • 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 Integer

    Windows(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
    Wend

    Sheets(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 Sub

    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

    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 With

    If rs.State = adStateOpen Then rs.Close
    If cn.State = adStateOpen Then cn.Close

    Set rs = Nothing
    Set cn = Nothing

    MsgBox Your request has been submitted. vbNewLine vbNewLine Thanks..., vbOKOnly, John Smith
    On Error Resume Next
    Call Email
    Range(e11:e18).ClearContents

    End 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 B

    Thanks 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 me

    DB: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 in

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

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

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

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

    'Declare variables locally to search for scripts in the Excel

    Dim vSimpleCount, vComplexCount, vMediumCount
    As
    Integer

    Dim vtemp
    As
    String
    vtemp =

    Dim iRowIndex, iColumnIndex, iSearchText
    As
    Integer

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

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

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

    'Search for Scripts in the Excel

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

    For iColumnIndex = 6
    To 16

    For iSearchText = 1
    To 30

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

    'Searching for the Keyword in the Excel

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

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

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

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

    'Increment the Simple Scripts Count
    vSimpleCount = vSimpleCount 1

    End
    If

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

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

    'Increment the Medium Scripts Count
    vMediumCount = vMediumCount 1

    End
    If

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

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

    'Increment the Complex Scripts Count
    vComplexCount = vComplexCount 1

    End
    If

    End
    If

    End
    If

    End
    If

    Next iSearchText

    Next iColumnIndex

    Next iRowIndex

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

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

    Me.Hide()

    End
    Sub

    DB: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,
    ADAndrewDen

    DB: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!

  • 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).Value

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

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

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

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

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

  • 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
    Next

    For 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 Help

    DB: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 Function

    then try the following function as well

    Try the following

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

    tested sheet using count cell color

    finally you have to save your sheet with .xlsm extension

    Gratefully,

  • 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.