• RELEVANCY SCORE 3.78

    DB:3.78:How To Find/Count Duplicate Cells In Worksheet pc




    I have an Excel 2010 worksheet with two columns that contain about 500 cells each. The contents of the cells are names (such as Bob Smith). I need to get a count of all names that appear in both columns. I've tried this:
    =COUNTIF($A$2:$A$500, B$2:B$500)
    no luck.
    What I'm looking for is a simple number to appear in the cell that contains this formula.
    Kinda new with Excel formulas... appreciate the help.

  • RELEVANCY SCORE 3.14

    DB:3.14:How Do I Duplicate Text In One Worksheet To Another Worksheet In The Same Book 88




    I'm having employees write text in cells on one worksheet and I would like it to duplicate the results on a seperate worksheet.
    I know how to do it with numbers just not text.

    DB:3.14:How Do I Duplicate Text In One Worksheet To Another Worksheet In The Same Book 88

    Hi, not sure what you need , you can link both books with a formula like
    ='[Book1.xlsm]Sheet1'!A4

  • RELEVANCY SCORE 3.10

    DB:3.10:Function Question 38




    Here is what I'm trying to do:
    I want tofind the contents of a specific cell in worksheet A within a range of cells in worksheet B. Then I want to add a value from another column in the corresponding row of worksheet B, to a value in another column in the corresponding row of worksheet
    A.

    For example, I have a social security number in cell A1 of worksheet A. I want to find that same social security number in the range of cells A1:A20 on worksheet B. Lets' say on worksheet B it's in cell A5. I then want to sum the value in cell C5 (so
    same row, different column)on worksheet B, with the value in C1 (again, same row, different column)on worksheet A.

    Thanks.

    DB:3.10:Function Question 38

    Hi,

    Try this. It will return a blank if it can't find the SS No on the other sheet.

    =IFERROR(VLOOKUP(A1,Sheet2!$A$1:$C$20,3,FALSE)+C1,"")

  • RELEVANCY SCORE 3.06

    DB:3.06:How Do I Protect A Header In A Worksheet p9


    I have been able to protect a worksheet by locking specific cells while allowing other cells to be accessed. I have not been able to find out how to protect my header from being changed.

    DB:3.06:How Do I Protect A Header In A Worksheet p9

    Please let Microsoft know you want this by choosing Send Feedback about Excel from the Excel Help menu.

  • RELEVANCY SCORE 3.02

    DB:3.02:Need Help Adding Cells Across Multiple Worksheets Using Sumif And Vlookup xa


    I have 3 worksheets within a file. Onworksheet 1, I would like to do a VLookup toworksheet 2 3 to find values that match ap/n on worksheet 1. The content and layout of the worksheets is different, but they do contain some of the same p/n's on all
    3worksheets. How do I structure the formula?
    worksheet 1 p/n's
    12357
    25478
    36254
    worksheet 2
    12357 valueis 6000
    25478 value is 8500
    36254 valueis 26547
    worksheet 3
    12357 valueis 500
    25478 valueis 880

    I want these values to be the added result on worksheet 1
    12357 combined value is 6500
    25478 combined value is 9380
    36254 combined value is 26547 (there is no match for this one on worksheet 3, so value is only what is on worksheet 2)

    Any help is appreciated. Thank you!

    DB:3.02:Need Help Adding Cells Across Multiple Worksheets Using Sumif And Vlookup xa


    I have 3 worksheets within a file. Onworksheet 1, I would like to do a VLookup toworksheet 2 3 to find values that match ap/n on worksheet 1. The content and layout of the worksheets is different, but they do contain some of the same p/n's on all
    3worksheets. How do I structure the formula?
    worksheet 1 p/n's
    12357
    25478
    36254
    worksheet 2
    12357 valueis 6000
    25478 value is 8500
    36254 valueis 26547
    worksheet 3
    12357 valueis 500
    25478 valueis 880

    I want these values to be the added result on worksheet 1
    12357 combined value is 6500
    25478 combined value is 9380
    36254 combined value is 26547 (there is no match for this one on worksheet 3, so value is only what is on worksheet 2)

    Any help is appreciated. Thank you!

    Thank you Vijay. This worked perfectly. I appreciate your help!

  • RELEVANCY SCORE 2.97

    DB:2.97:Access Cells In Ms Office Excel Worksheet With Value2 Property k8



    I'm developing a sample application for access values from an active MS Excel 2010 worksheet. I used the following code specified in an example of a book I'm reading.
    1. Excel.Worksheet myWorksheet = Globals.ThisAddIn.Application.ActiveSheet
    2. as Excel.Worksheet;
    3. string strProductName = myWorksheet.Cells[2, 1].Value2.ToString();

    When I typed the code in Visual Studio, in line 3 after the Cells[2, 1], .Value2 property is not present. instead of it only some methods are available including ToString(). Am I missing any assembly reference? I can't find which one is missing.

    DB:2.97:Access Cells In Ms Office Excel Worksheet With Value2 Property k8

    Thanks a lot. I was struggling with this issue for two days and you showed me the path. :)

  • RELEVANCY SCORE 2.90

    DB:2.90:How To Print A Range Of Cells Of A Hidden Worksheet 8s


    I have a excel program with 2 worksheet. One of them is hidden. I want to print a range of cells of that hidden worksheet.

    DB:2.90:How To Print A Range Of Cells Of A Hidden Worksheet 8s

    I looked at that a few days ago and decided I could contribute very little or nothing.

    Can't get started on working it out.

    Gord

  • RELEVANCY SCORE 2.90

    DB:2.90:Use Conditional Formatting To Highlight Duplicate Data Across Several Rows pa


    Hello,
    I am trying to find an easier way to highlight duplicate data across several rows without having to create a formula for each row.
    Example Worksheet (top row is the column headings, all following rows are the data, I have numbered each row)
    A B C D
    1 x 1 2 x
    2 x y 3 a
    3 a a a 0

    My goal: highlight the duplicates within each row, and not throughout the entire matrix. So, my desired result would be:
    A B C D
    1 x 1 2x
    2 x y 3 a
    3 a a a 0
    Yet, when I use the "Find duplicates" option within conditional formatting, I get:
    A B C D
    1 x 1 2x
    2 x y 3a
    3 a a a 0
    It seems that the way the "duplicate" conditional formatting option works within Excel 2007 is that duplicates can only be found within the current selection.
    At the moment, the only way to get the highlighting I desire is to create a conditional formatting rule for each row. Ideally I would like to be able to select several cells and then set the formatting to only compare the values within each row (instead
    of within the entire selection).
    What I want to know, is there any way to get my desired result with one formula?
    Thank you.

    DB:2.90:Use Conditional Formatting To Highlight Duplicate Data Across Several Rows pa

    Excel sheet settings:

    Cell format = "General" Font = Calibri, Size = 11 Cell Protection = Unlocked Cell formats were cleared before entering formula for conditional formatting.Each character is the only inhabitant of the cell; meaning there are no spaces before or after the characters (which may influence the outcome)

    I expanded the matrix from my initial post to be:
    A B C D
    1 x 1 2 x
    2 x y 3 a
    3 a a a 0
    4 1 2 4 1
    5 b c d e
    6 b b a 1
    7 2 4 5 4
    8 x y x a
    9 a a a 0
    10 1 2 4 1
    11 b c d e
    12 b b a 1

    @Jeeped;
    Thank you for tackling this question for me. However, when I input your equation into I was able to achieve the desired result.
    HOWEVER, I think it may be worth noting, that after I input you equation the first time, nothing happened. When I went to look at the formula, here is what I saw:
    =COUNTIF($A1048566:$D1048566,D1048566)1

    So, I naturally corrected the error in the equation so it would be:
    =COUNTIF($A1:$D1,D1)1
    And here was my resulting matrix:

    [Sorry for the bad paste, but I didn't feel like making this pretty today]
    So, the rows filled in as expected! Yeah!

    @Kirby . Thank you for also tackling this question. I have taken your equation and altered the column letters to coincide with my matrix. This gives me an equation of:
    =OR(COUNTIF(A1:D1,A1)1,COUNTIF(A1:D1,B1)1,COUNTIF(A1:D1,C1)1,COUNTIF(A1:D1,D1)1)
    However, when I input the equation into the Conditional formatting tool, I got the same result as I did when I input Jeeped's equation:
    =OR(COUNTIF(XFB1048566:A1048566,XFB1048566)1,COUNTIF(XFB1048566:A1048566,XFC1048566)1,COUNTIF(XFB1048566:A1048566,XFD1048566)1,COUNTIF(XFB1048566:A1048566,A1048566)1)

    Unfortunately, Rows 1,3, 6, 7, 8, 9 and 12 are not cooperating.

    OVERALL, the 'COUNTIF' equation supplied by Jeeped solved my problem. Kirby, you solution seems sound, however, it did not work for me in this case.

  • RELEVANCY SCORE 2.89

    DB:2.89:I Need To Create A Formula Or Function To Count Pages In A Worksheet. p8


    01-01-2010 To 06-30-2010 Box Data Finance Type Uninsured

    Box Number

    of

    Case Number

    Address

    Reconciled Date
    Closed Date
    Financing Type

    I need to create a formula for specific cells to automatically count in ascending order( as shown in the example above, Box Number 1 of ?, 2 of ?, 3 of ?, etc.) for each page of the worksheet. My problem is making sure the formula or function is in the
    cells that I specify and that they do count the pages in those cells I choose in ascending order on each page.
    Simular to when you choose the Header/Footer option under the View tab and you hit the drop down menu for either header of footer and you see the option( Page 1 of ?) choose it and it will automatically number each page of that particular worksheet either
    at the top of the page or the bottom of the page in ascending order.

    DB:2.89:I Need To Create A Formula Or Function To Count Pages In A Worksheet. p8

    do you have any data which you can provide us to work out a formula?

    If the above was helpful please click "Vote as helpful". If it solves your problem click "Mark As Answered". Cheers !!!!!!!!!!

  • RELEVANCY SCORE 2.83

    DB:2.83:Count Duplicate Object df


    how to count duplicate data in map and list ?

  • RELEVANCY SCORE 2.81

    DB:2.81:Performance Issue 9k


    Hi,
    What do you think about ODP when manipulating worksheet : I find it's not that great . Will it be realistic to compare it to Excel : example : changing values in cells, recalculate, save, etc.

    Thanks

    DB:2.81:Performance Issue 9k

    Hi,
    What do you think about ODP when manipulating worksheet : I find it's not that great . Will it be realistic to compare it to Excel : example : changing values in cells, recalculate, save, etc.

    Thanks

  • RELEVANCY SCORE 2.81

    DB:2.81:Function Call Substitute For Currentregion 11


    All,
    Is there a "better" way....
    I'm sure that some of you are aware that the CurrentRegion property does not return a result when called from a function. I have come across a number of instances where I’ve needed the CurrentRegion property returned within a function call.As
    a result, I created a function that is similar to CurrentRegion as a work around to this issue.
    I have listed my code below for “CustomCurrentRegion”.The code, which may seem rather long, is really a “copy” of itself; I haven’t taken the time to condense “similar” code blocks into Subs/Functions or to eliminate redundancies.Overall,
    it appears to be working in a similar manner to the CurrentRegion property.(“TestCustomCurrentRegion” is used simply to call “CustomCurrentRegion” for testing purposes). The code can be easily copied and pasted into the VBE (and take note that
    I pasted in Option Explicit below as well). The spacing below may look awkward, but it will be more easily read in the VBE. The code is not extensively commented, but hopefully there are enough comments to understand the scope.
    What I’m curious to know is the following:Is there a “better/best” way to create a function that mimics the CurrentRegion property?And/or, is there a way to improve/change the code below to be “better”?
    Thanks for your input,
    Matthew Herbert
    Option Explicit
    Sub TestCustomCurrentRegion()
    Dim rngRes As Range
    Set rngRes = CustomCurrentRegion(ActiveCell)
    If rngRes Is Nothing Then
    Debug.Print " Res: Nothing"
    Else
    Debug.Print " Res: "; rngRes.Address
    End If
    Set rngRes = Nothing
    End Sub
    Function CustomCurrentRegion(Rng As Range) As Range
    '---------------------------------------------------------------------
    'INFO: 06/15/2010, Matthew Herbert
    '---------------------------------------------------------------------
    'PURPOSE: The CurrentRegion property of the Range Object will NOT
    ' work when called from a function. As a result, this
    ' function mimics the CurrentRegion property (i.e. select
    ' a cell(s) within the worksheet and press Ctrl+Shift+* on
    ' the keyboard to see a visual selection of CurrentRegion,
    ' or access CurrentRegion from the "Go To" dialog box under
    ' the "Special" button).
    '
    ' The function is intended to be called from ONE cell, and
    ' the range used within the function is set to Rng(1) and
    ' NOT the ActiveCell within Rng.
    '
    'Rng [in] A reference range from which the current region is
    ' built.
    '
    'RETURNS: Nothing - This result should NEVER occur, though it's
    ' coded for a "just in case" scenario.
    ' Range Object - The Range of the current region
    '---------------------------------------------------------------------
    'UTILIZES: GetOuterRange
    '---------------------------------------------------------------------
    'NOTES: I have not exhaustively tested .CurrentRegion b/c I
    ' don't see a need for testing it such that CustomCurrentRegion
    ' behaves the same way. In a normal scenario, I get the
    ' .CurrentRegion from a single cell Range.
    '
    ' One may want to change the Rng(1) to the ActiveCell within
    ' Rng.
    '
    'PATTERNS: Diagonal directions are a combination of Up/Down and Left/Right.
    ' Up/Left and Down/Right have similar structures (in context
    ' of evaluating the code below).
    '
    ' UP DOWN LEFT RIGHT
    ' Exit Do =0 Max =0 Max
    ' Inc/Dec -1 +1 -1 +1
    ' rngTemp +1 -1 +1 -1
    '
    ' Regular directions (Top, Bottom, Left, Right) are also similar
    ' in structure (in context of evaluating the code below).
    ' You'll see a pattern with Top and Left and Bottom and Right.
    '
    ' TOP LEFT BOTTOM RIGHT
    ' Need Top - 1 Top Top
    ' Lft Lft - 1 Lft
    ' Rgt Rgt Rgt + 1
    ' Bot Bot + 1
    '
    ' Exit Do Top=0 Lft=0 BotMax RgtMax
    '
    ' rngEval [Need] [Need] [Need] [Need]
    '
    ' lOffset .Cells(1).Row .Cells(1).Column (Bot - 1) - .Cells( (Rgt - 1) - .Cells(
    ' - (Top + 1) - (Lft + 1) .Cells.Count).Row .Cells.Count).Column
    '
    ' Resize .Offset(-lOffset) .Offset(,-lOffset) .Resize(.Rows .Resize(,.Columns
    ' .Resize(.Rows .Resize(,.Columns .Count + lOffset) .Count + lOffset)
    ' .Count + lOffset) .Count + lOffset)
    '---------------------------------------------------------------------
    Dim lngRowMax As Long
    Dim intColMax As Integer
    Dim lngRowTop As Long
    Dim lngRowBot As Long
    Dim lngOffset As Long
    Dim intOffset As Integer
    Dim intColLft As Integer
    Dim intColRgt As Integer
    Dim Wks As Worksheet
    Dim rngTemp As Range
    Dim rngResize As Range
    Dim rngEval As Range
    Dim rngOuter As Range
    Dim blnAdd As Boolean
    'get a worksheet object reference to Rng (for use in With blocks)
    Set Wks = Rng.Parent
    'set the range to be "resized" as part of the CurrentRegion process
    ' NOTE: this is the first cell in Rng and not something else (such as the
    ' ActiveCell within Rng)
    Set rngResize = Rng(1)
    'get the max rows and columns of the worksheet, i.e. get the worksheet bounds
    ' (which creates an interesting dynamic due to the fact that there is no
    ' Cells(0,0) in the worksheet)
    With Wks
    lngRowMax = .Rows.Count
    intColMax = .Columns.Count
    End With
    'get the row and column numbers for the cell
    With rngResize
    With .Cells(1)
    lngRowTop = .Row
    intColLft = .Column
    End With

    With .Cells(.Cells.Count)
    lngRowBot = .Row
    intColRgt = .Column
    End With
    End With
    'get the "outer region," i.e. one row above, one row below,
    ' one column left, and one column right
    If lngRowTop 1 Then lngRowTop = lngRowTop - 1
    If intColLft 1 Then intColLft = intColLft - 1
    If lngRowBot lngRowMax Then lngRowBot = lngRowBot + 1
    If intColRgt intColMax Then intColRgt = intColRgt + 1
    'return a Range Object that corresponds to the "outer region"
    Set rngOuter = GetOuterRange(rngResize)
    'Debug.Print "OuterRange:"; rngOuter.Address
    'loop until you can't find anything in the outer region's cells
    Do Until Application.WorksheetFunction.CountA(rngOuter) = 0
    '--------------------------------------------------------------------
    'GENERAL COMMENTS:
    ' The code below will "move" in the stated direction, looking
    ' for additional data contained in that direction. If data is
    ' found, then the range is expanded and the process "restarts"
    ' its loop. The diagonal directions are structured slightly
    ' different (meaning a range object--rngTemp--is being used)
    ' than the non-diagonal directions.
    '
    ' Though the code below is not explicitly commented, note the
    ' +1 or -1 to .Row and/or .Column. Also, blnAdd is used as a flag
    ' to determine whether the range needs to be resized to include
    ' new data found in the specified search direction.
    '--------------------------------------------------------------------
    '------------------------------------------------------
    'upper left direction
    Set rngTemp = rngResize
    blnAdd = False
    With rngResize
    With .Cells(1)
    lngRowTop = .Row - 1
    intColLft = .Column - 1
    End With
    End With

    'continue to search in the specified direction for data to
    ' be added. If data needs to be added, then blnAdd will
    ' be set to True and the range will be resized later.
    Do
    If lngRowTop = 0 Or intColLft = 0 Then Exit Do
    If IsEmpty(Wks.Cells(lngRowTop, intColLft)) Then Exit Do

    blnAdd = True
    lngRowTop = lngRowTop - 1
    intColLft = intColLft - 1
    Loop

    If blnAdd Then
    With Wks
    Set rngTemp = .Cells(lngRowTop + 1, intColLft + 1)
    End With
    Set rngResize = Range(rngResize, rngTemp)
    'Debug.Print "UpLft:"; rngResize.Address
    End If
    '------------------------------------------------------

    '------------------------------------------------------
    'upper right direction
    Set rngTemp = rngResize
    blnAdd = False
    With rngResize
    lngRowTop = .Cells(1).Row - 1
    With .Cells(.Cells.Count)
    intColRgt = .Column + 1
    End With
    End With

    Do
    If lngRowTop = 0 Or intColRgt intColMax Then Exit Do
    If IsEmpty(Wks.Cells(lngRowTop, intColRgt)) Then Exit Do

    blnAdd = True
    lngRowTop = lngRowTop - 1
    intColRgt = intColRgt + 1
    Loop

    If blnAdd Then
    With Wks
    Set rngTemp = .Cells(lngRowTop + 1, intColRgt - 1)
    'Debug.Print " Tmp:"; rngTemp.Address
    End With
    Set rngResize = Range(rngResize, rngTemp)
    'Debug.Print "UpRgt:"; rngResize.Address
    End If
    '------------------------------------------------------

    '------------------------------------------------------
    'bottom right direction
    Set rngTemp = rngResize
    blnAdd = False
    With rngResize
    With .Cells(.Cells.Count)
    lngRowBot = .Row + 1
    intColRgt = .Column + 1
    End With
    End With

    Do
    If lngRowBot lngRowMax Or intColRgt intColMax Then Exit Do
    If IsEmpty(Wks.Cells(lngRowBot, intColRgt)) Then Exit Do

    blnAdd = True
    lngRowBot = lngRowBot + 1
    intColRgt = intColRgt + 1
    Loop

    If blnAdd Then
    With Wks
    Set rngTemp = .Cells(lngRowBot - 1, intColRgt - 1)
    'Debug.Print " Tmp:"; rngTemp.Address
    End With
    Set rngResize = Range(rngResize, rngTemp)
    'Debug.Print "DwRgt:"; rngResize.Address
    End If
    '------------------------------------------------------
    '------------------------------------------------------
    'bottom left direction
    Set rngTemp = rngResize
    blnAdd = False
    With rngResize
    With .Cells(.Cells.Count)
    lngRowBot = .Row + 1
    End With
    intColLft = .Cells(1).Column - 1
    End With

    Do
    If lngRowBot lngRowMax Or intColLft = 0 Then Exit Do
    If IsEmpty(Wks.Cells(lngRowBot, intColLft)) Then Exit Do

    blnAdd = True
    lngRowBot = lngRowBot + 1
    intColLft = intColLft - 1
    Loop

    If blnAdd Then
    With Wks
    Set rngTemp = .Cells(lngRowBot - 1, intColLft + 1)
    'Debug.Print " Tmp:"; rngTemp.Address
    End With
    Set rngResize = Range(rngResize, rngTemp)
    'Debug.Print "DwLft:"; rngResize.Address
    End If
    '------------------------------------------------------

    '------------------------------------------------------
    'top direction
    blnAdd = False
    With rngResize
    With .Cells(1)
    lngRowTop = .Row - 1
    intColLft = .Column
    End With

    With .Cells(.Cells.Count)
    lngRowBot = .Row
    intColRgt = .Column
    End With
    End With

    Do
    If lngRowTop = 0 Then Exit Do

    With Wks
    Set rngEval = .Range(.Cells(lngRowTop, intColLft), _
    .Cells(lngRowTop, intColRgt))
    End With

    If Application.WorksheetFunction.CountA(rngEval) = 0 Then Exit Do

    blnAdd = True
    lngRowTop = lngRowTop - 1
    Loop

    If blnAdd Then
    With rngResize
    lngOffset = .Cells(1).Row - (lngRowTop + 1)
    Set rngResize = .Offset(-lngOffset).Resize(.Rows.Count + lngOffset)
    End With
    'Debug.Print " Top:"; rngResize.Address
    End If
    '------------------------------------------------------

    '------------------------------------------------------
    'right direction
    blnAdd = False
    With rngResize
    With .Cells(1)
    lngRowTop = .Row
    intColLft = .Column
    End With

    With .Cells(.Cells.Count)
    lngRowBot = .Row
    intColRgt = .Column + 1
    End With
    End With

    Do
    If intColRgt intColMax Then Exit Do
    'If lngRowTop = 0 Then lngRowTop = 1

    With Wks
    Set rngEval = .Range(.Cells(lngRowTop, intColRgt), _
    .Cells(lngRowBot, intColRgt))
    End With

    If Application.WorksheetFunction.CountA(rngEval) = 0 Then Exit Do

    blnAdd = True
    intColRgt = intColRgt + 1
    Loop

    If blnAdd Then
    With rngResize
    intOffset = (intColRgt - 1) - .Cells(.Cells.Count).Column
    Set rngResize = .Resize(, .Columns.Count + intOffset)
    End With
    'Debug.Print " Rgt:"; rngResize.Address
    End If
    '------------------------------------------------------

    '------------------------------------------------------
    'bottom direction
    blnAdd = False
    With rngResize
    With .Cells(1)
    lngRowTop = .Row
    intColLft = .Column
    End With

    With .Cells(.Cells.Count)
    lngRowBot = .Row + 1
    intColRgt = .Column
    End With
    End With

    Do
    If lngRowBot lngRowMax Then Exit Do

    With Wks
    Set rngEval = .Range(.Cells(lngRowBot, intColLft), _
    .Cells(lngRowBot, intColRgt))
    End With

    If Application.WorksheetFunction.CountA(rngEval) = 0 Then Exit Do

    blnAdd = True
    lngRowBot = lngRowBot + 1
    Loop

    If blnAdd Then
    With rngResize
    lngOffset = (lngRowBot - 1) - .Cells(.Cells.Count).Row
    Set rngResize = .Resize(.Rows.Count + lngOffset)
    End With
    'Debug.Print " Bot:"; rngResize.Address
    End If
    '------------------------------------------------------

    '------------------------------------------------------
    'left direction
    blnAdd = False
    With rngResize
    With .Cells(1)
    lngRowTop = .Row
    intColLft = .Column - 1
    End With

    With .Cells(.Cells.Count)
    lngRowBot = .Row
    intColRgt = .Column
    End With
    End With

    Do
    If intColLft = 0 Then Exit Do

    With Wks
    Set rngEval = .Range(.Cells(lngRowTop, intColLft), _
    .Cells(lngRowBot, intColLft))
    End With

    If Application.WorksheetFunction.CountA(rngEval) = 0 Then Exit Do

    blnAdd = True
    intColLft = intColLft - 1
    Loop

    If blnAdd Then
    With rngResize
    intOffset = .Cells(1).Column - (intColLft + 1)
    Set rngResize = .Offset(, -intOffset).Resize(, .Columns.Count + intOffset)
    End With
    'Debug.Print " Lft:"; rngResize.Address
    End If
    '------------------------------------------------------
    '------------------------------------------------------
    'create outer range for the test in the Do Loop

    Set rngOuter = GetOuterRange(rngResize)
    'Debug.Print "OuterRange:"; rngOuter.Address
    '------------------------------------------------------
    Loop '-- Do Until Application.WorksheetFunction.CountA(rngOuter) = 0
    If Application.WorksheetFunc1tion.CountA(rngOuter) = 0 Then
    Set CustomCurrentRegion = rngResize
    Else
    Set CustomCurrentRegion = Nothing
    End If
    'explicitly clear the memory
    Set Wks = Nothing
    Set rngTemp = Nothing
    Set rngResize = Nothing
    Set rngEval = Nothing
    Set rngOuter = Nothing
    End Function
    Private Function GetOuterRange(Rng As Range) As Range
    '---------------------------------------------------------------------
    'INFO: 06/15/2010, Matthew Herbert
    '---------------------------------------------------------------------
    'PURPOSE: Return the outer range of Rng. If Rng is C3:D4 then the
    ' outer range is B2:E2 (Top), E2:E5 (Right), B5:E5 (Bottom),
    ' and B2:B5 (Left).
    '
    'Rng [in] A reference range from which the outer region's range
    ' will be built.
    '
    'RETURNS: Nothing - This result should NEVER occur, though it's
    ' coded for a "just in case" scenario.
    ' Range Object - The Range of the outer region
    '---------------------------------------------------------------------
    'UTILIZES: N/A
    '---------------------------------------------------------------------
    'NOTES: None
    '---------------------------------------------------------------------
    Dim lngRowMax As Long
    Dim intColMax As Integer
    Dim lngRowTop As Long
    Dim lngRowBot As Long
    Dim intColLft As Integer
    Dim intColRgt As Integer
    Dim lngRowT As Long
    Dim lngRowB As Long
    Dim intColL As Integer
    Dim intColR As Integer
    Dim lngCnt As Long
    Dim rngTop As Range
    Dim rngBot As Range
    Dim rngLft As Range
    Dim rngRgt As Range
    Dim rngRes As Range
    Dim rngArr(3) As Range
    Dim Wks As Worksheet
    'get a worksheet object reference to Rng (for use in With blocks)
    Set Wks = Rng.Parent
    'get the max rows and columns of the worksheet, i.e. get the worksheet bounds
    With Wks
    lngRowMax = .Rows.Count
    intColMax = .Columns.Count
    End With
    'get the row and column numbers for Rng
    With Rng
    With .Cells(1)
    lngRowTop = .Row - 1
    intColLft = .Column - 1
    End With

    With .Cells(.Cells.Count)
    lngRowBot = .Row + 1
    intColRgt = .Column + 1
    End With
    End With
    'check if the values are "out of bounds" and reset the variable appropriately
    If lngRowTop = 0 Then
    lngRowT = 1
    Else
    lngRowT = lngRowTop
    End If
    If lngRowBot lngRowMax Then
    lngRowB = lngRowMax
    Else
    lngRowB = lngRowBot
    End If
    If intColLft = 0 Then
    intColL = 1
    Else
    intColL = intColLft
    End If
    If intColRgt intColMax Then
    intColR = intColMax
    Else
    intColR = intColRgt
    End If
    'set up the Top, Bottom, Left, and Right ranges based on whether the bounds
    ' are in or out of the worksheet. Store the ranges in an array, which
    ' will be combined via Union later. If a range is Nothing, then we'll ignore
    ' it when we loop through the array.
    'don't look too lightly at the .Range(.Cells(),.Cells()) portion below b/c
    ' you have to "keep your ducks in a row"
    With Wks
    If lngRowTop 0 Then
    Set rngTop = .Range(.Cells(lngRowT, intColL), .Cells(lngRowT, intColR))
    Set rngArr(0) = rngTop
    End If

    If lngRowBot = lngRowMax Then
    Set rngBot = .Range(.Cells(lngRowB, intColL), .Cells(lngRowB, intColR))
    Set rngArr(1) = rngBot
    End If

    If intColLft 0 Then
    Set rngLft = .Range(.Cells(lngRowT, intColL), .Cells(lngRowB, intColL))
    Set rngArr(2) = rngLft
    End If

    If intColRgt = intColMax Then
    Set rngRgt = .Range(.Cells(lngRowT, intColR), .Cells(lngRowB, intColR))
    Set rngArr(3) = rngRgt
    End If
    End With
    'loop through the array of ranges and add up the non-Nothing ranges
    For lngCnt = LBound(rngArr) To UBound(rngArr)
    If Not rngArr(lngCnt) Is Nothing Then
    If rngRes Is Nothing Then
    Set rngRes = rngArr(lngCnt)
    Else
    Set rngRes = Union(rngRes, rngArr(lngCnt))
    End If
    End If
    Next lngCnt
    'return the result
    If rngRes Is Nothing Then
    Set GetOuterRange = Nothing
    Else
    Set GetOuterRange = rngRes
    End If
    'explicitly clear the memory
    Set rngTop = Nothing
    Set rngBot = Nothing
    Set rngLft = Nothing
    Set rngRgt = Nothing
    Set rngRes = Nothing
    Set rngArr(0) = Nothing
    Set rngArr(1) = Nothing
    Set rngArr(2) = Nothing
    Set rngArr(3) = Nothing
    Set Wks = Nothing
    End Function

    DB:2.81:Function Call Substitute For Currentregion 11

    Peter,
    Thanks for the response. I had done the same; however, I stripped out the IIf and used a traditional If Then statement. I remember reading somewhere that the traditional If Then is sligthly faster (in terms of optimization time) because of the True and
    False evaluation in the IIf.
    Thanks,
    Matthew Herbert

  • RELEVANCY SCORE 2.81

    DB:2.81:Re: How To Find Duplicate Records In A Table f3


    can u please expain count(1)1

    DB:2.81:Re: How To Find Duplicate Records In A Table f3

    Hi,

    Assuming that you are connecting ( APEX and sql*plus) to the same DB version the query will run in both.
    The only time it won't run is if APEX and sql*plus connect to different versions and you use commands that are version specific
    like regexp_count for 11g, other regular expressions for 10g/11g.

    In your case chr(39) is equal to apostrophe (')

    here are examples on its use

    SQL with t as -- "sample data"
    2 (select 'd''silva' name_ from dual union all
    3 select 'john' name_ from dual union all
    4 select 'd''abo' name_ from dual union all
    5 select 'a''abo' name_ from dual
    6 ) -- "end sample data"
    7 select name_
    8 from t
    9 where name_ like 'd'||chr(39)||'%' -- equal to look for rows that start with d and an apostrophe (d')
    10 /

    NAME_
    -------
    d'silva
    d'abo

    SQL
    SQL with t as -- "sample data"
    2 (select 'd''silva' name_ from dual union all
    3 select 'john' name_ from dual union all
    4 select 'd''abo' name_ from dual union all
    5 select 'a''abo' name_ from dual
    6 ) -- "end sample data"
    7 select name_
    8 from t
    9 where name_ like '%'||chr(39)||'%' -- equal to look for rows that has an apostrophe (') anywhere in the string
    10 /

    NAME_
    -------
    d'silva
    d'abo
    a'abo

    SQL
    SQL with t as -- "sample data"
    2 (select 'd''silva' name_ from dual union all
    3 select 'john' name_ from dual union all
    4 select 'd''abo' name_ from dual union all
    5 select 'xx''abo' name_ from dual union all
    6 select 'a''abo' name_ from dual
    7 ) -- "end sample data"
    8 select name_
    9 from t
    10 where name_ like '_'||chr(39)||'%' -- equal to look for rows where 2nd character is an apostrophe (_')
    11 /

    NAME_
    -------
    d'silva
    d'abo
    a'abo

  • RELEVANCY SCORE 2.81

    DB:2.81:Global Search For Duplicate Entries In Workbook km


    I have tried COUNT IF but it only searched one worksheet at a time; I do not see how PIVOT TABLE applies to my search...

    DB:2.81:Global Search For Duplicate Entries In Workbook km

    Use the COUNTIF.3D functionwhich s part of the morefunc.xll add-in which you need to download install.

    =COUNTIF.3D(Sheet1:Sheet4!A1,"Z")

    Get it from http://xcell05.free.fr/english/index.html
    OR are you open to a VBA solution? If so, tell us more details.

    Bernard Liengme, Nova Scotia, Canada http://people.stfx.ca/bliengme/

  • RELEVANCY SCORE 2.80

    DB:2.80:Inactive Cells fz


    Hello,
    I'm trying to perform some very basic functions (count, sumif, etc...) but the formulas aren't working. I've checked that the calculations are set to automatic and the worksheet isn't protected or locked. The cells are all formatted to General, but the problem
    seems to lie in the formatting. If I go into a cell and press F2 then enter, the cell is recognized by my formulas. Since the spreadheets have tens of thousands of rows and dozens of columns, doing this for each cell is out of the question. Could someone
    point me to a more appropriate solution that would "activate" all the cells at once? I would really appreciate it.

    Thanks
    Hans

    DB:2.80:Inactive Cells fz

    Thanks a lot, in the mean time I found a solution that worked but I followed your recommendations also. Anyhow, thanks for the help!
    Cheers

  • RELEVANCY SCORE 2.80

    DB:2.80:Code To Copy Cells In 55 Worksheets Into Summary Page pd


    I have 55 exact worksheet copies that I would like to extract information from 2 columns: D and Eand make a summary page. The columns would be D2:D97 and E2:E97. The summary page wouldend up being 110 columns(55x2) in which I could compare all the worksheet
    data on one worksheet. Can anyone please help me? I have read through a lot of the posts and can't find something that would work. Thank you in advance!

    DB:2.80:Code To Copy Cells In 55 Worksheets Into Summary Page pd

    Mr Ogilvy,
    You helped me out earlier today with this question about copying a range out of 55 worksheets. Someone came back to me and asked if I could bring back a column c2:c97 which is text describing the rows of data. How could the code you supplied bring back
    this one column so that it could be used to identify what data is in each row? I tried to modify the code you supplied but only want one instance of this to be on the summary page ....

  • RELEVANCY SCORE 2.80

    DB:2.80:I Need To Create An Exact Duplicate Of An Excel Worksheet In The Same Workbook. aa


    Any points in the right direction or suggestions would be appreciated. Still trying to find my way through programming with excel.

    DB:2.80:I Need To Create An Exact Duplicate Of An Excel Worksheet In The Same Workbook. aa

    A very powerfull tool to work with ActiveX in Microsoft applications is the Record Macro tool. Try this: Open MS Excel at worksheet you want to copy, go to the menu "Tools" select "Macros" and then "Record New Macro...". Make a copy of the worksheet and every thing you want to do with the workbook and stop recording the macro. Again select "ToolsMacrosMacro..." select the macro you've created and choose to "Edit". The Excel will open the Visual Basic editor and show you the code you've recorded. This code has the same codes you need to implement at your vi activeX Server to execute your job.If that doesn't work let me know ok?

  • RELEVANCY SCORE 2.78

    DB:2.78:In Excell Worksheet I Find Certain Cells Are Displaying Autoshape Signs - How To Remove Those sm


    In the Spreadsheet I have prepared one table showing results by inserting formulas there. The original datadownloadedand pasted in idjacent cells. When the results are copied down in in next row below, the copying takes long time to get done. Moreover,
    the total bytes of the Spreadsheet is showing abnormal number as utilised. My question is - how to free my spreadsheet from reappearing again of those Autoshape cells ? How to remove those cells from the affected cells of the Table thus made ? Please suggest.

    DB:2.78:In Excell Worksheet I Find Certain Cells Are Displaying Autoshape Signs - How To Remove Those sm

    sorry Wrong forum ...

    If this proposed solution has resolved your issue(s), please mark it as Propose as Answer to let others know the solution that worked for you
    Please click on the "Vote as Helpful" button!
    http://office.microsoft.com/en-us/outlook-help/learn-where-menu-and-toolbar-commands-are-in-office-2010-HA101794130.aspx - Silverlight applets Mapping 2003 to 2010 commands for all Office apps.
    http://office.microsoft.com/en-us/training/HA102295841033.aspx - flash applets mapping Office 2003 menus to 2007 ribbon.

  • RELEVANCY SCORE 2.77

    DB:2.77:Create Excel File Always Results In Unreadable Content Error On Open 78


    Greetings,
    I am creating Excel 2007 files using the SpreadsheetDocument (Do cumentFormat namespace) in the Open XML Format SDK 2.0, v2.0.5022.0. My code below compiles and creates the excel file, and the validator returns no errors. Yet opening the document
    always yields the Excel found unreadable content in 'file.xlsx'. Do you want to recover the contents of the file? Recovery yields a perfectly fine looking document, with the recovery complete dialog indicating cells were repaired, but no details.
    I also tried creating the cells with a type of InlineText, but that seemed to make the formatting of the recovered spreadsheet worse. Any help/clues would be appreciated.
    // create the workbook
    spreadSheet.AddWorkbookPart();
    spreadSheet.WorkbookPart.Workbook = new Workbook();
    // create the worksheet
    spreadSheet.WorkbookPart.AddNewPartWorksheetPart();
    spreadSheet.WorkbookPart.WorksheetParts.FirstWorksheetPart().Worksheet = new Worksheet();
    // create sheet data
    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
    Row contentRow = new Row();
    contentRow.AppendChild(new Cell() { CellValue = new CellValue(Header 1)});

    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(contentRow);
    contentRow = new Row();
    contentRow.AppendChild(new Cell() { CellValue = new CellValue(TextValue)});
    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(contentRow);
    // save worksheet
    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
    // create the worksheet to workbook relation
    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
    spreadSheet.WorkbookPart.Workbook.GetFirstChildSheets().AppendChild(new Sheet()
    {
    Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
    SheetId = 1,
    Name = Sheet 1
    });
    spreadSheet.WorkbookPart.Workbook.Save();
    spreadSheet.Close();

    // Validator:
    try
    {
    OpenXmlValidator validator = new OpenXmlValidator();
    int count = 0;
    foreach (ValidationErrorInfo error in
    validator.Validate(
    SpreadsheetDocument.Open(d:\\users\\user1\\desktop\\test.xlsx, true)))
    {
    count;
    MessageBox.Show(Error count \n
    Description: error.Description \n
    Path: error.Path.XPath \n
    Part: error.Part.Uri \n
    -------------------------------------------);
    }
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);
    }

    DB:2.77:Create Excel File Always Results In Unreadable Content Error On Open 78

    Hi bantar,
    Thanks for your question.
    From your shared code, it seems that you are inserting a string value into a cell, which is different from inserting a numeric value. There should be a shared string tablecontaining all the string values and the cell value only stores the index of
    the string in shared string table, for more detailed information, you could refer to
    How to: Insert Text into a Cell in a Spreadsheet Document on MSDN. In addition, there are many other resources such as
    How to: Create a Spreadsheet Document by Providing a Filenamewhich you could refer to.
    To figure out what is the unreadable data, you could try to use the Productivity Tool's Compare Files to see the differences between
    the files with/without repair.
    Hope this helps. If you have any question, please let me know.
    Thanks,
    Lu

  • RELEVANCY SCORE 2.77

    DB:2.77:Copy A Data Row From Sql Datagrid To Excel Name Range cm


    What I'm trying to do is double click a row in a data gridand insert that row into name ranged cells on my worksheet. I've searched the forums and can't find any post about this. Using VSTO and Excel 2010

    DB:2.77:Copy A Data Row From Sql Datagrid To Excel Name Range cm

    I found this snippet that works for me.
    Dim row As NorthwindDataSet.CustomersRow
    row = CType(CType(Me.CustomersBindingSource.Current, DataRowView).Row, NorthwindDataSet.CustomersRow)
    here is what I used to get the current row to sheet1.

    Dim row As CustomerSelectionDataSet.CustomersRow
    row = CType(CType(Me.CustomersBindingSource1.Current, DataRowView).Row, CustomerSelectionDataSet.CustomersRow)
    Dim Message As String = You Cannot Select a Blank Row

    Try
    Globals.Sheet1.GBMWhoId.Value2 = row.CustomerID
    Globals.Sheet1.GBMWhoCompany.Value2 = row.CompanyName
    Globals.Sheet1.GBMWhoFirstName.Value2 = row.FirstName
    Globals.Sheet1.GBMWhoLastName.Value2 = row.LastName
    Globals.Sheet1.GBMWhoAddress.Value2 = row.Address
    Globals.Sheet1.GBMWhoCityStZip.Value2 = (row.CityState , row.PostalZipCode)
    Globals.Sheet1.GBMWhoPhone.Value2 = row.Phone
    Globals.Sheet1.GBMWhoEMail.Value2 = row.EMailAddress

    Me.Close()
    Catch ex As Exception
    MsgBox(Message vbCrLf ex.ToString)
    End Try

  • RELEVANCY SCORE 2.77

    DB:2.77:Find Duplicate Record Using Where Clause kx


    Hi All;
    I need to find the duplicate records using where clause
    Below is my query

    Any help much appreciated
    Thanks
    select count(*) as duplicatenumber,new_applicantidname ,new_applicantid,new_hpwwardcodefrom FilteredNew_applicationformwhere statecodename ='active'group by new_applicantidname ,new_applicantid,new_hpwwardcodehaving count(new_applicantid) 1

    Pradnya07

    DB:2.77:Find Duplicate Record Using Where Clause kx

    Use this minor change:
    DECLARE @Foo TABLE
    (
    ApplicationID INT,
    Name VARCHAR(10),
    WardName VARCHAR(10),
    WardCode INT
    )

    INSERT INTO @Foo VALUES
    (1, 'ABC', 'Preston', 123),
    (1, 'ABC', 'Wigen', 154),
    (2, 'XYZ', 'Leeds', 321),
    (2, 'XYZ', 'Leeds', 321),
    (3, 'QWE', 'London', 778),
    (3, 'QWE', 'Manchester', 258),
    (4, 'ASH', 'Bangalore', 890);

    -- For Unique Duplicate Records
    SELECT T.* FROM
    (
    SELECT
    *, COUNT(ApplicationID) OVER(PARTITION BY ApplicationID, Name, WardName, WardCode) UniqueCount,
    COUNT(ApplicationId) over (PARTITION BY ApplicationID, Name) as DupCount
    FROM @Foo
    ) AS T
    WHERE T.UniqueCount = 1 AND DupCount 1

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

  • RELEVANCY SCORE 2.77

    DB:2.77:How To Get Data From An Umerged Call To Merged Cells And Drag It Through The Whole Wrksheet j3


    Two Worksheets
    Worksheet One: Not Merged Cells (containing Text)
    Woksheet Two: Merged Cells (requiring information from Worksheet one unmerged cells)

    when draging the cells in worksheet two it skips the cells in workseet one
    Example
    worksheet two cells (A498 to A 505) merged as one=PROPER('Members Details'!A64)
    worksheet two cells (A505 to A 513) merged as one=PROPER('Members Details'!A72) when dragged

    any solution please

  • RELEVANCY SCORE 2.77

    DB:2.77:Linking Non Consecutive Cells On One Worksheet To Consecutive Cells On Another Worksheet 81


    Hello,

    DB:2.77:Linking Non Consecutive Cells On One Worksheet To Consecutive Cells On Another Worksheet 81

    I have found a solution unrelated to this question. The question no longer needs to be answered, thanks.

  • RELEVANCY SCORE 2.77

    DB:2.77:Chart Object And Multi-Area Range j3


    Hello,
    In the Excel worksheet I tries to build a chart that will include multiple cells, that havent shared borders.
    Actually my question is how to invoke method SetSource with Range that consisted of several non-adjacent cells to each other.
    Some clarification I find in MSDN (How to: Refer to Multiple Ranges):
    http://msdn.microsoft.com/en-us/library/bb211408
    But this code always fails with Error: Cannot find an overload for 'Union' and the argument count: '2 '
    Perhaps something wrong in my code.
    Here is my PowerShell 2.0 code, runs on Windows 7 32-bit and Excel 2010:
    $newchart = $sheet2.Shapes.AddChart().chart
    $newchart.ChartType = $xlColumnClustered
    $newchart.ChartStyle = 5
    $newchart.HasTitle = $true
    # error in the next row
    $bigRange = $workbook.Parent.Union($sheet1.cells.item($j,$i),$sheet1.cells.item($j,$i13),$null)
    $newchart.SetSourceData($bigRange)
    $newchart.SeriesCollection(1).XValues = $newsheet.cells.item(3,1)
    $newchart.SeriesCollection(1).Name = $sheet1.cells.item($j,1).Text
    $newchart.ChartTitle.Text = $sheet1.cells.item(2,$i).Text
    $newchart.ChartTitle.Font.Size = 10
    $newchart.ChartTitle.Font.Bold = $false

    Thanks in advance for any ideas,
    Anton

    DB:2.77:Chart Object And Multi-Area Range j3

    Andy,
    For me this code works if I substitute the rows marked bold with this one:
    $newchart.SetSourceData($sheet1.cells.item($j,$i))
    Now I'm going to try with Visual Studio and return with results.
    Thanks,
    Anton

  • RELEVANCY SCORE 2.76

    DB:2.76:Count Function Not Recognizing Date Range 71


    I am trying to count the number of cells that contain a date within a given range on a separate worksheet:

    =COUNT((Worksheet!$E$2:$E$11=DATE(2011,1,1))*(Worksheet!$E$2:$E$11=DATE(2011,1,31)))

    The current result is 1, but I know Ihave 4 cells that contain a January date. Is there something wrong with my syntax or do the formula's date formats have to match what's in the cells?

    DB:2.76:Count Function Not Recognizing Date Range 71

    Hi,

    Try this

    =countif(Worksheet!$E$2:$E$11,"="DATE(2011,1,1))-countif(Worksheet!$E$2:$E$11,""DATE(2011,1,31))

    Hope this helps.

  • RELEVANCY SCORE 2.75

    DB:2.75:Sparse Matrix Performance Issue j1


    Anyone have a feel for the difference in performance in gathering all non-empty cells into an array by iterating the entire UsedRange of a worksheet cell by cell vs. using Find and FindNext in a loop ?
    In other words, what cell count would be required to favor the latter approach ?

    DB:2.75:Sparse Matrix Performance Issue j1

    Thanks Charles. Great research work.
    This does not tell me exactly where the density must be for a trade-off, but it appears that coverting the range to an array is one of the fastest alternatives.

  • RELEVANCY SCORE 2.75

    DB:2.75:I Have A Worksheet With Over10.000 Rows And Multilple Merged Cells. To Sort I Need To Find And Unmverge The Cells. Is There A Way To Do It With A Macre 73


    the merged cells are columns C and D. They are located sporadically throughout the worksheet. I receive updates from another source and will need to perform this task repeatedly. Thanks for any assiatance

    DB:2.75:I Have A Worksheet With Over10.000 Rows And Multilple Merged Cells. To Sort I Need To Find And Unmverge The Cells. Is There A Way To Do It With A Macre 73

    The macro to do it would look like this:
    Sub UnmergeAllInCandD()
    With Range("C:D")
    .MergeCells = False
    End With
    End Sub

    But you can do it pretty easily from the keyboard by selecting columns C and D and then going to the Cells group on the Home tab and choosing Format and then the Cells option, and then the "Format Cells..." option which brings up a format cells dialog, choose
    the [Alignment] tab in it and clear the "Merge Cells" checkbox.

  • RELEVANCY SCORE 2.74

    DB:2.74:Adding Details To Cells Based On A Condition f8


    Adding details to cells based on a condition
    What I want to do is look for the cell that contains Grand Count. Once detected, for the cell below Grand Count,
    Insert the following details, see the image below:
    http://www.iimmgg.com/image/1a57a3ce2e961d6e3dd267fc1583e30c

    Important: If you look at the image above we can see that Grand Count is located at A988, but it is not always the case. Grand Count can be located at any row in Column A.
    I have attached a sample workbook which contains
    Sheet 1, shows how the initial worksheet looks like
    sheet 2, shows how the final outcome should look like
    sheet 3, contains a button where the codes should be inserted
    http://www.mediafire.com/?z67nsp2eak91t84

  • RELEVANCY SCORE 2.74

    DB:2.74:How To Reference A Group Of Cells In Another Worksheet kk


    How can I reference a group of cell from one worksheet in another worksheet within the same workbook that will automatically update when changes are made. I have inventory list for a retail outlet with different departments on each worksheet already created.
    The worksheets have numerous other columns containing all of the internal data to calculate inventory levels, case costs, case counts, unit costs, retail price, cost of goods percentages, and total invnetory values. All of this information is not necessary
    when doing an actual hand count. I created these inventory count sheets without the other internal information and it proved to make things easier and more efficient for the laboror performing the physical count task. But it was time consuming copying and
    pasting and formating the inventory count sheet, so I am hoping that someone can help me simplify this process so that the Count Sheet will automatically update when we update the Inventory Lists Worksheets by adding new items or deleting other items that
    we no longer stock.

    Thanks for any and all suggestions or solutions that you might have. I greatly appreciate it.

    Anthony..

    DB:2.74:How To Reference A Group Of Cells In Another Worksheet kk

    If the source is in Z100 on Sheet1, then in another sheet:
    =Sheet1!Z100

    which should adjust if rows/columns are added

  • RELEVANCY SCORE 2.74

    DB:2.74:Excel.Exe Process Does Not Stop Once Opened In Vc++ cz


    Hello,
    I am opening an excel sheet, in the background from a windows forms application, and taking some data out of the sheet and filling an array. This is all done when there is a selection made in a combo box. I have tried to close excel at the end of the function,
    however the excel.exe process remains active.

    Please see the code below:

    //Create a new Excel application with 3-sheet workbook
    Microsoft::Office::Interop::Excel::Application^ exAppCal =
    gcnew Microsoft::Office::Interop::Excel::ApplicationClass();

    //Cal sheet is protected with password with read only option, open sheet as read only
    Workbook^ exWbCal = exAppCal-Workbooks-Open(...Product.xls, //Filename
    Type::Missing, //Update Links
    true, //Read Only
    Type::Missing, //Format
    Type::Missing, //Password
    Type::Missing, //WriteResPassword
    Type::Missing, //IgnoreReadOnlyRecommended
    Type::Missing, //Origin
    Type::Missing, //Delimiter
    Type::Missing, //Editable
    Type::Missing, //Notify
    Type::Missing, //Converter
    Type::Missing, //AddToMru
    Type::Missing, //Local
    Type::Missing); //CorruptLoad

    //Create a variable for the active Worksheetss tracking handle
    //first Worksheet is the default active one)
    Worksheet^ exWsCal = safe_castWorksheet^(exAppCal-ActiveSheet);

    //Initialises loop counter
    i = 1;

    for each(Worksheet^ ws in exWbCal-Worksheets)
    {
    sheetName = ws-Name;

    //check if there is a sheet for the selected product type and set it as the current sheet
    if(String::Equals(sheetName, cmbProductType-Text))
    {
    //Make matching sheet the active sheet
    safe_castWorksheet^(exAppCal-ActiveWorkbook-Sheets-Item[i])-Select(Type::Missing);

    exWsCal = safe_castWorksheet^(exAppCal-ActiveSheet);

    break;
    }

    i++;
    }

    //Product letter is stored in top left cell A1 of each sheet
    calProdType = ((safe_castRange^(exWsCal-Cells[1, 1]))-Cells-Value2)-ToString();

    rnCal = exWsCal-Range[B5, Type::Missing];

    //Find last data position in excel sheet
    rnCal = rnCal-End[XlDirection::xlDown];

    //total number of rows
    int rows = rnCal-Row;

    //create a new array large enough to store all product names
    calDatabase = gcnew arrayString^, 2(rows - databaseTitle + 1, arrayColumns);

    //Step through each row in the calibration sheet and add it to the calDatabase
    //if there is a Y in check serial number
    for(i = 5; i = rows; i++)
    {
    if(String::Equals(((safe_castRange^(exWsCal-Cells[i, 1]))-Cells-Value2)-ToString(), Y))
    {
    calDatabase[i - databaseTitle, 0] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colProdCode]))-Cells-Value2)-ToString();
    calDatabase[i - databaseTitle, 1] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colProdName]))-Cells-Value2)-ToString();
    calDatabase[i - databaseTitle, 2] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colOrderCode]))-Cells-Value2)-ToString();
    calDatabase[i - databaseTitle, 3] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colLabProdName]))-Cells-Value2)-ToString();
    calDatabase[i - databaseTitle, 4] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colLabFilename]))-Cells-Value2)-ToString();
    calDatabase[i - databaseTitle, 5] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colCheckCalCert]))-Cells-Value2)-ToString();
    calDatabase[i - databaseTitle, 6] = ((safe_castRange^(exWsCal-Cells[i, (int)CalExcelSheet::colPrintCalCert]))-Cells-Value2)-ToString();
    }
    }

    //number of products found
    int length = (calDatabase-Length)/arrayColumns;

    //variable to store the number of products i.e. excludes items with N in check serial
    int numberOfProducts = 0;

    //Steps through each product in database if the name is nullptr then dont count it
    for(i = 0; i length; i++)
    {
    if(calDatabase[i, (int)CalDatabasePosition::ProdName] != nullptr)
    numberOfProducts++;
    }

    //create a new array large enough to store all products
    arrayString^^ productList = gcnew arrayString^(numberOfProducts);

    int arrayPosition = 0;

    for(i = 0; i length; i++)
    {
    if(calDatabase[i, (int)CalDatabasePosition::ProdName] != nullptr)
    productList[arrayPosition++] = calDatabase[i, (int)CalDatabasePosition::ProdName];
    }

    Array::Sort(productList);

    cmbProducts-Items-AddRange(productList);

    //Close the Workbook
    exAppCal-Workbooks-Close();
    exAppCal-Quit();

    DB:2.74:Excel.Exe Process Does Not Stop Once Opened In Vc++ cz

    Hi Bruce,

    Thank you for having a look at my code, with Andrei's help I have now resolved the problem in my code.

    For information purposes, you say that excel.exe stops ok on your system is this with the latest versions of VS and Excel? I am currently using VS 2005 and Excel 2003.

    Best regards,
    Robin

  • RELEVANCY SCORE 2.73

    DB:2.73:Remove Duplicate Entries In Cells Excel km


    Hi,

    DB:2.73:Remove Duplicate Entries In Cells Excel km

    Thanks, but is there a simple method?

    Hi,
    try using a code.

  • RELEVANCY SCORE 2.73

    DB:2.73:How To Replace Duplicate Cells Information With A Blank Cell c3


    I am trying to find a way after exporting a file into excel that has duplicate cells ie.
    Item Code MaterialDescription Location
    123456paper Stockton
    123456 paper Fresno
    to make the duplicate cells ( the cells that are in bold ) to show up as a blank cell.

  • RELEVANCY SCORE 2.73

    DB:2.73:Worksheet Change Event Based On Two Cells j9


    Hopfully simple...
    On the same worksheet... Worksheet change event
    If A1 changes, run macro_01
    If B1 changes, run macro_02

    DB:2.73:Worksheet Change Event Based On Two Cells j9

    Thanx!
    When I make my first million.... I'm buying you a Krystal WITH cheese!
    (...or White Castle if you prefer) :-)

  • RELEVANCY SCORE 2.70

    DB:2.70: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.70:How To Make Macro Run Faster mk

    Thanks, to everyone very much for suggestions :

  • RELEVANCY SCORE 2.70

    DB:2.70:Copy Data Which Are Nonempty In A Worksheet 9j


    I need a macro to copy the visible cells or in fact the nonempty cells in the worksheet and then paste to another sheet. I couldnt find any answer in any of the BLOGS till now.
    I couldn post this question in http://answers.microsoft.com/en-us because i dont get much answers or replies there for macro based questions.
    So i need help in this forum please.

    DB:2.70:Copy Data Which Are Nonempty In A Worksheet 9j

    Sorry ron.. forgot to provide much info on this..
    But still i modified Gary's code and was able to do .
    Thanks for your time and patience

  • RELEVANCY SCORE 2.70

    DB:2.70:Mirroring Data In Another Cell 79


    Hello,

    I have duplicate data in two parts of my worksheet and I want to ensure that when I change it in one cell the duplicate also changes to the same value. I'm aware of using the "equals" original cell to do this for values but not sure it works with text?

    Also, this works with individual cells, is it possible to duplicate a row of cells so that changing any cell will change the corresponding cell in the duplicate row in another part of the worksheet? I hope this is understandable!

    Thanks,
    Barry

    DB:2.70:Mirroring Data In Another Cell 79

    As usual I was trying to make a mountain out of a molehill! It's simple and works perfectly,

    As usual, looking in the PDF User Manual delivered with every copy of iWork would have spare time (page 66 in English version)

    Yvan KOENIG (from FRANCE samedi 1 novembre 2008 12:24:26)

  • RELEVANCY SCORE 2.68

    DB:2.68:How To Retrive Info From Worksheet Multiple Cells Into New Worksheet Specific Cells pj


    I would like to retrive info from Worksheet A multiple cells into new worksheet B to specific cells. Please help.

    Thanks,
    HG

    DB:2.68:How To Retrive Info From Worksheet Multiple Cells Into New Worksheet Specific Cells pj

    Hi not sure what you need you want in sheet B to sum cells A1 to A5 from sheet A if yes
    =sum(sheetA!A1:A5)

  • RELEVANCY SCORE 2.67

    DB:2.67:Create Excel File Results In Unreadable Content Error On Open mf


    Hi,
    I have a page to upload excel file. In my page, I first save the excel file locally and then open it. I only read column B value of each row and base on the value of column B to search in SQL server. The returned search results inserted into the corresponding
    row.
    I tested the code with an excel file with ONLY ONE worksheet. The code runs without any errors. When the total number of cells in the worksheet more thanaround1000, the excel file gets an Excel Found Unreadable Content error on open. I clicked
    Yes to repair the file and the dialog shows Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. without any details. I also find the data inserted perfectly in the workbook.
    Can anyone help to solve this error? Thanks

    My insert text code:
    public static void InsertText(SpreadsheetDocument spreadSheet, string text, uint rowIndex, string columnName, string sheetName, bool flag)
    {
    WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sheetName);

    // Insert cell A1 into the new worksheet.
    Cell cell = InsertCellInWorksheet(columnName, rowIndex, worksheetPart);

    if (flag)
    {
    // Get the SharedStringTablePart. If it does not exist, create a new one.
    SharedStringTablePart shareStringPart;
    if (spreadSheet.WorkbookPart.GetPartsOfTypeSharedStringTablePart().Count() 0)
    {
    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfTypeSharedStringTablePart().First();
    }
    else
    {
    shareStringPart = spreadSheet.WorkbookPart.AddNewPartSharedStringTablePart();
    }

    // Insert the text into the SharedStringTablePart.
    int index = InsertSharedStringItem(text, shareStringPart);
    cell.CellValue = new CellValue(index.ToString());
    cell.DataType = new EnumValueCellValues(CellValues.SharedString);
    }
    else
    {
    //number
    cell.CellValue = new CellValue(text);
    cell.DataType = new EnumValueCellValues(CellValues.Number);
    }

    // Save the new worksheet.
    worksheetPart.Worksheet.Save();
    }

    DB:2.67:Create Excel File Results In Unreadable Content Error On Open mf

    Hi karen0123456789,
    Thanks for posting in the MSDN Forum.
    It's based on my experience that the key of your issue will be the algorithm of your program. How many times your InsertText method will be implement? And every time you will iterate the SharedStringTablePart from begin to end, a great deal of resource will
    be wasted on meaningless work.
    Following is my suggest which I provide your by my experience:

    use InlineString instead of set value in SharedStringTablePart.Do you best to share every Part (Workbook, Worksheet, SharedStringTable, WorkbookStyle etc.) and their sub nods to save resource.

    I hope what I said can help you.
    Have a good day,
    TomTom Xu [MSFT]
    MSDN Community Support | Feedback to us

  • RELEVANCY SCORE 2.67

    DB:2.67:Is There Any Other Way To Insert A Text To A Cell In A Excel Document From .Net Using Open Xml??? dc


    Currently im exporting some records to excel and i used this code to insert a cell to a worksheet...
    private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
    {
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData sheetData = worksheet.GetFirstChildSheetData();
    string cellReference = columnName rowIndex;

    // If the worksheet does not contain a row with the specified row index, insert one.
    Row row;
    if (sheetData.ElementsRow().Where(r = r.RowIndex == rowIndex).Count() != 0)
    {
    row = sheetData.ElementsRow().Where(r = r.RowIndex == rowIndex).First();
    }
    else
    {
    row = new Row() { RowIndex = rowIndex };
    sheetData.Append(row);
    }

    // If there is not a cell with the specified column name, insert one.

    if (row.ElementsCell().Where(c = c.CellReference.Value == columnName rowIndex).Count() 0)
    {
    return row.ElementsCell().Where(c = c.CellReference.Value == cellReference).First();
    }
    else
    {
    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
    Cell refCell = null;
    foreach (Cell cell in row.ElementsCell())
    {
    if (string.Compare(cell.CellReference.Value, cellReference, true) 0)
    {
    refCell = cell;
    break;
    }
    }

    Cell newCell = new Cell() { CellReference = cellReference };
    row.InsertBefore(newCell, refCell);

    worksheet.Save();
    return newCell;
    }
    }
    The problem i face is if there are around 100 records it takes a long time to iterate through the loops and create the cells. So is there a more efficient way to insert a list
    of records (around 100 records) to a worksheet. Or is there a way to insert a text directly rather than creating cells and rows???

    DB:2.67:Is There Any Other Way To Insert A Text To A Cell In A Excel Document From .Net Using Open Xml??? dc

    I have used ClosedXML library available at codeplex in which it is quite easy to manipulate records in Excel file. You can add a reference to closedxml using Nuget Package manager in Visual Studio and call the methods like below

    var workbook = new ClosedXML.Excel.XLWorkbook(filePath);
    var workSheet = workbook.Worksheet(Sheet1);

    //In order to update any cell, specify column alphabet followed with a row number

    workSheet.Cell(A1).Value = mean;

    //In order to read cell value

    string val=workSheet.Cell(A1).Value;

    Hope this helps!Ovais Mehboob Ahmed Khan http://ovaismehboob.wordpress.com

  • RELEVANCY SCORE 2.67

    DB:2.67:Row Count And Column Count Of Excel File In C# zx


    How can I get the row count and column count of an excel file I opened using C#? I want to iterate through the cells with background color but I have no idea when to stop the loop. I already have a variable for the active worksheet then I have no idea what to do to get the row count and column count of the worksheet...
     
    Thanks!

    DB:2.67:Row Count And Column Count Of Excel File In C# zx

    thanks Hemant so much. it's a great advise since 5 hours ago that I've start searching on my problem.

  • RELEVANCY SCORE 2.67

    DB:2.67:Smartview Refresh All In Protected Worksheet m7


    Hi All,

    After locked the cells and protected the worksheet. I clicked the icon Refresh All and I discovered the cells are not lock. How can I Refresh All and the cells in the protected worksheet are still lock?

    Thanks,
    Michael

    DB:2.67:Smartview Refresh All In Protected Worksheet m7

    Hi All,

    After locked the cells and protected the worksheet. I clicked the icon Refresh All and I discovered the cells are not lock. How can I Refresh All and the cells in the protected worksheet are still lock?

    Thanks,
    Michael

  • RELEVANCY SCORE 2.67

    DB:2.67:Count Variable And Transfer Results To Different Worksheet k8


    I have a series of worksheets that are set up with the same column headers but different data.
    Worksheet Test1
    Col A ColB ColC ColD ColE ColF ColG ColH ColI ColJ ColI
    Row1
    Row2 failed
    Row3 failed
    ColA will be the main component to verify if I need to continue the code. The remaining columns will have a mixture of string's or #'s, I only care if the column show's blank or failed (not sure if it matters but the column could say failed-cert and
    this would not count). I want it to count one column at a time and then copy it over to another workbook that is formatted similar but the trick is tofind the original worksheets name test in the first column and then a subtotal column
    A from worksheet test in column b, then start copying over the resultsfrom column b to column c in the new workbook.
    This is what I have so far:
    Sub Calculate_Issues()
    '/Purpose:
    '/Created: 10/13/2010 04:25 PM
    '/Created By:


    Dim a As String
    Dim b As Integer
    Dim c As Integer
    Dim d As Integer
    Dim e As Variant
    Dim f As Integer
    Dim g as integer
    a =
    For b = 15 To 45
    c = 3
    d = 3
    e = 0
    f= 3
    g = 0

    Application.ScreenUpdating = False
    Workbooks.Open Filename:= _
    test.xlsx, ReadOnly:=True
    Windows(test.xlsx).Activate
    Do Until IsEmpty(Columns(d))
    Do While Not (IsEmpty(Cells(c, b)))
    Select Case Cells(c, b)
    Case
    Cells(c, b) =g 1
    Case Failed
    Cells(c, b) =e 1
    End Select
    c =c 1
    d = d 1

    Loop
    End If
    Loop

    Loop
    Windows(Untested Rows.xlsm).Activate
    Cells.Find(What:=ProVision, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    For e = Cells(37, f) To Cells(37, f)

    b = b 1
    f = f 1
    Next
    Next

    End Sub

    DB:2.67:Count Variable And Transfer Results To Different Worksheet k8

    Hello,
    Your question falls into a category which requires a more in-depth level of support. Please visit the below link to see the various free and paid support options that are available to better meet your needs.
    http://support.microsoft.com/kb/2273257/en-us
    Best Regards,
    ChadChad Mathiason

  • RELEVANCY SCORE 2.67

    DB:2.67:Excel 2007 Macros With Vb xc


    Using VB in a 2007 Excel Macro, how can you program VB to find exactly matched content from individual cells within a column on worksheet 3 in worksheet 2, to then have VB Copy information from the 2nd cell over from the individually matched content on worksheet 2 back over to worksheet 3 but in the correct cell. For example;

    DB:2.67:Excel 2007 Macros With Vb xc

    Hello,Please refer to this VBA basic tutorial:http://www.anthony-vba.kefra.com/vba/vbabasic2.htmWe could manipulate the Cells with Excel Range object, such as :Worksheets(Sheet2).Range(A1, A3, A5) = AAAMeans set cell A1,A3,A5 with text AAA, so we could also retrieve the value of a cell by using the same format. So you need to write your own logic to locate the cells, and set/retrieve the value of it.Thanks.Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • RELEVANCY SCORE 2.66

    DB:2.66:Count Number Of Cells Find In A Coulmn zx


    How to get fta.count in below code.

  • RELEVANCY SCORE 2.66

    DB:2.66:Set Certain Cells On A Worksheet To Uppercase Only x1


    The title says it all

    DB:2.66:Set Certain Cells On A Worksheet To Uppercase Only x1

    Is this going to be random cells or range specific? The code that I supplied will change the cell to upper case after the information in the cell is entered and the user moves out of that cell. All you need to do is change the range to the code to meet your
    needs.

    ("D2:D1000,F2:F1000,G2:G1000,K2:K1000"))

  • RELEVANCY SCORE 2.66

    DB:2.66:Counting Instances Of Order Types In Monthly Buckets 1x


    Hey All,
    I am a bit of a noob to programming and have been doing some macro scripting to help summarise data. The latest one works fine building a table from raw data extracted using ODBC but it takes for ever over the 600k of records it needs to analyse.
    Any help in coding a better way to speed things up would be appreciated. Its the 'PopulateTable' sub that I am wanting help on doing a different way specifically.
    So after the ODBC get I have two columns A has the Order date for each order (yyyymmdd) and B has the method of entry. What I need to do is list how many orders were placed by each method for each month of the date range (say a year so 600 records.
    Here is my very dodgy code :-(
    Thanks in advance.
    FormatSalesperson
    'Create Summary Page
    DataSummary
    'Create Data Table on Summary Page
    CreateTable
    'Populate Table
    PopulateTable
    End Sub
    'Populate Table
    Sub PopulateTable()
    Dim iDate, iSales As String
    Dim iCol As Integer
    iCol = 5
    Dim iRow As Integer
    iRow = 2
    Worksheets(Orders).Activate
    Cells(2, 1).Select
    iLastRow = fGetLastRow
    'Loop through Orders adding count to table where relevant
    For iLpc = 2 To iLastRow
    Cells(iLpc, 1).Select
    iDate = Left(Cells(iLpc, 1).Value, 6)
    iSales = Cells(iLpc, 2).Value
    Worksheets(Summary).Activate
    'Set Variable to equal Column where Date exists
    Cells(1, 1).Select
    For iCol = 5 To 36
    'Find Correct column
    Dim iTblDate As String
    iTblDate = Cells(1, iCol).Value
    If iTblDate = iDate Then
    For iRow = 2 To 15
    'Find Correct Row
    If Cells(iRow, 4).Value = iSales Then
    Cells(iRow, iCol).Value = Cells(iRow, iCol).Value 1
    End If
    Next
    End If
    Next
    Worksheets(Orders).Activate
    Next
    End Sub

    'Create Data Table on Summary Page
    Sub CreateTable()
    Worksheets(Summary).Activate
    'Add Dates across the top
    Cells(2, 1).Select
    iLastRow = fGetLastRow
    Dim iCol As Integer
    iCol = 5
    For iLpc = 2 To iLastRow
    Cells(1, iCol).Value = Cells(iLpc, 1).Value
    iCol = iCol 1
    Next
    'Add Salesperson Values down D Column
    Cells(2, 2).Select
    iLastRow = fGetLastRow
    Dim iRow As Integer
    iRow = 2
    For iLpc = 2 To iLastRow
    Cells(iRow, 4).Value = Cells(iLpc, 2).Value
    iRow = iRow 1
    Next
    End Sub
    'Create Summary Page
    Sub DataSummary()

    Worksheets.Add
    ActiveSheet.Name = Summary
    Worksheets(Orders).Activate

    'Copy and format Dates
    Range(A:A).Copy
    Worksheets(Summary).Activate
    ActiveSheet.Paste Destination:=Worksheets(Summary).Range(A:A)
    'Remove Duplicates
    ActiveSheet.Range(A:A).RemoveDuplicates Columns:=1, Header:= _
    xlYes
    'Trim to just Year and Month
    Cells(2, 1).Select
    iLastRow = fGetLastRow
    For iLpc = 2 To iLastRow
    Cells(iLpc, 1).Select
    Cells(iLpc, 1).Value = Left(Cells(iLpc, 1).Value, 6)
    Next
    'Remove Duplicates
    ActiveSheet.Range(A:A).RemoveDuplicates Columns:=1, Header:= _
    xlYes
    'Sort
    With ActiveWorkbook.Worksheets(Summary).Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(A:A), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range(A:A)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    'Copy and format Salesperson
    Worksheets(Orders).Activate
    Range(B:B).Copy
    Worksheets(Summary).Activate
    ActiveSheet.Paste Destination:=Worksheets(Summary).Range(B:B)
    'Remove Duplicates
    ActiveSheet.Range(B:B).RemoveDuplicates Columns:=1, Header:= _
    xlYes
    'Sort
    With ActiveWorkbook.Worksheets(Summary).Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(B:B), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range(B:B)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With


    End Sub
    'Change Salesperson Column to Human Readable values Numbers to Manual Entry
    Sub FormatSalesperson()
    'Comment
    Cells(1, 1).Select
    iLastRow = fGetLastRow
    For iLpc = 2 To iLastRow
    Cells(iLpc, 1).Select
    If Cells(iLpc, 2).Value = WEB Then
    Cells(iLpc, 2).Value = Webcell
    'iLpc = iLpc - 1
    ElseIf Cells(iLpc, 2).Value = BAT Then
    Cells(iLpc, 2).Value = EBO
    ElseIf Cells(iLpc, 2).Value = 0 And Cells(iLpc, 2).Value = 999 Then
    Cells(iLpc, 2).Value = Manual Entry
    End If
    Next
    End Sub

    'Get Order Data from OOHEAD for Given Period
    Sub GetOrders()
    'Get Date to use in SQL
    Dim iDate As Long
    iDate = Cells(3, 3).Value
    'Load SQL String
    QryStr =
    Open C:\Documents and Settings\jeremyl\My Documents\Work_Files 18_02_09\Ecommerce\Orders SQL.mss For Input As #1
    Do While Not EOF(1)
    MyChar = Input(1, #1)
    QryStr = QryStr MyChar
    Loop
    Close #1

    'Add in Date variable value to query string
    QryStr = Replace(QryStr, :Date, iDate, 1, -1, 1)
    Workbooks.Add
    While Sheets.Count 1
    Application.DisplayAlerts = False
    Sheets(2).Delete
    Application.DisplayAlerts = True
    Wend
    ActiveSheet.Name = Orders
    With ActiveSheet.QueryTables.Add(Connection:=ODBC;DSN=TEST;USER NAME=ODBC;PASSWORD=ODBC;, _
    Destination:=Range(A1))
    .CommandText = QryStr
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With

    End Sub

    'Function to Get last row of active worksheet
    Function fGetLastRow() As Long
    ' Save current selected cell
    glrC = ActiveCell.Column
    glrR = ActiveCell.Row
    Range(A1048576).Select
    Selection.End(xlUp).Select
    fGetLastRow = ActiveCell.Row
    ActiveSheet.Cells(glrR, glrC).Select
    End Function

    DB:2.66:Counting Instances Of Order Types In Monthly Buckets 1x

    The changes below that I made is mainly style but somne of the changes will improve performance. Using find/findnext is one of the performance changes. I also found one loop that wasn't necessary. Using Select and Active slow down the
    code because the cells or worksheets actually have to be selected and updated which take time.

    Sub main()
    FormatSalesperson
    Create Summary Page
    DataSummary
    Create Data Table on Summary Page
    CreateTable
    Populate Table
    PopulateTable
    End Sub

    Populate Table
    Sub PopulateTable()
    Dim iDate, iSales As String
    Dim iCol As Integer
    iCol = 5
    Dim iRow As Integer
    iRow = 2

    avoid acrtivate and select
    Worksheets(Orders).activate
    With Worksheets(Orders)
    Cells(2, 1).Select
    iLastRow = .Range(A Rows.Count).End(xlUp).Row
    Loop through Orders adding count to table where relevant

    For iLpc = 2 To iLastRow
    .Cells(iLpc, 1).select
    iDate = Left(.Cells(iLpc, A).Value, 6)
    iSales = .Cells(iLpc, B).Value

    Worksheets(Summary).Activate
    With Worksheets(Summary)
    Set HeaderDates = .Range(E1, AK1)
    Set c = HeaderDates.Find(what:=iDate, _
    LookIn:=xlValues, lookat:=xlWhole)
    If c Is Nothing Then
    MsgBox (Cannot Find Date : iDate)
    Else
    Set SearchCol = .Range(.Cells(2, c.Column), _
    .Cells(15, c.Column))
    Set c = SearchCol.Find(what:=iSales, _
    LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
    FirstAddr = c.Address
    Do
    c.Value = c.Value + 1
    Set c = SearchCol.FindNext(after:=c)
    Loop While Not c Is Nothing And c.Address FirstAddr
    End If
    End If
    Set Variable to equal Column where Date exists
    Cells(1, 1).Select
    For iCol = 5 To 36
    Find Correct column
    Dim iTblDate As String
    iTblDate = .Cells(1, iCol).Value
    If iTblDate = iDate Then

    For iRow = 2 To 15
    Find Correct Row
    If .Cells(iRow, 4).Value = iSales Then
    .Cells(iRow, iCol).Value = _
    .Cells(iRow, iCol).Value + 1
    End If
    Next
    End If
    End If
    End With
    Next iLpc
    .Worksheets(Orders).Activate
    Next
    End With

    End Sub

    Create Data Table on Summary Page
    Sub CreateTable()
    Worksheets(Summary).Activate
    With Worksheets(Summary)
    Add Dates across the top
    Cells(2, 1).Select
    iLastRow = fGetLastRow
    iLastRow = .Range(A Rows.Count).End(xlUp).Row
    Dim iCol As Integer
    iCol = 5
    For iLpc = 2 To iLastRow
    .Cells(1, iCol).Value = .Cells(iLpc, A).Value
    iCol = iCol + 1
    Next iLpc
    Add Salesperson Values down D Column
    Cells(2, 2).Select
    iLastRow = fGetLastRow
    iLastRow = .Range(B Rows.Count).End(xlUp).Row
    Dim iRow As Integer
    iRow = 2

    this loop only keep on over writing the cell
    Cells(iRow, 4) without incrementing iRow
    For iLpc = 2 To iLastRow
    Cells(iRow, 4).Value = Cells(iLpc, 2).Value
    iRow = iRow + 1
    Next
    .Cells(iRow, 4).Value = .Cells(iLastRow, 2).Value
    End With
    End Sub

    Create Summary Page
    Sub DataSummary()

    Worksheets.Add
    add new worksheet as last sheet
    Set NewSht = Worksheets.Add(after:=Sheets(Sheets.Count))
    ActiveSheet.Name = Summary
    NewSht.Name = Summary
    Worksheets(Orders).Activate
    With Worksheets(Orders)

    Copy and format Dates
    .Range(A:A).Copy
    Worksheets(Summary).Activate
    ActiveSheet.Paste Destination:=Worksheets(Summary).Range(A:A)
    .Columns(A).Copy _
    Destination:=Worksheets(Summary).Columns(A)
    Remove Duplicates
    End With

    With Worksheets(Summary)

    ActiveSheet.Range(A:A).RemoveDuplicates Columns:=1, Header:= _
    xlYes
    .RemoveDuplicates Columns:=1, _
    header:=xlYes

    Trim to just Year and Month

    Cells(2, 1).Select
    iLastRow = fGetLastRow
    iLastRow = .Range(A Rows.Count).End(xlUp).Row
    For iLpc = 2 To iLastRow
    Cells(iLpc, 1).Select
    .Cells(iLpc, 1).Value = Left(.Cells(iLpc, 1).Value, 6)
    Next iLpc

    Remove Duplicates
    ActiveSheet.Range(A:A).RemoveDuplicates Columns:=1, Header:= _
    xlYes
    .Range(A:A).RemoveDuplicates Columns:=1, header:=xlYes

    Sort
    .with ActiveWorkbook.Worksheets(Summary).Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(A:A), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range(A:A)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .Columns(A).Sort _
    key1:=.Range(A1), _
    Order:=xlAscending, _
    header:=xlYes, _
    DataOption:=xlSortNormal, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    SortMethod:=xlPinYin
    End With

    Copy and format Salesperson
    Worksheets(Orders).Activate
    With Worksheets(Orders)
    .Columns(B).Copy _
    Destination:=Worksheets(Summary).Columns(B)
    End With
    Worksheets(Summary).Activate
    ActiveSheet.Paste Destination:=Worksheets(Summary).Range(B:B)
    Remove Duplicates
    ActiveSheet.Range(B:B).RemoveDuplicates Columns:=1, header:= _
    xlYes
    With Worksheets(Summary)
    .Columns(B).RemoveDuplicates Columns:=1, _
    header:=xlYes
    Sort
    With ActiveWorkbook.Worksheets(Summary).Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(B:B), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range(B:B)
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    .Columns(B).Sort _
    key1:=.Range(B1), _
    Order:=xlAscending, _
    header:=xlYes, _
    DataOption:=xlSortNormal, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    SortMethod:=xlPinYin

    End With



    End Sub
    Change Salesperson Column to Human Readable values Numbers to Manual Entry
    Sub FormatSalesperson()
    With Worksheets(Orders)
    Comment
    Cells(1, 1).Select
    iLastRow = fGetLastRow
    iLastRow = .Range(A Rows.Count).End(xlUp).Row
    For iLpc = 2 To iLastRow
    Cells(iLpc, 1).Select
    OldCellType = .Cells(iLpc, B).Value
    If Cells(iLpc, 2).Value = WEB Then
    Cells(iLpc, 2).Value = Webcell
    iLpc = iLpc - 1
    ElseIf Cells(iLpc, 2).Value = BAT Then
    Cells(iLpc, 2).Value = EBO
    ElseIf Cells(iLpc, 2).Value = 0 And Cells(iLpc, 2).Value = 999 Then
    Cells(iLpc, 2).Value = Manual Entry
    End If

    set null if doesnt math any cases below
    NewCellType =
    If IsNumeric(OldCellType) Then
    OldCellTypeNum = Val(OldCellType)
    If OldCellTypeNum = 0 And _
    OldCellTypeNum = 999 Then

    NewCellType = Manual Entry
    End If
    End If
    Select Case OldCellType
    Case WEB: NewCellType = Webcell
    iLpc = iLpc - 1
    Case BAT: NewCellType = EBO
    Case BAT: NewCellType = EBO
    Case Is = 0

    End Select

    .Cells(iLpc, B).Value = NewCellType
    Next
    End With
    End Sub

    Get Order Data from OOHEAD for Given Period
    Sub GetOrders()
    Get Date to use in SQL
    Dim iDate As Long

    With Worksheets(Orders)
    iDate = .Cells(3, 3).Value
    End With

    Load SQL String
    QryStr =
    Open C:\Documents and Settings\jeremyl\My Documents\Work_Files 18_02_09\Ecommerce\Orders SQL.mss For Input As #1
    Do While Not EOF(1)
    MyChar = Input(1, #1)
    QryStr = QryStr + MyChar
    Loop
    Close #1

    Add in Date variable value to query string
    QryStr = Replace(QryStr, :Date, iDate, 1, -1, 1)
    Set NewBk = Workbooks.Add
    With NewBk
    Application.DisplayAlerts = False
    While Sheets.Count 1
    .Sheets(2).Delete
    Wend
    Application.DisplayAlerts = True

    .Sheets(1).Name = Orders

    With .Sheets(Orders).QueryTables.Add( _
    Connection:=ODBC;DSN=TEST;USER NAME=ODBC;PASSWORD=ODBC;, _
    Destination:=.Range(A1))

    .CommandText = QryStr
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    End With
    End Sub

    Function to Get last row of active worksheet
    Function fGetLastRow(FindCol As ) As Long
    Save current selected cell
    glrC = ActiveCell.Column
    glrR = ActiveCell.Row

    Range(A1048576).Select
    Selection.End(xlUp).Select
    fGetLastRow = ActiveCell.Row
    ActiveSheet.Cells(glrR, glrC).Select
    End Function

  • RELEVANCY SCORE 2.65

    DB:2.65:Can Print Titles From One Worksheet Be Referenced In Another Worksheet In The Same Workbook In Excel 2010 9k


    Have set up a form in excel that uses merged cells to approximate an existing printed form. Below the fill in area, are merged rows/columns for the user. There is a requirement for the user to be able to use the sort function which requires these columns
    to be 'unmerged' and then re-merged which the user is not going to do. To try to get around this....

    I separated the top information which uses the merged cells and placed the columnar information on a separate worksheet with the cells non-merged where the user can enter their data and sort as required.

    Can I reference the Print Title cell range A1:M137 from the first worksheet as the Print Title for the second worksheet so when this sheet is printed, it prints as the completed form?

    Can't find the exact circumstances trolling other sites....

    Regards - Lenny33

  • RELEVANCY SCORE 2.65

    DB:2.65:Compare Cells In Column F In Worksheet 1 To Cells In Column H In Worksheet 2 And Report The Difference If Not Equal. 3d



    Worksheet 1 Column F is sorted in ascending order and the number of rows could be as many an 12k. Worksheet 2 Column H is sorted in ascending order and also very large but the row count could differ from Worksheet 1.

    DB:2.65:Compare Cells In Column F In Worksheet 1 To Cells In Column H In Worksheet 2 And Report The Difference If Not Equal. 3d


    Worksheet 1 Column F is sorted in ascending order and the number of rows could be as many an 12k. Worksheet 2 Column H is sorted in ascending order and also very large but the row count could differ from Worksheet 1.

    Example: If worksheet 1 column F row 18 has a value of 12 and worksheet 2 column G has a value of 9, then I want to generate a worksheet 3 reporting the difference of the item id (120-3245) pulled versus returned: 12-9=3 showing the item id worksheet 3 and
    item id.

  • RELEVANCY SCORE 2.65

    DB:2.65:Lcoking Individual Cells In Excel 2007 3k


    How do you lock individual cells in Excel 2007 without locking/protecting the entire worksheet?

    DB:2.65:Lcoking Individual Cells In Excel 2007 3k

    Yes, it was operator error! Combining Trip-to-Tokyo's steps with Bernard's comment about choices within the Protection dialog box, it works.
    SPXN, you CAN get the functionality you want in a protected sheet. Follow T-t-T's steps plus tick the many boxes in the Protection box for what you'll "Allow all users of this worksheet to:" do. I needed to tick the boxes for Select Locked Cells and Select
    Unlocked Cells; I don't want my users to add or delete columns or rows, but you do so you'll need to tick those.
    Thank you, Trip to Tokyo Bernard!

  • RELEVANCY SCORE 2.65

    DB:2.65:Hi, I Want To Use A Formula To Find If 2 Cells Contain Exactly The Same Formula, Not The Same Value When The Formula Is Calculated. a7


    I want to create a self-marking worksheet for students. In particular I want to check if they have added a column of numbers using Autosum correctly or used cell references with plus signs, or indeed just typed the answer. Every comparison I have tried
    so far, indicates the cells are the same if the resulting value is the same.

    DB:2.65:Hi, I Want To Use A Formula To Find If 2 Cells Contain Exactly The Same Formula, Not The Same Value When The Formula Is Calculated. a7

    rng is the argument passed to the function. For example if you enter the formula
    =CellFormula(A10) in a cell, rng is cell A10.
    rng.Formula is the text of the formula of rng, so in our example the formula of cell A10 (if the cell doesn't have a real formula, the value of the cell is returned).
    The line
    CellFormula = rng.Formula
    in the VBA code specifies that the CellFormula function returns the text of the formula of rng.

  • RELEVANCY SCORE 2.65

    DB:2.65:Clear Cells On Not Active Worksheet 8p


    Is there a way to clear the contents the range B:F on worksheet DataStorage without making the worksheet active. NoteI need to clear the data not Delete the contents.

    DB:2.65:Clear Cells On Not Active Worksheet 8p

    Try

    Worksheets("DataStorage").Range("B:F").ClearContents

  • RELEVANCY SCORE 2.64

    DB:2.64:How Do I Copy A Formula Value Total Count From One Worksheet To Another In The Same Workbook 19


    Am using Microsoft Excel, Mac version, and have several columns, each ending with a column total of checked boxes (X) in the first worksheet. I want to have those column totals appear automatically in cells in a different worksheet within the same workbook
    so that the second worksheet has the totals from each column in the first worksheet. Seems like it should be simple, but can't seem to copy and paste without an error message, and trying each option to correct the error doesn't seem to work, also cannot find
    any pertinent info in Help.

    DB:2.64:How Do I Copy A Formula Value Total Count From One Worksheet To Another In The Same Workbook 19

    Thanks very much. Using the 'worksheet name' for sheet 1 with single quotes in the formula worked great.

  • RELEVANCY SCORE 2.64

    DB:2.64:Links To Other Data Sourses ps


    In Excell 2010, we have a worksheet that has links to other worksheets. How do I find what cells contain these links?

    DB:2.64:Links To Other Data Sourses ps

    Yes this did work!!!! Thank you. I found most of them, but I still must be missing some because I still have the message coming up when I open Excel asking me if I want to update these links. I'll keep looking. Thank you again!

    Hi,

    This only works for the active sheet so if you have links on other sheets you will need to run the code with that sheet active. My concern wasn't that it would fail to find links i was more concerned that it
    could find cells that weren't links.

  • RELEVANCY SCORE 2.64

    DB:2.64:Visible Cells Only Selected d1


    I was able to customize my toolbar in Office 2003 with an icon for 'Visible Cells Only' that was frequently used when doing filters or subtotals to take the outcome data to another worksheet. I'm unable to find that ability in 2007

    DB:2.64:Visible Cells Only Selected d1

    You can also use the shortcut
    Alt ;
    --
    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm
    "Mike H.." wrote in message news:*** Email address is removed for privacy ***...

    Hi,

    Right click the 'Quick Action toolbar' (QAT)
    Click Customize QAT
    Form the dropdown select 'All Commands'
    Scroll down to 'Select Visible Cells'
    Select and click ADD
    OK

    Your done

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

  • RELEVANCY SCORE 2.64

    DB:2.64:Bug Office Excell 2010 9x


    hello
    support.

    I would have
    two
    problems
    with excel
    2010:
    1.
    when you
    hide
    the cells
    on the worksheet
    me
    hiding,
    but
    when I go
    to
    find out
    that I did not
    discover
    the
    how can I
    do?
    2.
    When
    I copy cells
    from
    an excel sheet
    to another
    I did not
    copy the
    cells
    but rather
    I will overwrite
    the cells
    used.
    how can I
    do?

    Thank you
    in
    advance.

    Regards

    DB:2.64:Bug Office Excell 2010 9x

    Hi
    In answer to question 1.
    To hide a cell you must first mark the cell as hidden.
    Right click the cell and select Format Cell from the menu.
    On the Protection tab tick the box for Hidden.
    The cell will not however disappear untill you activate sheet protection.
    To do this
    Click the Review tab
    Select Protect Sheet and click OK.
    This only marks the cell as hidden and the contents will still be visible.
    You may be better hiding a row or column (right click on row/column heading and select Hide).

    In answer to question 2.
    Copy and paste will overwrite the destination cells.
    If you want to keep the existing cells on the second sheet you must paste in to an unused area of the second sheet. Or you can right click where you want to put the copied cells and select Insert Copied cells from the menu.G North MMI

  • RELEVANCY SCORE 2.63

    DB:2.63:Using The Background Color Of A Cell To Automatically Place A Particular Value Into Another Cell. fd


    I have an EXCEL worksheet where some of the cells have a background color. I need to identify those cells that are colored, and which color they are, in order to decide which values to put in other cells.For example, I want to count all of the GREEN cells in a row, then place that total in another area of the worksheet.I have looked through the MS list of EXCEL functions and been unable to find what I need. I have perused various MS forums and been unable to find the answer to my question.

    DB:2.63:Using The Background Color Of A Cell To Automatically Place A Particular Value Into Another Cell. fd

    I have an EXCEL worksheet where some of the cells have a background color. I need to identify those cells that are colored, and which color they are, in order to decide which values to put in other cells.For example, I want to count all of the GREEN cells in a row, then place that total in another area of the worksheet.I have looked through the MS list of EXCEL functions and been unable to find what I need. I have perused various MS forums and been unable to find the answer to my question.

    In a standard module(Insert--Module)

    Public Function f( _
    ByRef rng As Range, _
    iColor As Integer _
    ) As Long
    Dim c As Range
    Application.Volatile
    For Each c In rng
    If c.Interior.ColorIndex = iColor Then
    f = f + 1
    End If
    Next
    Set c = Nothing
    End Function
    In the code module of the sheet:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
    End Sub
    Does not work if the colors are made withconditional formatting.

    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/

  • RELEVANCY SCORE 2.63

    DB:2.63:Count Function Begins With And Does Not Begins With 18


    I have the following in my worksheet

    Drawing No.Transmittal No.
    1011A-B-1060 TN-12000
    1011R-B-1063
    P1012-V-1023 TN-05121
    P1013-C-1024
    so on and so forth

    1. I want to count all records that starts with P, how? and does not begins with P, how? can anyone help me...
    2. I want to count number of blank cells to a records that is not start with "P", how?

    Thank you in advance....
    revned

    DB:2.63:Count Function Begins With And Does Not Begins With 18

    Using SUMPRODUCT is the same as using an array formula, and in Excel 2003 you cannot reference entire columns in an array formula. So change your references to A1:A65535 and E1:E65535 (or less) and try again.

  • RELEVANCY SCORE 2.63

    DB:2.63:Import From Xlsx With High Speed 1a


    Hi
    I used the following code for import it works slowly,how can I improve itSpeed?I don't want to import from xml,
    private void Button_Click(object sender, RoutedEventArgs e)
    {

    OpenFileDialog flDialog = new OpenFileDialog();

    flDialog.Filter = Excel Files(*.xlsx)|*.xlsx;

    bool res = (bool)flDialog.ShowDialog();

    if (res)
    {

    FileInfo fs = flDialog.File;

    string fileName = fs.Name;

    #region Reading Data From Excel File

    dynamic objExcel = AutomationFactory.CreateObject(Excel.Application);

    //Open the Workbook Here

    dynamic objExcelWorkBook =

    objExcel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

    \\ fileName);

    //Read the Worksheet

    dynamic objActiveWorkSheet = objExcelWorkBook.ActiveSheet();

    //Cells to Read

    dynamic objCell_1, objCell_2;

    //Iterate through Cells

    for (int count = 2; count 300; count)
    {

    objCell_1 = objActiveWorkSheet.Cells[count, 1];

    objCell_2 = objActiveWorkSheet.Cells[count, 2];

    populationData.Add

    (

    new PopulationClass()

    {

    StateName = objCell_1.Value,

    Population = objCell_2.Value

    }

    );

    }

    dgExcelData.ItemsSource = populationData;

    #endregion

    }


    }

    DB:2.63:Import From Xlsx With High Speed 1a

    Hi Angela,
    Well, it seems you are using Primary Interop Assemblies to communicate with Excel, which is quite slow actually.

    If I were you I would use OpenXMLfor speeding up the file reading and writing, however it requires pretty much code to write. This might be the fastest way.
    Or you would like to try some third party library for instance:
    http://npoi.codeplex.com/, by this you could definitely speed up your reading and writing.
    By the way, I think you should ask such question atExcel for Developers
    forum for a better support, the experts there will tell you what is the best way to communicate with Excel file. Hardly to provide more information from Silverlight forum. Thank you.
    --James

    THE CONTENT IS PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED
    Thanks
    MSDN Community Support

    Please remember to Mark as Answer the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

  • RELEVANCY SCORE 2.62

    DB:2.62:Label K .Caption = Worksheet(Pc Database).Cells(L, J) k3


    What am I doing wrong.

    I have a series of labelbox'S on a userform that I am pulling information from Excel. There are 22 sequential columns in the same row (Col X - AS). I am using MATCH to find the item on a rowin column C, then looking across to X - AS. I am using aDo
    Loop to loop thru each Labelbox (11 -42) to put the(col 1, row) information in. I am very new to this and my head hurts. Please help.

    Private Sub CommandButton1_Click()
    Worksheets(PC Database).Select
    DateLabel = Now()
    partnumber = TextBox1.Value
    If partnumber = Then
    MsgBox Please enter a Part Number
    End If
    Worksheets(PC Database).Select
    Application.ScreenUpdating = True
    l = Application.WorksheetFunction.Match(partnumber, Range(c1:c18800), 0)
    Label2.Caption = Part Number Part Name vbCrLf Worksheets(PC Database).Cells(l, 3) vbTab Worksheets(PC Database).Cells(l,
    5)
    Label3.Caption = Supplier Kanban vbCrLf Worksheets(PC Database).Cells(l, 6) vbTab
    Worksheets(PC Database).Cells(l, 12)
    j = 26
    k = 11
    m = k
    'm = Label k
    'n = m .Caption=worksheet('PC Database').cells(l,j)
    Do While j = 42
    If Cells(l, j) = 1 Then '
    Label k .Caption = Worksheet('PC Database').Cells(l, j)
    Else
    'Label11.Caption = 0
    End If
    j = j 1
    k = k 1
    'm = Label k
    'n = m .Caption=worksheet('PC Database').cells(l,j)
    Loop


    End Sub

    Jim Neely

    DB:2.62:Label K .Caption = Worksheet(Pc Database).Cells(L, J) k3

    Hi Jim,
    For VB6 questions, please take a look at the links below:
    Where to post your VB 6 questions
    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

    Click
    HERE to participate the survey.

  • RELEVANCY SCORE 2.62

    DB:2.62:Excel 2010 Ignore Cells A1 And A2 And Start From Cell A3 In Column Aa z1


    Hello from Steved

    Please tell the macro to Ignore Cells A1 and A2 and Start from Cell A3 In Column A:A.
    And in "Column C:C", Cell C3start the count at number 1 as it is starting the count at 3

    Sub NumberColumn()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' get the last row from column A that has a value
    lastRow = ws.Range("A"
    ws.Rows.Count).End(xlUp).Row

    ' use the last row to determine how far down to extend the formula

    ws.Range("C3:C"
    lastRow).Formula =
    "=row()"
    End Sub

    Thank you.

  • RELEVANCY SCORE 2.61

    DB:2.61:Counting Conditionally Formatted Bad Style Cells In A Worksheet jd



    Hi Guys,
    [ check attached images ]
    Anyone can help me - I've marked number of cells conditionally marked as Bad style (having different formula conditions in each row of data) in my different worksheets.
    If I need to COUNT all Bad cells by some formula/function - what should be the best option for me.
    Any simple and straight method will work as blessing for me :)
    Thanks and Cheers,
    HS

    DB:2.61:Counting Conditionally Formatted Bad Style Cells In A Worksheet jd

    Re: counting syles

    A short bit of vba code will do it.
    The sLogo variable should be assigned the name of the style searched for.
    Currently it is set to search for Bad styles.
    Select the cells you wish to search and run the code.
    (do not select entire columns or rows)
    '---
    Sub HowMany()
    Dim N As Long
    Dim rCell As Range
    Dim sLogo As String

    sLogo = Bad
    For Each rCell In Selection.Cells
    If rCell.Style.Name = sLogo Then
    N = N 1
    End If
    Next
    MsgBox sLogo style count is N in range Selection.Address(0, 0)
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    Formats Styles excel add-in : Free
    (Lists or deletes unused styles or custom number formats from a workbook)
    https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

  • RELEVANCY SCORE 2.61

    DB:2.61:Count Visible Cells 1x


    How can count visible cells?
    Something similar to the sum visible cells.
    Using macros.

    Thank you all again.

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

    DB:2.61:Worksheet Cells Wont Display Contents dc


    Excel worksheet cells won't display their contents

  • RELEVANCY SCORE 2.61

    DB:2.61:Re: Essbase - File Row Count k1


    The Application log should tell you how many cells were updated. A row count is not logged, if there was only one data column, then the cell and row counts should be the same unless there was duplicate records summed ("add to existing" option) or replaced when loaded.

    DB:2.61:Re: Essbase - File Row Count k1

    Senthil,

    U can use ESSCMD such as GetDBSTATE or GETDBINFOcto get some information about database staticics.

    FMI, u can refer the DBAG material.

    Cheers.,
    Cnee.,

  • RELEVANCY SCORE 2.61

    DB:2.61:Copy Cells From One Workbook To Another Using A Macro ca


    HI all
    I am a novice when it comes to VB but i have managed to find the below code which works perfectly.
    The problem is I need the information to be copied to a different workbook rather than a different worksheet within the same work book.
    Can anyone help?

    Sub UpdateLogWorksheet()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range

    'cells to copy from Input sheet - some contain formulas
    myCopy = v2,c25,j25,f9,f11,f15,r25,h5,p5,u5,b5,f10,f10,e28,k28,f10,f10,f10,f10,f10,f12,f10,f10,f10,a27
    Set inputWks = Worksheets(Input)
    Set historyWks = Worksheets(LBG OOH Spreadsheet)
    With historyWks
    nextRow = .Cells(.Rows.Count, A).End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
    Set myRng = .Range(myCopy)
    If Application.CountA(myRng) myRng.Cells.Count Then
    MsgBox Please fill in all the cells!
    Exit Sub
    End If
    End With
    With historyWks
    With .Cells(nextRow, A)
    .Value = Now
    .NumberFormat = mm/dd/yyyy hh:mm:ss
    End With
    .Cells(nextRow, B).Value = Application.UserName
    oCol = 3
    For Each myCell In myRng.Cells
    historyWks.Cells(nextRow, oCol).Value = myCell.Value
    oCol = oCol 1
    Next myCell
    End With

    'clear input cells that contain constants
    With inputWks
    On Error Resume Next
    With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
    .Clearcontents
    Application.Goto .Cells(1) ', Scroll:=True
    End With
    On Error GoTo 0
    End With
    End Sub

  • RELEVANCY SCORE 2.61

    DB:2.61:Find And Count Red Cells In A Row pa


    I want to a formula or macro to return the number of cells in rangeC23:CZ23, that have a red shaded background.

    DB:2.61:Find And Count Red Cells In A Row pa

    link is not working

    Try again. Works for me.

    Gord

  • RELEVANCY SCORE 2.61

    DB:2.61:How To Find Duplicate Row In Sql Query? 7k


    Hi All,

    Please solve my query, find duplicate row and how to count its. your suggestion would be greatly appreciated.

  • RELEVANCY SCORE 2.60

    DB:2.60:Increase The Size Of All The Cells On My Worksheet In Excel 2008 18


    How do I, after selecting all the cells on my worksheet increase the size of the cells.

    DB:2.60:Increase The Size Of All The Cells On My Worksheet In Excel 2008 18

    Thank you very much, i get it now. I had selected the cells using the diamond shape but wasn't sure about managing the Format/Row /Height.
    Getting familiar with Office for Mac, but it is the 2008 version.

  • RELEVANCY SCORE 2.60

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

    DB:2.60:Count Characters In Worksheet 3j


    Need a total count in a entire excel worksheet. (Including spaces but, excluding errors if possible)

    DB:2.60:Count Characters In Worksheet 3j

    Well, get to it, 'cause now I'm hungry! ;-)

  • RELEVANCY SCORE 2.60

    DB:2.60:Vba To Copy Cells From One Worksheet To Another 3f


    Can anyone provide a bit of code, to copy cellsfrom one worksheet to another in the same book please? Sheet1 is a stock list and sheet2 will contain the list of parts that need ordering.
    Psuedo code might be-:
    Sheet2 is activated so that a list can be created starting at the active cell, from some cells on a known row in sheet1. Also I can see the list grow.
    After searching down Sheet1, some data is found and the row is known.
    Copy Sheet1.range (cells(myRow,1), cells(myRow,5)) to Sheet2.activecell
    Move the active cell to the next row in sheet2

    Part of the macro is shown below
    Dim ws1Name As String, ws2Name As String
    Dim myRow as integer
    ' Set counters for processing Sheet1
    sfRow = 1
    ' Set sheet names
    ' Activate Sheet2 to showlist as it grows
    ws1Name = "Sheet1"
    ws2Name = "Sheet2"
    Sheets(ws2Name).Activate
    ' find a relevant row of Sheet1
    myRow = a calculation
    ' copy the data from sfRow to Sheet2 starting at the active cell
    Sheets(ws1Name).Range(Cells(myRow, 1), Cells(myRow, 5)).copy Destination:= Activecell.....? Can't do this bit
    ' move the active cell to next row
    ActiveCell.Offset(1, 0).Activate

    DB:2.60:Vba To Copy Cells From One Worksheet To Another 3f

    Oh my goodness- a full stop ! Great, thanks. It works a treat.
    I suppose the other one worked too then? It just created a list of blank cells. What a Muppethmmm ...
    Thanks again.

  • RELEVANCY SCORE 2.60

    DB:2.60:Datalogging Macro Creating Duplicate Entries 3k


    I am running a macro to record specific cells -- like a data logger.  A DDE link from data acquisition software is sending data points to specific cells in my worksheet. The first data point is the date/time from the hardware and the other points are from the active channel outputs.  The macro is designed to pick those incoming data points in those cells on a timed interval and place them in the next available row in that worksheet. 
     
    Here is the problem:  the macro is duplicating data points at the timed interval instead of just one set of data points per timed interval.  I have changed many things to remedy the problem and what I have found out through those changes is that the duplicate entry date/time stamps directly correspond to the refresh rate of the data acquisition software.  Strange thing is that it will only duplicate the entry once or twice then will wait for the next time interval.
     
    If anyone has any ideas, tips or tricks on how to correct this I would be much obliged!  Thanks in advance!
     
    Here is the code:
     
    Dim count As IntegerDim n_rows As IntegerDim start_row As IntegerDim col_mov As Integer
    Sub initialize_button_set()
    start_row = 7count = 0n_rows = Worksheets(CF 03-01).Cells(1, 2).ValueApplication.OnTime Now + TimeValue(00:01:00), get_dataRem Worksheets(CF 03-01).Buttons.Add 275, 0, 72, 36Worksheets(CF 03-01).Buttons.Caption = STOPWorksheets(CF 03-01).Buttons.OnAction = Halt
    End Sub
    Sub get_data()
    If count = n_rows Then      Halt
    Else
       For col_mov = 0 To 6       Worksheets(CF 03-01).Cells(start_row + count, 2 + col_mov).Value = Worksheets(CF 03-01).Cells(5, 2 + col_mov).Value   Next col_mov    Worksheets(CF 03-01).Cells(3, 2).Value = start_row + count    count = count + 1    Application.OnTime Now + TimeValue(00:01:00), get_data 
    End IfEnd Sub

    DB:2.60:Datalogging Macro Creating Duplicate Entries 3k

    I am running a macro to record specific cells -- like a data logger.  A DDE link from data acquisition software is sending data points to specific cells in my worksheet. The first data point is the date/time from the hardware and the other points are from the active channel outputs.  The macro is designed to pick those incoming data points in those cells on a timed interval and place them in the next available row in that worksheet. 
     
    Here is the problem:  the macro is duplicating data points at the timed interval instead of just one set of data points per timed interval.  I have changed many things to remedy the problem and what I have found out through those changes is that the duplicate entry date/time stamps directly correspond to the refresh rate of the data acquisition software.  Strange thing is that it will only duplicate the entry once or twice then will wait for the next time interval.
     
    If anyone has any ideas, tips or tricks on how to correct this I would be much obliged!  Thanks in advance!
     
    Here is the code:
     
    Dim count As IntegerDim n_rows As IntegerDim start_row As IntegerDim col_mov As Integer
    Sub initialize_button_set()
    start_row = 7count = 0n_rows = Worksheets(CF 03-01).Cells(1, 2).ValueApplication.OnTime Now + TimeValue(00:01:00), get_dataRem Worksheets(CF 03-01).Buttons.Add 275, 0, 72, 36Worksheets(CF 03-01).Buttons.Caption = STOPWorksheets(CF 03-01).Buttons.OnAction = Halt
    End Sub
    Sub get_data()
    If count = n_rows Then      Halt
    Else
       For col_mov = 0 To 6       Worksheets(CF 03-01).Cells(start_row + count, 2 + col_mov).Value = Worksheets(CF 03-01).Cells(5, 2 + col_mov).Value   Next col_mov    Worksheets(CF 03-01).Cells(3, 2).Value = start_row + count    count = count + 1    Application.OnTime Now + TimeValue(00:01:00), get_data 
    End IfEnd Sub

  • RELEVANCY SCORE 2.60

    DB:2.60:Matching Data On Separate Worksheets a8


    I have data on 1 worksheet (called A) that I need to find duplicate matches on a separate worksheet (let's call B). Anyrecords on worksheet (A) not matched to (B) I'd like to put on a separate worksheet. All the data records are in the same workbook
    and I have a primary key to sort/filter/match with. any suggestions??

    DB:2.60:Matching Data On Separate Worksheets a8

    Hi,

    Thanks for posting in Microsoft Community.

    You may use VLOOKUP and FIND functions and see if it helps.

    Visit the following MS link for instructions on how to use VLOOKUP and FIND functions:

    VLOOKUP: http://support.microsoft.com/kb/324986

    FIND:http://office.microsoft.com/en-001/excel-help/find-findb-functions-HP010342526.aspx

    Hope the above information is helpful. Reply for further assistance.

    Thank You.

  • RELEVANCY SCORE 2.60

    DB:2.60:Form 6i 13


    Hi,

    I used Forms [32 Bit] Version 6.0.8.13.0 (Production).

    I need export data to excel, but....problem.

    1 - Change background color cells
    ...
    args := ole2.create_arglist;
    ole2.add_arg(args,lins);
    ole2.add_arg(args,cols);
    cell := ole2.get_obj_property(worksheet,'Cells',args);
    ole2.destroy_arglist(args);
    ole2.set_property(cell,'Value','JOMAR');
    ole2.set_property(cell,'ColorIndex',7);
    ole2.release_obj(cell);
    ranger := OLE2.GET_OBJ_PROPERTY( worksheet,'UsedRange');
    range_col := OLE2.GET_OBJ_PROPERTY( ranger,'Columns');
    OLE2.INVOKE( range_col,'AutoFit' );
    ...
    What's i failed?...

    2 - I need write char in cells A2:D2, How?
    ...
    ole2.add_arg(args,'A2:D2');
    cell := ole2.get_obj_property(worksheet,'Cells',args);
    ole2.destroy_arglist(args);
    ole2.set_property(cell,'Value','JOMAR RAMOJOMAR');
    ...
    It's no work.... any idea?

    3 - Example:
    A B C D
    -------------------------------------
    1 | | JOMAR | | RAMOJ |
    -------------------------------------
    2 | JOMAR RAMOJOMAR |
    -------------------------------------
    ...
    ole2.add_arg(args,'A2:D2');
    cell := ole2.get_obj_property(worksheet,'Cells',args);
    ole2.destroy_arglist(args);
    ole2.set_property(cell,'Value','JOMAR RAMOJOMAR');
    ...
    How format A2:D2 one col?...

    4 - Background process

    I create and open file excel after operations i quit aplication excel.
    I open taks manager and process excel running... why?

    5 - I looking for example and tutorial OLE2 and nothing...How i find?

    Regards

    Jomar

    [PRE]

    Message was edited by:
    Jomar

    DB:2.60:Form 6i 13

    I would suggest you to search over this forum.....there are multiple threads regarding this matter......

    Greetings...
    Sim

  • RELEVANCY SCORE 2.60

    DB:2.60:Locking Just Selected Cells In An Excel Worksheet 78


    I can lock cellsprotect (entire) Exccel worksheet, but can't seem to lock selected cells only. How do you do that?

    DB:2.60:Locking Just Selected Cells In An Excel Worksheet 78


    How to Lock Individual Cells in a Worksheet
    http://remoteitassist.com/helpdesk/knowledgebase.php?act=artarticle_id=2
    http://www.google.com/search?q=How+to+Lock+Individual+Cells+in+a+Worksheetrls=com.microsoft:en-auie=UTF-8oe=UTF-8startIndex=startPage=1

    This is an older MSKB, I wonder whether it still applies in Vista?

    XL: How to Lock Individual Cells in a Worksheet
    http://support.microsoft.com/kb/214081

  • RELEVANCY SCORE 2.60

    DB:2.60:How To Find All The Duplicate Entries And Combine Cell Date Values. 1s


    Hi,
    I've been doing this manually for ages... it would be great if someone can help...
    I have a ws with many columns and rows... I'm only interested in a few cells from each row though.
    Col A contains DeskNumber,
    Col D contains Start Date (when the desk is free)
    Col E contains End Date (when the desk is free)
    I'd like to find all entries for a particular desk in column A (there will be several for each desk), then pick up all the dates that the desk is free.. and copy this to another worksheet.
    thank you
    Angie

    DB:2.60:How To Find All The Duplicate Entries And Combine Cell Date Values. 1s

    Hello Angie,
    I suggest to give us some examples or to have a look here:
    http://www.sulprobil.com/html/listfreq.html
    Regards,
    Bernd

    http://www.sulprobil.com

  • RELEVANCY SCORE 2.59

    DB:2.59:Worksheet Function To Find Cells Without A Value? k7


    Hi coders,
    assume the following pseudocode: with A1:A1000, find cells wich do not contain ( not even as a substring) the ABC- pattern and return their amount ( how many of them are there in the range). Taking into account that I would like to put this as a cell's
    formula, whatwould it look like?

    DB:2.59:Worksheet Function To Find Cells Without A Value? k7

    Excellent! Works like a charm, thank you very much! Btw., this *ABC-* - what type regular expression is that and is it documented somewhere?

    Mac

  • RELEVANCY SCORE 2.59

    DB:2.59:How To Access The Array Inside ak


    public iterate(int ngens) {
    int p=0;
    int q=0;
    for(int n=0; nngens; n++){
    for(q=0; qn; q++){
    for(p=0; pm; p++){
    if(cells[(p-1+m)%m][(q+1+n)%n]==ALIVE){
    count++;
    }
    if(cells[p][(q+1+n)%n]==ALIVE){
    count++;
    }
    if(cells[(p+1+m)][(q+1+n)%n]==ALIVE){
    count++;
    }
    if(cells[(p-1+m)%m][q]==ALIVE){
    count++;
    }
    if(cells[(p+1+m)%m][q]==ALIVE){
    count++;
    }
    if(cells[(p-1+m)%m][(q-1+n)%n]==ALIVE){
    count++;
    }
    if(cells[p][(q-1+n)%n]==ALIVE){
    count++;
    }
    if(cells[(p+1+m)%m][(q+1+n)%n]==ALIVE){
    count++;
    }
    if(count==0||count==1){
    cells[p][q]=NOT_ALIVE;
    }
    if(count==2||count==3){
    cells[p][q]=ALIVE;
    }
    if(count=4){
    cells[p][q]=NOT_ALIVE;
    }
    if(count==3cells[p][q]==0){
    cells[p][q]=ALIVE;
    }

    }
    }
    }

    DB:2.59:How To Access The Array Inside ak

    What in the world are you asking?Anyway, this will be one of my favourite topics
    wonderful
    :-)

  • RELEVANCY SCORE 2.59

    DB:2.59:Excel 2007 Vba - How To Loop Through Cells And Insert Vlookup Function Based On Another Worksheet? dc


    Hello, how would it be written to loop through a range of cells based on a total record count (intRecordCount_all example below) and inserting a VLOOKUP function that referencesanother sheet within same workbook? Here is what I currently have:
    intRecordCount_temp = 0
    intRecordCount_all =297 '297 records were counted in prior steps say for example.
    For intRecordCount_temp = 1 To intRecordCount_all
    Worksheets(Sheet1).Cells(1 intRecordCount_temp, 6).Value = =VLOOKUP(' Worksheets(Sheet1).Cells(1 intRecordCount_temp,
    6).Offset(0, -5) ',Sheet2!A:I,2,FALSE)
    Next

    ...but it's displaying: Run-time error '1004': Application-defined or object-defined error

    Thanks in advance.

    DB:2.59:Excel 2007 Vba - How To Loop Through Cells And Insert Vlookup Function Based On Another Worksheet? dc

    Never mind, looks like it needed 2 double quotes within the formula's first parameter on both ends which now it works.

  • RELEVANCY SCORE 2.59

    DB:2.59:Return And Display All Values That Have Multiple Criteria 89


    I have data worksheet and a summary worksheet in the same workbook. I need to be able to return all the referral RN's that meet a specified criteria in the summary worksheet.

    In the summary worksheet in a range of cells, specifically column KI want to display all the Referral RN's from the Data worksheet in column E that are .16 and column B =1
    I need for this to be able to populate automatically - No filtering...

    Display Referral RN's in a rangeof cell in column K in the summary worksheet

    Criteria from the data worksheet
    Column E .16
    Column B =1

    Data worksheet

    A
    B
    C
    D
    E

    1
    Referral RN
    Rework Count
    TAT Point
    TAT
    NET

    2
    1
    1
    Benefits
    0.5
    0.88

    3
    2
    0
    Benefits
    0.7
    0.4

    4
    3
    1
    Benefits
    1
    1.2

    5
    4
    2
    Benefits
    1.5
    0.1

    6
    5
    1
    Benefits
    0.63
    0.54

    7
    6
    0
    Benefits
    0.47
    0.4

    8
    7
    0
    Benefits
    2.1
    0.7

    9
    8
    1
    Benefits
    0.16
    0.16

    10
    9
    0
    Benefits
    0.06
    0.01

    11
    10
    0
    Benefits
    0.14
    0.08

    Thanks for your help! I hope I have explained this well. Please let me know if I need to clarify.

    Marc

    DB:2.59:Return And Display All Values That Have Multiple Criteria 89

    That does help, but since you already have an answer, I won't bother with the macro solution.

  • RELEVANCY SCORE 2.59

    DB:2.59:Locking And Protecting Specific Cells sp


    I unprotect my worksheet, unlock all cells, select specific cells, lock them, then protect the worksheet again. Although I can enter data into unlocked cells, I cannot format them in any way with color or alignment or font changes.
    Is there a way to protect only my formula cells and still be able to format unlocked cells on the sheet?

    DB:2.59:Locking And Protecting Specific Cells sp

    Your welcome and thanks for the feedback

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

  • RELEVANCY SCORE 2.59

    DB:2.59:Count Cells According To Rgb Values 8m


    Need a formula to count cells in a range according toRGB color settings.

  • RELEVANCY SCORE 2.59

    DB:2.59:Vba - If Statement Doesnt Work ss


    Hello,
    First of all, let me tell you that I'm new to VBA.
    I'm about to collect some data from a range in a worksheet to another range in another worksheet.
    For i = 7 To 69
    For j = 2 To 8
    If Sheet2.Cells(i, 1).Value = Sheet6.Cells(j, 7).Value Then
    If Sheet6.Cells(j, 13).Value X Then
    Sheet2.Cells(i, 9).Value = Sheet6.Cells(j, 12).Value
    End If
    End If
    Next j
    Next i

    The problem is the values withSheet6.Cells(j, 13).Value = X are restored too.
    Can I find any help, please?

    Kind regards,
    Achraf

    DB:2.59:Vba - If Statement Doesnt Work ss

    Your code works fine - here is an example file:
    http://1drv.ms/1j0kwO0
    Your problem may be that you are using the sheet codenames to directly address the sheet objects, where you may mean to use the name on the tab
    Sheet2.Cells(....
    may be using a different sheet than
    Worksheets(Sheet2).Cells(....
    In your project explorer, see what the code name is versus the sheet name - the syntax is Codename (Sheet name)

    In this example (changed from the uploaded file) Sheet2 has the codename Sheet1, so in VBA, it would be
    Sheet1.Cells(....
    or
    Worksheets(Sheet2).Cells(....

  • RELEVANCY SCORE 2.59

    DB:2.59:Is It Possible To Format A Cell In One Worksheet And For This To Automatically Format Cells In A Second Sheet 87


    I am giving cells a colour depending on the entry and need to duplicate this in a second sheet - I don't want to have to copy and paste each time. I am using Excel 2003.

    I have used a formula to copy the entry from sheet one to sheet two, but can't work out how to copy the colour of the cell as well?

    I have tried conditional formatting, but there are 5 conditions which means this will not work as I can only specify three conditions.

    Thanks

    Phil

  • RELEVANCY SCORE 2.59

    DB:2.59:Count Colored Non-Blank Cells sp


    Can some one help me to see how I can count specific colored non-blanl cells?

    - Siddisi

    DB:2.59:Count Colored Non-Blank Cells sp


    Can some one help me to see how I can count specific colored non-blanl cells?

    - Siddisi

    Hi,

    No excel version to date can count/sum coloured cells so we need a UDF. In addition it depends on how the colour is derived. If it's a simple fill colour then this code will do it. If it's as a result of conditional formatting then that's more difficult
    but dooable. I've assumed here we're talking about a fill colour and NOT conditional formatting.

    ALT+F11 to open VB editor. right click 'thisworkbook' and insert mosule and paste the code below in. Close VB editor.

    Back on the worksheet call with

    =CountColour(A1:A20,B1)

    Wher A1:A20 is the range to count and B1 is a cell with the same fill colour as you want to count

    Function CountColour(rng As Range, clr As Range)
    Dim c As Range
    For Each c In rng
    If c.Interior.ColorIndex = clr.Interior.ColorIndex And _
    Len(c) 0 Then
    CountColour = CountColour + 1
    End If
    Next
    End Function

  • RELEVANCY SCORE 2.59

    DB:2.59:Move Focus Between Worksheet Edit Area And Results Pane jp


    Hi,

    Is there a keyboard shortcut key to move focus into Worksheet Edit Area and the Results Tab for the SQL wroksheet ? (not using Mouse)
    When I run the query in SQL worksheet I have to mouse click on the one of the cells in the Results grid and then scroll to check the resulting records (and vice versa to edit the query), instead if there is a keyboard shortcut that could be used to move focus would increase the efficiency.

    I tried searching for it but could not find any.

    Thanks

    DB:2.59:Move Focus Between Worksheet Edit Area And Results Pane jp

    Sheez, 2 people gave the same answer here... and you still miss it???

    K.

  • RELEVANCY SCORE 2.58

    DB:2.58:Re: How To Write The Query To Find The Duplicate Rows? s7


    If you just want the duplicate ITEM_NO values,

    SELECT item_no
    FROM item
    GROUP BY item_no
    HAVING COUNT(*) 1Justin

    DB:2.58:Re: How To Write The Query To Find The Duplicate Rows? s7

    See Dublicates thread which discusses on deleting duplicate records.

    Thanks,
    Karthick.

  • RELEVANCY SCORE 2.58

    DB:2.58:Cannot Get Findfindnext Routine To Work When Referencing Another Worksheet am


    i have the following find routine that works fine returning the proper results. However, When I change the reference to look at a range on another sheet the variable foundcell is always set to nothing. There is valid data in the range I am referencing.
    The only change is pointing to another sheet. Am I referencing the sheet incorrectly? I have enclosed the 2 versions of the routine. The first references the same sheet and works fine the second references another sheet and returns no values.
    Thanks,
    Dan
    Version #1 (works fine)
    sub macro7(v1 as double,v2 as double)
    dim foundcell as range
    dim lastcell as range
    dim firstaddr as string
    dim z as integer
    with range("BI4:BI75")
    set lastcell = .cells(.cells(.count)
    end with
    set foundcell = range("BI4:BI75").find(what:="300",after:=lastcell)
    if not foundcell is nothing then
    firstaddr = foundcell.address
    end if
    do until foundcell is nothing
    set foundcell = range("BI4:BI74").FindNext(after:=foundcell)
    if foundcell.address = firstaddr then
    exit do
    end if
    loop
    end sub

    Version #2 (Problem version)
    sub macro7(v1 as double,v2 as double)
    dim foundcell as range
    dim lastcell as range
    dim firstaddr as string
    dim z as integer
    with sheets("AAPL").range("BI4:BI75") '
    This seems to be problem referencing other sheet
    set lastcell = .cells(.cells(.count)
    end with
    set foundcell = sheets("AAPL").range("BI4:BI75").find(what:="300",after:=lastcell) 'Problem here

    if not foundcell is nothing then
    firstaddr = foundcell.address
    end if
    do until foundcell is nothing
    set foundcell = sheets("AAPL").range("BI4:BI74").FindNext(after:=foundcell) 'Problem here

    if foundcell.address = firstaddr then
    exit do
    end if
    loop
    end sub

    DB:2.58:Cannot Get Findfindnext Routine To Work When Referencing Another Worksheet am

    thanks. Solves the problem. As this was my first find routine, I obviously need to learn the parameters better.

    thanks again,
    Dan

  • RELEVANCY SCORE 2.58

    DB:2.58:How To Count Datediff? p8


    Hi,
    I have a column titled Statusand a column titled
    Date of Request.I would liketo count and input to a cell in another worksheet if any cells in
    Statuschanges to PendingAND if the datediff between anycells in
    Date of Requestand today's day is less than 3 days. Is there a code to do this? Thanks in advance!

    DB:2.58:How To Count Datediff? p8

    Thank you Hans, I really appreciate your help.

  • RELEVANCY SCORE 2.58

    DB:2.58:Excel Conditional Formatting - Highlight Duplicates And Suppress Empty Cells And Zeros 1d


    I'm trying to create a Conditional Format that highlights duplicate entries in a worksheet.

    DB:2.58:Excel Conditional Formatting - Highlight Duplicates And Suppress Empty Cells And Zeros 1d

    The problem was when I used a Fill color with the formula I created empty cells were considered duplicates and received the Fill color.

  • RELEVANCY SCORE 2.57

    DB:2.57:Excel 2010 - Display Column Width Of Each Non-Blank Column In Row Two Of Worksheet p8


    I would like to display the column width in row two of a worksheetof all row one non-blank cells. For example, cell A1 through E1 contains data.
    I would like to display the column width of each column in cells A2 through E2.

    If cells A1 through AJ1 are non-blank cells, I would like to display the column width of columnsA through AJ in row 2.

    Thank you for your helpwith this.

    full-count

    DB:2.57:Excel 2010 - Display Column Width Of Each Non-Blank Column In Row Two Of Worksheet p8

    Hi,

    In cell A2, enter this formula and copy to the right.

    =IF(A1"",CELL("width",A1),"")

    Please note that if you change the column width, you will have to press F9 to get the revised figure in cell A2.

    Hope this helps.

  • RELEVANCY SCORE 2.57

    DB:2.57:Userform Populate Fields fx


    I have set up a userform that I use to enter sales data, what I would like to do is be able to use this same form to look up data already entered on the worksheet "sales", then make any changes, then update the workbook or add a new product projected sales
    data. I am not sure how to change the code to do this. I am fairly new to VBA coding I have tried searching the forum, but i haven't come up with the answer. (I have used this format based it to suit my situation from a Userform i downloaded from
    www.contextures.com)
    Once a cboRegion has been selected I would like to populate cboProduct1, cboProduct2 etc with products that have sales for the cboRegion selectedalong with populating txtJuly1, txtAugIn1, txtSept1etc etc.
    I know this seems like a lot of code below but a lot of it is repeated I just need you to give me an example to follow that would be great.
    Here is a screenshot of my userformhttp://cid-9b0f0d5afa4a0fd5.photos.live.com/self.aspx/Forum%20Screenshots/UserForm.jpg
    Here is a screenshot of my worksheet "Sales"
    http://cid-9b0f0d5afa4a0fd5.photos.live.com/self.aspx/Forum%20Screenshots/UserForm.jpg#resId/9B0F0D5AFA4A0FD5!361
    Looking forward to your suggestions:

    Option Explicit
    Private Sub cboProduct1_Change()
    End Sub
    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 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 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

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

    lPart2 = Me.cboProduct2.ListIndex
    'find third empty row in database
    lRow3 = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(3, 0).Row

    lPart3 = Me.cboProduct3.ListIndex
    'find forth empty row in database
    lRow4 = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(4, 0).Row

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

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

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

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

    lPart8 = Me.cboProduct8.ListIndex
    'find ninth empty row in database
    lRow9 = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(9, 0).Row

    lPart9 = Me.cboProduct9.ListIndex
    'find tenth empty row in database
    lRow10 = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(10, 0).Row

    lPart10 = Me.cboProduct10.ListIndex
    'check for a part number
    If Trim(Me.cboProduct1.Value) = "" Then
    Me.cboProduct1.SetFocus
    MsgBox "Please enter a product number"
    Exit Sub
    End If
    'copy the data to the database

    With ws
    If Me.cboProduct1.ListIndex -1 Then
    .Cells(lRow, 2).Value = Me.cboProduct1.Value
    .Cells(lRow, 18).Value = Me.cboProduct1.List(lPart, 1)
    .Cells(lRow, 1).Value = Me.cboRegion.Value
    .Cells(lRow, 3).Value = Me.txtJuly1.Value
    .Cells(lRow, 4).Value = Me.txtAug1.Value
    .Cells(lRow, 5).Value = Me.txtSept1.Value
    .Cells(lRow, 6).Value = Me.txtOct1.Value
    .Cells(lRow, 7).Value = Me.txtNov1.Value
    .Cells(lRow, 8).Value = Me.txtDec1.Value
    .Cells(lRow, 9).Value = Me.txtJan1.Value
    .Cells(lRow, 10).Value = Me.txtFeb1.Value
    .Cells(lRow, 11).Value = Me.txtMar1.Value
    .Cells(lRow, 12).Value = Me.txtApr1.Value
    .Cells(lRow, 13).Value = Me.txtMay1.Value
    .Cells(lRow, 14).Value = Me.txtJune1.Value
    .Cells(lRow, 17).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct2.ListIndex -1 Then
    .Cells(lRow2, 2).Value = Me.cboProduct2.Value
    .Cells(lRow2, 4).Value = Me.cboProduct2.List(lPart2, 1)
    .Cells(lRow2, 1).Value = Me.cboRegion.Value
    .Cells(lRow2, 7).Value = Me.txtJuly2.Value
    .Cells(lRow2, 8).Value = Me.txtAug2.Value
    .Cells(lRow2, 9).Value = Me.txtSept2.Value
    .Cells(lRow2, 10).Value = Me.txtOct2.Value
    .Cells(lRow2, 11).Value = Me.txtNov2.Value
    .Cells(lRow2, 12).Value = Me.txtDec2.Value
    .Cells(lRow2, 13).Value = Me.txtJan2.Value
    .Cells(lRow2, 14).Value = Me.txtFeb2.Value
    .Cells(lRow2, 15).Value = Me.txtMar2.Value
    .Cells(lRow2, 16).Value = Me.txtApr2.Value
    .Cells(lRow2, 17).Value = Me.txtMay2.Value
    .Cells(lRow2, 18).Value = Me.txtJune2.Value
    .Cells(lRow2, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct3.ListIndex -1 Then
    .Cells(lRow3, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow3, 3).Value = Me.cboProduct3.Value
    .Cells(lRow3, 4).Value = Me.cboProduct1.List(lPart3, 1)
    .Cells(lRow3, 5).Value = Me.cboRegion.Value
    .Cells(lRow3, 7).Value = Me.txtJuly3.Value
    .Cells(lRow3, 8).Value = Me.txtAug3.Value
    .Cells(lRow3, 9).Value = Me.txtSept3.Value
    .Cells(lRow3, 10).Value = Me.txtOct3.Value
    .Cells(lRow3, 11).Value = Me.txtNov3.Value
    .Cells(lRow3, 12).Value = Me.txtDec3.Value
    .Cells(lRow3, 13).Value = Me.txtJan3.Value
    .Cells(lRow3, 14).Value = Me.txtFeb3.Value
    .Cells(lRow3, 15).Value = Me.txtMar3.Value
    .Cells(lRow3, 16).Value = Me.txtApr3.Value
    .Cells(lRow3, 17).Value = Me.txtMay3.Value
    .Cells(lRow3, 18).Value = Me.txtJune3.Value
    .Cells(lRow3, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct4.ListIndex -1 Then
    .Cells(lRow4, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow4, 3).Value = Me.cboProduct4.Value
    .Cells(lRow4, 4).Value = Me.cboProduct1.List(lPart4, 1)
    .Cells(lRow4, 5).Value = Me.cboRegion.Value
    .Cells(lRow4, 7).Value = Me.txtJuly4.Value
    .Cells(lRow4, 8).Value = Me.txtAug4.Value
    .Cells(lRow4, 9).Value = Me.txtSept4.Value
    .Cells(lRow4, 10).Value = Me.txtOct4.Value
    .Cells(lRow4, 11).Value = Me.txtNov4.Value
    .Cells(lRow4, 12).Value = Me.txtDec4.Value
    .Cells(lRow4, 13).Value = Me.txtJan4.Value
    .Cells(lRow4, 14).Value = Me.txtFeb4.Value
    .Cells(lRow4, 15).Value = Me.txtMar4.Value
    .Cells(lRow4, 16).Value = Me.txtApr4.Value
    .Cells(lRow4, 17).Value = Me.txtMay4.Value
    .Cells(lRow4, 18).Value = Me.txtJune4.Value
    .Cells(lRow4, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct5.ListIndex -1 Then
    .Cells(lRow5, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow5, 3).Value = Me.cboProduct5.Value
    .Cells(lRow5, 4).Value = Me.cboProduct1.List(lPart5, 1)
    .Cells(lRow5, 5).Value = Me.cboRegion.Value
    .Cells(lRow5, 7).Value = Me.txtJuly5.Value
    .Cells(lRow5, 8).Value = Me.txtAug5.Value
    .Cells(lRow5, 9).Value = Me.txtSept5.Value
    .Cells(lRow5, 10).Value = Me.txtOct5.Value
    .Cells(lRow5, 11).Value = Me.txtNov5.Value
    .Cells(lRow5, 12).Value = Me.txtDec5.Value
    .Cells(lRow5, 13).Value = Me.txtJan5.Value
    .Cells(lRow5, 14).Value = Me.txtFeb5.Value
    .Cells(lRow5, 15).Value = Me.txtMar5.Value
    .Cells(lRow5, 16).Value = Me.txtApr5.Value
    .Cells(lRow5, 17).Value = Me.txtMay5.Value
    .Cells(lRow5, 18).Value = Me.txtJune5.Value
    .Cells(lRow5, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct6.ListIndex -1 Then
    .Cells(lRow6, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow6, 3).Value = Me.cboProduct6.Value
    .Cells(lRow6, 4).Value = Me.cboProduct1.List(lPart6, 1)
    .Cells(lRow6, 5).Value = Me.cboRegion.Value
    .Cells(lRow6, 7).Value = Me.txtJuly6.Value
    .Cells(lRow6, 8).Value = Me.txtAug6.Value
    .Cells(lRow6, 9).Value = Me.txtSept6.Value
    .Cells(lRow6, 10).Value = Me.txtOct6.Value
    .Cells(lRow6, 11).Value = Me.txtNov6.Value
    .Cells(lRow6, 12).Value = Me.txtDec6.Value
    .Cells(lRow6, 13).Value = Me.txtJan6.Value
    .Cells(lRow6, 14).Value = Me.txtFeb6.Value
    .Cells(lRow6, 15).Value = Me.txtMar6.Value
    .Cells(lRow6, 16).Value = Me.txtApr6.Value
    .Cells(lRow6, 17).Value = Me.txtMay6.Value
    .Cells(lRow6, 18).Value = Me.txtJune6.Value
    .Cells(lRow6, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct7.ListIndex -1 Then
    .Cells(lRow7, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow7, 3).Value = Me.cboProduct7.Value
    .Cells(lRow7, 4).Value = Me.cboProduct1.List(lPart7, 1)
    .Cells(lRow7, 5).Value = Me.cboRegion.Value
    .Cells(lRow7, 7).Value = Me.txtJuly7.Value
    .Cells(lRow7, 8).Value = Me.txtAug7.Value
    .Cells(lRow7, 9).Value = Me.txtSept7.Value
    .Cells(lRow7, 10).Value = Me.txtOct7.Value
    .Cells(lRow7, 11).Value = Me.txtNov7.Value
    .Cells(lRow7, 12).Value = Me.txtDec7.Value
    .Cells(lRow7, 13).Value = Me.txtJan7.Value
    .Cells(lRow7, 14).Value = Me.txtFeb7.Value
    .Cells(lRow7, 15).Value = Me.txtMar7.Value
    .Cells(lRow7, 16).Value = Me.txtApr7.Value
    .Cells(lRow7, 17).Value = Me.txtMay7.Value
    .Cells(lRow7, 18).Value = Me.txtJune7.Value
    .Cells(lRow7, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct8.ListIndex -1 Then
    .Cells(lRow8, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow8, 3).Value = Me.cboProduct8.Value
    .Cells(lRow8, 4).Value = Me.cboProduct1.List(lPart8, 1)
    .Cells(lRow8, 5).Value = Me.cboRegion.Value
    .Cells(lRow8, 7).Value = Me.txtJuly8.Value
    .Cells(lRow8, 8).Value = Me.txtAug8.Value
    .Cells(lRow8, 9).Value = Me.txtSept8.Value
    .Cells(lRow8, 10).Value = Me.txtOct8.Value
    .Cells(lRow8, 11).Value = Me.txtNov8.Value
    .Cells(lRow8, 12).Value = Me.txtDec8.Value
    .Cells(lRow8, 13).Value = Me.txtJan8.Value
    .Cells(lRow8, 14).Value = Me.txtFeb8.Value
    .Cells(lRow8, 15).Value = Me.txtMar8.Value
    .Cells(lRow8, 16).Value = Me.txtApr8.Value
    .Cells(lRow8, 17).Value = Me.txtMay8.Value
    .Cells(lRow8, 18).Value = Me.txtJune8.Value
    .Cells(lRow8, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct9.ListIndex -1 Then
    .Cells(lRow9, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow9, 3).Value = Me.cboProduct9.Value
    .Cells(lRow9, 4).Value = Me.cboProduct1.List(lPart9, 1)
    .Cells(lRow9, 5).Value = Me.cboRegion.Value
    .Cells(lRow9, 7).Value = Me.txtJuly9.Value
    .Cells(lRow9, 8).Value = Me.txtAug9.Value
    .Cells(lRow9, 9).Value = Me.txtSept9.Value
    .Cells(lRow9, 10).Value = Me.txtOct9.Value
    .Cells(lRow9, 11).Value = Me.txtNov9.Value
    .Cells(lRow9, 12).Value = Me.txtDec9.Value
    .Cells(lRow9, 13).Value = Me.txtJan9.Value
    .Cells(lRow9, 14).Value = Me.txtFeb9.Value
    .Cells(lRow9, 15).Value = Me.txtMar9.Value
    .Cells(lRow9, 16).Value = Me.txtApr9.Value
    .Cells(lRow9, 17).Value = Me.txtMay9.Value
    .Cells(lRow9, 18).Value = Me.txtJune9.Value
    .Cells(lRow9, 20).Value = Me.txtOtherInfo.Value
    End If
    If Me.cboProduct10.ListIndex -1 Then
    .Cells(lRow10, 1).Value = CDate(Me.txtDate.Value)
    .Cells(lRow10, 3).Value = Me.cboProduct10.Value
    .Cells(lRow10, 4).Value = Me.cboProduct1.List(lPart10, 1)
    .Cells(lRow10, 5).Value = Me.cboRegion.Value
    .Cells(lRow10, 7).Value = Me.txtJuly10.Value
    .Cells(lRow10, 8).Value = Me.txtAug10.Value
    .Cells(lRow10, 9).Value = Me.txtSept10.Value
    .Cells(lRow10, 10).Value = Me.txtOct10.Value
    .Cells(lRow10, 11).Value = Me.txtNov10.Value
    .Cells(lRow10, 12).Value = Me.txtDec10.Value
    .Cells(lRow10, 13).Value = Me.txtJan10.Value
    .Cells(lRow10, 14).Value = Me.txtFeb10.Value
    .Cells(lRow10, 15).Value = Me.txtMar10.Value
    .Cells(lRow10, 16).Value = Me.txtApr10.Value
    .Cells(lRow10, 17).Value = Me.txtMay10.Value
    .Cells(lRow10, 18).Value = Me.txtJune10.Value
    .Cells(lRow10, 20).Value = Me.txtOtherInfo.Value
    End If
    End With

    'clear the data
    Me.cboProduct1.Value = ""
    Me.cboProduct2.Value = ""
    Me.cboProduct3.Value = ""
    Me.cboProduct4.Value = ""
    Me.cboProduct5.Value = ""
    Me.cboProduct6.Value = ""
    Me.cboProduct7.Value = ""
    Me.cboProduct8.Value = ""
    Me.cboProduct9.Value = ""
    Me.cboProduct10.Value = ""
    Me.cboRegion.Value = ""
    Me.txtDate.Value = Format(Date, "Short Date")
    Me.txtJuly1.Value = ""
    Me.txtJuly2.Value = ""
    Me.txtJuly3.Value = ""
    Me.txtJuly4.Value = ""
    Me.txtJuly5.Value = ""
    Me.txtJuly6.Value = ""
    Me.txtJuly7.Value = ""
    Me.txtJuly8.Value = ""
    Me.txtJuly9.Value = ""
    Me.txtJuly10.Value = ""
    Me.txtAug1.Value = ""
    Me.txtAug2.Value = ""
    Me.txtAug3.Value = ""
    Me.txtAug4.Value = ""
    Me.txtAug5.Value = ""
    Me.txtAug6.Value = ""
    Me.txtAug7.Value = ""
    Me.txtAug8.Value = ""
    Me.txtAug9.Value = ""
    Me.txtAug10.Value = ""
    Me.txtSept1.Value = ""
    Me.txtSept2.Value = ""
    Me.txtSept3.Value = ""
    Me.txtSept4.Value = ""
    Me.txtSept5.Value = ""
    Me.txtSept6.Value = ""
    Me.txtSept7.Value = ""
    Me.txtSept8.Value = ""
    Me.txtSept9.Value = ""
    Me.txtSept10.Value = ""
    Me.txtOct1.Value = ""
    Me.txtOct2.Value = ""
    Me.txtOct3.Value = ""
    Me.txtOct4.Value = ""
    Me.txtOct5.Value = ""
    Me.txtOct6.Value = ""
    Me.txtOct7.Value = ""
    Me.txtOct8.Value = ""
    Me.txtOct9.Value = ""
    Me.txtOct10.Value = ""
    Me.txtNov1.Value = ""
    Me.txtNov2.Value = ""
    Me.txtNov3.Value = ""
    Me.txtNov4.Value = ""
    Me.txtNov5.Value = ""
    Me.txtNov6.Value = ""
    Me.txtNov7.Value = ""
    Me.txtNov8.Value = ""
    Me.txtNov9.Value = ""
    Me.txtNov10.Value = ""
    Me.txtDec1.Value = ""
    Me.txtDec2.Value = ""
    Me.txtDec3.Value = ""
    Me.txtDec4.Value = ""
    Me.txtDec5.Value = ""
    Me.txtDec6.Value = ""
    Me.txtDec7.Value = ""
    Me.txtDec8.Value = ""
    Me.txtDec9.Value = ""
    Me.txtDec10.Value = ""
    Me.txtJan1.Value = ""
    Me.txtJan2.Value = ""
    Me.txtJan3.Value = ""
    Me.txtJan4.Value = ""
    Me.txtJan5.Value = ""
    Me.txtJan6.Value = ""
    Me.txtJan7.Value = ""
    Me.txtJan8.Value = ""
    Me.txtJan9.Value = ""
    Me.txtJan10.Value = ""
    Me.txtFeb1.Value = ""
    Me.txtFeb2.Value = ""
    Me.txtFeb3.Value = ""
    Me.txtFeb4.Value = ""
    Me.txtFeb5.Value = ""
    Me.txtFeb6.Value = ""
    Me.txtFeb7.Value = ""
    Me.txtFeb8.Value = ""
    Me.txtFeb9.Value = ""
    Me.txtFeb10.Value = ""
    Me.txtMar1.Value = ""
    Me.txtMar2.Value = ""
    Me.txtMar3.Value = ""
    Me.txtMar4.Value = ""
    Me.txtMar5.Value = ""
    Me.txtMar6.Value = ""
    Me.txtMar7.Value = ""
    Me.txtMar8.Value = ""
    Me.txtMar9.Value = ""
    Me.txtMar10.Value = ""
    Me.txtApr1.Value = ""
    Me.txtApr2.Value = ""
    Me.txtApr3.Value = ""
    Me.txtApr4.Value = ""
    Me.txtApr5.Value = ""
    Me.txtApr6.Value = ""
    Me.txtApr7.Value = ""
    Me.txtApr8.Value = ""
    Me.txtApr9.Value = ""
    Me.txtApr10.Value = ""
    Me.txtMay1.Value = ""
    Me.txtMay2.Value = ""
    Me.txtMay3.Value = ""
    Me.txtMay4.Value = ""
    Me.txtMay5.Value = ""
    Me.txtMay6.Value = ""
    Me.txtMay7.Value = ""
    Me.txtMay8.Value = ""
    Me.txtMay9.Value = ""
    Me.txtMay10.Value = ""
    Me.txtJune1.Value = ""
    Me.txtJune2.Value = ""
    Me.txtJune3.Value = ""
    Me.txtJune4.Value = ""
    Me.txtJune5.Value = ""
    Me.txtJune6.Value = ""
    Me.txtJune7.Value = ""
    Me.txtJune8.Value = ""
    Me.txtJune9.Value = ""
    Me.txtJune10.Value = ""
    Me.txtOtherInfo.Value = ""
    Me.txtDate.SetFocus
    End Sub
    Private Sub cmdCLOSE_click()
    Unload Me
    End Sub
    Private Sub Label1_Click()
    End SubPrivate Sub ComboBox1_Change()
    End Sub
    Private Sub Label5_Click()
    End Sub
    Private Sub Label7_Click()
    End SubPrivate Sub lblCaseIn_Click()
    End Sub
    Private Sub lblWHTrans_Click()
    End Sub
    Private Sub txtDate_Change()
    End SubPrivate Sub UserForm_initialize()
    Dim cPart 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.cboProduct1
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct2
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct3
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct4
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPartFor Each cPart In ws.Range("PartIDList")
    With Me.cboProduct5
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct6
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct7
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPartFor Each cPart In ws.Range("PartIDList")
    With Me.cboProduct8
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct9
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cPart In ws.Range("PartIDList")
    With Me.cboProduct10
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
    Next cPart
    For Each cLoc In ws.Range("LocationList")
    With Me.cboRegion
    .AddItem cLoc.Value
    .List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
    End With
    Next cLoc

    Me.txtDate.Value = Format(Date, "Short Date")
    Me.cboRegion.Value = ""
    Me.txtJuly1.Value = ""
    Me.txtAug1.Value = ""
    Me.txtSept1.Value = ""
    Me.txtOct1.Value = ""
    Me.txtNov1.Value = ""
    Me.txtDec1.Value = ""
    Me.txtJan1.Value = ""
    Me.txtFeb1.Value = ""
    Me.txtMar1.Value = ""
    Me.txtApr1.Value = ""
    Me.txtMay1.Value = ""
    Me.txtJune1.Value = ""
    Me.txtJuly2.Value = ""
    Me.txtAug2.Value = ""
    Me.txtSept2.Value = ""
    Me.txtOct2.Value = ""
    Me.txtNov2.Value = ""
    Me.txtDec2.Value = ""
    Me.txtJan2.Value = ""
    Me.txtFeb2.Value = ""
    Me.txtMar2.Value = ""
    Me.txtApr2.Value = ""
    Me.txtMay2.Value = ""
    Me.txtJune2.Value = ""
    Me.txtJuly3.Value = ""
    Me.txtAug3.Value = ""
    Me.txtSept3.Value = ""
    Me.txtOct3.Value = ""
    Me.txtNov3.Value = ""
    Me.txtDec3.Value = ""
    Me.txtJan3.Value = ""
    Me.txtFeb3.Value = ""
    Me.txtMar3.Value = ""
    Me.txtApr3.Value = ""
    Me.txtMay3.Value = ""
    Me.txtJune3.Value = ""
    Me.txtJuly4.Value = ""
    Me.txtAug4.Value = ""
    Me.txtSept4.Value = ""
    Me.txtOct4.Value = ""
    Me.txtNov4.Value = ""
    Me.txtDec4.Value = ""
    Me.txtJan4.Value = ""
    Me.txtFeb4.Value = ""
    Me.txtMar4.Value = ""
    Me.txtApr4.Value = ""
    Me.txtMay4.Value = ""
    Me.txtJune4.Value = ""
    Me.txtJuly5.Value = ""
    Me.txtAug5.Value = ""
    Me.txtSept5.Value = ""
    Me.txtOct5.Value = ""
    Me.txtNov5.Value = ""
    Me.txtDec5.Value = ""
    Me.txtJan5.Value = ""
    Me.txtFeb5.Value = ""
    Me.txtMar5.Value = ""
    Me.txtApr5.Value = ""
    Me.txtMay5.Value = ""
    Me.txtJune5.Value = ""
    Me.txtJuly6.Value = ""
    Me.txtAug6.Value = ""
    Me.txtSept6.Value = ""
    Me.txtOct6.Value = ""
    Me.txtNov6.Value = ""
    Me.txtDec6.Value = ""
    Me.txtJan6.Value = ""
    Me.txtFeb6.Value = ""
    Me.txtMar6.Value = ""
    Me.txtApr6.Value = ""
    Me.txtMay6.Value = ""
    Me.txtJune6.Value = ""
    Me.txtJuly7.Value = ""
    Me.txtAug7.Value = ""
    Me.txtSept7.Value = ""
    Me.txtOct7.Value = ""
    Me.txtNov7.Value = ""
    Me.txtDec7.Value = ""
    Me.txtJan7.Value = ""
    Me.txtFeb7.Value = ""
    Me.txtMar7.Value = ""
    Me.txtApr7.Value = ""
    Me.txtMay7.Value = ""
    Me.txtJune7.Value = ""
    Me.txtJuly8.Value = ""
    Me.txtAug8.Value = ""
    Me.txtSept8.Value = ""
    Me.txtOct8.Value = ""
    Me.txtNov8.Value = ""
    Me.txtDec8.Value = ""
    Me.txtJan8.Value = ""
    Me.txtFeb8.Value = ""
    Me.txtMar8.Value = ""
    Me.txtApr8.Value = ""
    Me.txtMay8.Value = ""
    Me.txtJune8.Value = ""
    Me.txtJuly9.Value = ""
    Me.txtAug9.Value = ""
    Me.txtSept9.Value = ""
    Me.txtOct9.Value = ""
    Me.txtNov9.Value = ""
    Me.txtDec9.Value = ""
    Me.txtJan9.Value = ""
    Me.txtFeb9.Value = ""
    Me.txtMar9.Value = ""
    Me.txtApr9.Value = ""
    Me.txtMay9.Value = ""
    Me.txtJune9.Value = ""
    Me.txtJuly10.Value = ""
    Me.txtAug10.Value = ""
    Me.txtSept10.Value = ""
    Me.txtOct10.Value = ""
    Me.txtNov10.Value = ""
    Me.txtDec10.Value = ""
    Me.txtJan10.Value = ""
    Me.txtFeb10.Value = ""
    Me.txtMar10.Value = ""
    Me.txtApr10.Value = ""
    Me.txtMay10.Value = ""
    Me.txtJune10.Value = ""
    Me.txtJuly10.Value = ""
    Me.txtOtherInfo.Value = ""
    Me.cboRegion.SetFocus
    End Sub

    DB:2.57:Userform Populate Fields fx

    Thanks Andreas, you have gone out of your way to help me, after sending you the file your reviewed the code changed it to get it to do exactly as I wanted. You are marvellous!!!
    Deb.

  • RELEVANCY SCORE 2.57

    DB:2.57:Find Duplicate Rows Based On Two Colums And Transfer Results Into Another Worksheet c1


    Hi,
    I have a worksheet (Saisie de Données) containingdata in column B andD that are sometime duplicates. I would like to be able to recognize these duplicates and sum the data in the columnsG to V. The result would then be transfer
    to another worksheet (Sommaire -Paie)that will receive the non-duplicate rows with their related data and the duplicate row with the sum results. All the columns remains the same between the two worksheet except for the column C that
    is not copied to the new worksheet. Everytime the macro will be launched, the data in thesecond worksheet (Sommaire - Paie) will be overwritten.
    I attached a copy of the worksheet with the data to be analysed (Saisie de Données) and the espected result (Sommaire - Paie) that I created manually.

    To get the attached file, follow the dropbox link:
    https://
    dl.dropbox.com/u/6307588/Promec%20-%20Rapport%20Heures%20-%20%282012-09-16%20au%202012-09-22%29%20-%20Example.xlsm
    In the real workbook, there is a lot more line, but it's always the same pattern, the worker's name with the hours he did during the week...
    Thanks!!!

  • RELEVANCY SCORE 2.57

    DB:2.57:I Need Help To Modify This Code For Comparison Report. sm


    Hello,
    The code is work really good to compare QTY of same part number from 2 database, and I like to change it to comparing ID number make sure they are match.With this code it does bring in ID of single part number, but if there is part number with multiple
    ID number then macro will add all of them up. Can you help please? I have been try tofix it but no luck:(( Here are basic information on those sheets.

    Vendor Sheet
    A B D F G
    Level Part Number Part Desc Qty ID number

    MasterSheet
    A BEI H
    Level Part Number Part Desc Qty ID number

    Report Sheet
    C D E G K L
    Master PN Master desc Master IdVendor IDVendor PN Vendor Desc

    And here is the code.

    Sub CompareReport()
    Dim Nump1 As Long
    Dim Level As Variant 'Assembly Level
    Dim Vendpart As Variant 'part number
    Dim Vendpartnum() As String 'part number
    Dim VendID As Variant 'part Find Number
    Dim Vendquan() As Long
    Dim description As Variant 'part description
    Dim VenDescription() As String 'part description
    Dim Nump2 As Long
    Dim MasterPN As Variant 'Master Part Number
    Dim MasterDes As Variant 'Master description
    Dim MasterID As Variant 'Master ID_Number
    Dim pos1, pos7 As Variant
    Dim i, j, k, x, y As Integer
    '==================================================================================================
    On Error GoTo ErrHandler
    'VALIDATIONS---------------------------------------------------------------------------------------
    'Check if data exists
    If VendorSht.Cells(2, 1).Value = "" Then
    MsgBox "Data not found in Vendor Date tab." vbCrLf "Compare Report will exit."
    Exit Sub
    End If

    If MasterSht.Cells(2, 1).Value = "" Then
    MsgBox "Data not found in Master List tab." vbCrLf "Compare Report will exit."
    Exit Sub
    End If

    'Check filters
    With VendorSht
    If .AutoFilterMode = True Then .AutoFilterMode = False
    End With
    With MasterSht
    If .AutoFilterMode = True Then .AutoFilterMode = False
    End With

    'Clear data from List Comparison tab
    With ReportSht
    .Range("C3:M65536").ClearContents
    End With
    '==================================================================================================
    With VendorSht
    '.Select
    Nump1 = .Cells(.Rows.Count, "A").End(xlUp).Row 'determine how many cells are in each column
    Level = .Range(.Cells(2, 1), .Cells(Nump1, 1)).Value
    Vendpart = .Range(.Cells(2, 2), .Cells(Nump1, 2)).Value
    VendID = .Range(.Cells(2, 7), .Cells(Nump1, 7)).Value
    description = .Range(.Cells(2, 4), .Cells(Nump1, 4)).Value
    End With

    For i = 1 To UBound(Level) 'loop through the large array of all level numbers

    '!!--change top row level to something besides first level do it here-----!!

    If Level(i, 1) = 1 Then 'checks that is in level 1
    j = j + 1 'use this counter to determin size needed for level1loc
    End If
    Next

    ReDim level1loc(0 To j - 1) As Long 'resizes level1loc to the number of level one components
    j = 0
    For i = 1 To UBound(Level)

    '!!---change your top row level to something besides first level do it here-----!!

    If Level(i, 1) = 1 Then 'checks that is in level 1
    level1loc(j) = i 'forms an array of where all the first level assemblies are located in level array
    j = j + 1
    End If
    Next

    'Fills arrays of just level one parts
    For k = 0 To UBound(level1loc)

    ReDim Preserve Vendpartnum(k + 1) As String
    ReDim Preserve VenDescription(k + 1) As String
    ReDim Preserve Vendquan(k + 1) As Long

    pos7 = level1loc(k)
    Vendpartnum(k) = Vendpart(pos7, 1)
    VenDescription(k) = description(pos7, 1)
    Vendquan(k) = VendID(pos7, 1)

    Next

    With MasterSht
    Nump2 = .Cells(.Rows.Count, "A").End(xlUp).Row 'determine how many cells are in each column
    MasterPN = .Range(.Cells(2, 2), .Cells(Nump2, 2)).Value
    MasterDes = .Range(.Cells(2, 5), .Cells(Nump2, 5)).Value
    MasterID = .Range(.Cells(2, 8), .Cells(Nump2, 8)).Value
    End With

    With ReportSht
    'Fills the Master BOM list
    For i = 1 To UBound(MasterPN)
    pos1 = i + 2
    'pos = level1loc(i)
    .Cells(pos1, 3).Value = MasterPN(i, 1)
    .Cells(pos1, 4).Value = MasterDes(i, 1)
    .Cells(pos1, 5).Value = MasterID(i, 1)
    Next
    For x = 1 To UBound(MasterPN)
    For y = 0 To UBound(Vendpartnum) - 1
    If UCase(MasterPN(x, 1)) = UCase(Vendpartnum(y)) Then '
    .Cells(x + 2, 7) = .Cells(x + 2, 7) + Vendquan(y) ' 'Add to match Count (different ID numbers)
    .Cells(x + 2, 11) = Vendpartnum(y) 'Write data_b value
    .Cells(x + 2, 12) = VenDescription(y) 'Write data_b value
    Vendpartnum(y) = "1" 'indexes the Master PN array so we can determine which ones werent in the list later
    Else
    If InStr(1, UCase(MasterPN(x, 1)), UCase(Vendpartnum(y))) 0 Then 'checks to see if the parts are the same with different code
    If Vendpartnum(y) "1" And Vendpartnum(y) "" Then
    .Cells(x + 2, 7) = .Cells(x + 2, 7) '+ Vendquan(y) ' 'Add to match Count (different ID numbers)
    .Cells(x + 2, 11) = Vendpartnum(y) 'Write data_b value
    .Cells(x + 2, 12) = VenDescription(y) 'Write data_b value
    Vendpartnum(y) = "1" 'indexes the Master PN array so we can determine which ones werent in the list later
    End If
    End If
    If InStr(1, UCase(Vendpartnum(y)), UCase(MasterPN(x, 1))) 0 Then
    If Vendpartnum(y) "1" And Vendpartnum(y) "" Then
    .Cells(x + 2, 7) = .Cells(x + 2, 7) '+ Vendquan(y) ' 'Add to match Count (different ID numbers)
    .Cells(x + 2, 11) = Vendpartnum(y) 'Write data_b value
    .Cells(x + 2, 12) = VenDescription(y) 'Write data_b value
    Vendpartnum(y) = "1" 'indexes the Master PN array so we can determine which ones werent in the list later
    End If
    End If
    End If
    Next y
    Next x
    End With

    With ReportSht
    For j = 1 To UBound(Vendpartnum)
    If j = 1 Then 'this if statement is used only in the first iteration of the loop. this is because later I check the previous rows information
    ' to see if it is a duplicate and j-1 when it is one equals zero and causes issues
    If Vendpartnum(j) "1" And Vendpartnum(j) "" Then 'checks to see the current position has been indexed or not
    .Cells(x + 4, 11) = Vendpartnum(j) 'enters in the Vendor part number
    .Cells(x + 4, 12) = VenDescription(j) 'enters in the Vendor part description
    .Cells(x + 4, 7) = .Cells(x + 4, 7) + Vendquan(j) 'enter Vendor qty
    'Cells(x + 4, 2) = MasterDes(j, 1) 'enters in the Master part description
    x = x + 1
    End If
    Else
    If UCase(Vendpartnum(j)) UCase(Vendpartnum(j - 1)) Then 'checks to make sure the part number does not equal the last part number added to the list
    If Vendpartnum(j) "1" And Vendpartnum(j) "" Then 'checks to see if the part has been indexed
    .Cells(x + 4, 11) = Vendpartnum(j) 'enters the P/N
    .Cells(x + 4, 12) = VenDescription(j) 'enters in the Vendor part description
    .Cells(x + 4, 7) = .Cells(x + 4, 7) '+ Vendquan(j) '
    x = x + 1
    End If
    Else
    If Vendpartnum(j) "1" And Vendpartnum(j) "" Then 'Same thing as above except this is when the Part number equals the last one entered so the quantities are
    'just added to the already existing quantities
    .Cells(x + 4, 7) = .Cells(x + 4, 7) + Vendquan(j) '
    x = x + 1
    End If
    End If
    End If
    Next
    End With

    MsgBox "Compare Report Complete!"

    Exit Sub
    '==================================================================================================
    ErrHandler:
    MsgBox Err.description
    End Sub

    DB:2.57:I Need Help To Modify This Code For Comparison Report. sm

    With this code it does bring in ID of single part number, but if there is part number with multiple ID number then macro will add all of them up.

    Then you need a different code, have a look at this thread:

    http://answers.microsoft.com/en-us/office/forum/officeversion_other-customize/how-can-i-compare-2-columns-in-excel-and-have-the/61a75a51-f2b3-49f8-aba5-3665dff48b8d

    Andreas.

    PS.: I'm on vacation the next 2 weeks, an answer may take a while if you have any further question.

  • RELEVANCY SCORE 2.57

    DB:2.57:Help To Locked All Cells Of A Worksheet 8f


    I need to locked ALL cells of a worksheet using macro

    DB:2.57:Help To Locked All Cells Of A Worksheet 8f

    I got it and is correct!!!

    Sheets("Sheet1").Select
    Sheets("Sheet1").Cells.Locked = True

  • RELEVANCY SCORE 2.57

    DB:2.57:Visual Basic Help x9


    Hey Everyone,
    I wrote a code in Visual basic that updates a Master spreadsheet when I change data in a different sheet. For example if I mark down an x next to someones name in a group it will find his name on a spreadsheet of the entire class and mark an x down next
    to that name. However when I change multiple cells at a time using a drag or a fill the code breaks. Is there a way to fix this? Here is my code:
    Private Sub Worksheet_Change(ByVal Target As Excel.range)
    Dim wb As Workbook
    Dim mWS As Worksheet
    Dim conName As String
    Dim mCol As range
    Dim mCon As Integer
    Dim count As Long
    Dim cell As range
    Dim y As String



    count = 1
    y =
    Set wb = ActiveWorkbook
    Set mWS = wb.Sheets(Master)
    Set mCol = mWS.range(B:B)
    mCon = 0



    If Target.Column 100 Then
    ThisRow = Target.Row
    conName = ActiveSheet.Cells(ThisRow, B)
    y = Target.Value
    End If


    For Each cell In mCol
    If cell.Value = conName Then
    mCon = count
    Exit For
    End If
    count = count 1
    Next
    Dim cVal As Variant
    Set cVal = mWS.Cells(count, Target.Column)
    cVal.Value = y

    End Sub

    Any help is appreciated.

    Thanks,
    RJ

    DB:2.57:Visual Basic Help x9

    For names in column A of both sheets, and x (or other values) being entered into column B (again, of both sheets) use this code to allow multi-cell entry. (I also assumed that the names are always found on both sheets - if that is not true, then the code
    needs to be modified.)
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rngC As Range
    'Only fire if cells are in column B

    If Intersect(Range(B:B), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each rngC In Intersect(Range(B:B), Target)
    ThisWorkbook.Sheets(Master).Range(A:A).Find(rngC.Offset(0, -1).Value).Offset(0, 1).Value = Target.Value
    Next rngC
    Application.EnableEvents = True
    End Sub

  • RELEVANCY SCORE 2.57

    DB:2.57:Appending Data To An Existing Worksheet cs


    I have data in two worksheets (Main and Sheet A), one with filtered data (Main) that I would like to paste (append) the information after the last row of data in an existing worksheet (Sheet A).

    I am needing to find the last row of data ,data begins in Cell A12 in Sheet A (includes header row), however cells in column A do not have data in all cells, but cells in column B do.

    Here is a portion of the code I have now. Any thoughts or suggestions.

    Sheets("Main").Select
    ' ActiveSheet.Range("A2:E65000").Select
    ' ActiveCell.CurrentRegion.Select
    ' Selection.Offset(1).Resize(Selection.Rows.Count - 1).Select
    ' Selection.Copy
    ' Sheets("SheetA").Select
    ' Range("B13").End(xlDown).Select' ActiveCell.Offset(1).Select
    ' Selection.PasteSpecial

  • RELEVANCY SCORE 2.57

    DB:2.57:Unable To Copy Selected Cells From One Worksheet To Another x7


    Suddenly I am unable to copy selected cells from one worksheet to another. When I select cells in one worksheet using the CTRL key, I right click as always then navigate to my destination worksheet..usually I hit enter an the data copies over...not happening
    now. Any Ideas?

    DB:2.57:Unable To Copy Selected Cells From One Worksheet To Another x7

    Are these two different worksheets in the same workbook? If so, do you have code in the Worksheet_Activate event in the worksheet module, or in the Workbook_SheetActivate event in the ThisWorkbook module? That might clear the clipboard.
    Or are they worksheets in different workbooks? If so, you may have opened the workbooks in different instances of Excel. Pressing Enter to paste doesn't work between instances.

  • RELEVANCY SCORE 2.57

    DB:2.57:Find Cells Containg Certain Text And Count The Next Column m8


    Hi I'm needing help on a worksheet, I'm trying to get2 running totals on a seperate sheetif2cells = certain text; an example below:

    Sheet 1
    Cells in Column A headed Strapped would contain either YES or NO
    Cells in Column B headed Wrapped would contain either CLEAR or NO
    Cells in Column C headed Quantity would contain a numerical figureeg 55

    eg.
    A2=YES B2=CLEAR C2=55
    A3=YESB3=CLEAR C3=15

    Sheet 2:

    Two headers Strapped and Wrapped and then to be able to have a running total of what was strapped and what was wrapped

    Strapped Wrapped
    If Cell A2 = Yes then 55 If Cell B2 = Clear then 55
    If Cell A3 = Yes then70If Cell B2 = Clear then 70

    If the cells = NO then I don't want it to add the figure

    Any help or direction greatly appreciated as it is going to save me a lot of work.

    Thanks

    Simon

    DB:2.57:Find Cells Containg Certain Text And Count The Next Column m8

    Worked a treat, thank you very much!

    In that case, you should mark RonaldoOneNil's message as the "Answer" so that others (like myself) don't keep opening your thread thinking that you still need some help.

  • RELEVANCY SCORE 2.57

    DB:2.57:Lookup Formula To Also Find Colour Of Cell, As Well As The Value In It 99


    I am creating an Excel 2007 spreadsheet which contains one worksheet in which to enter data, and another worksheet that uses 'LOOKUP' based on the data entered.
    Whilst it successfully finds the correct value, the values on the second worksheet are colour coded (by the cells being filled in with a colour).
    Is there a way for the LOOKUP formula to also return the colour with the value to the 1st worksheet?
    I hope that question makes sense?
    Many thanks for your help
    Kind regards,
    Michael

    DB:2.57:Lookup Formula To Also Find Colour Of Cell, As Well As The Value In It 99

    I think somethinglikethis will work for you:
    Application.FindFormat.Interior.ColorIndex = 6
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Range(a2:a65535).Select
    Selection.Replace What:=, Replacement:=Sub-Total, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
    ReplaceFormat:=True

    You will probably have to tweak it a bit for your specific purposes . . . .Ryan Shuell

  • RELEVANCY SCORE 2.57

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

    DB:2.57:For-Next Loop Not Looping f3


    In the following snippit I would expect it to iterate 4 times (the value of iWSCount), but it passes right by the 'Next iWS' statement. This is A2007 on Windows8. Is my syntax wrong? I have rarely used For-Next, but I can't see any problem.
    Thank you for any help.

    For iWS = 1 To iWSCount 'Iterate Worksheet Count
    'Add Headings row
    iRow = 1
    objWorkBook.Worksheets(iWS).Cells(iRow, 1) = A. Sam Farms
    iRow = 2
    objWorkBook.Worksheets(iWS).Cells(iRow, 1) = Plant Scheduling Worksheet
    iRow = 3
    objWorkBook.Worksheets(iWS).Cells(iRow, 1) = strYear strLocation

    'Create Column headings
    iRow = 5
    objWorkBook.Worksheets(iWS).Cells(iRow, 1) = Plant Chr(13) Chr(10) Date
    objWorkBook.Worksheets(iWS).Cells(iRow, 2) = Sow Chr(13) Chr(10) Date
    objWorkBook.Worksheets(iWS).Cells(iRow, 3) = Acres

    rstColumns.MoveFirst
    For iCol = 5 To rstColumns.RecordCount * 3 5 Step 3
    objWorkBook.Worksheets(iWS).Cells(iRow, iCol) = rstColumns!Variety
    objWorkBook.Worksheets(iWS).Cells(iRow, iCol).HorizontalAlignment = xlCenter
    objWorkBook.Worksheets(iWS).Cells(iRow, iCol).Font.Bold = True
    'objWorkBook.Worksheets(iWS).Cells(iRow, iCol).Columns.AutoFit

    objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol) = rstColumns!DaysToHarvest Days Chr(13) Chr(10) Plants Ordered
    objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol).HorizontalAlignment = xlCenter
    objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol).Font.Bold = True
    'objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol).Columns.AutoFit
    objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol 1) = Chr(13) Chr(10) Chr(13) Chr(10) Plants Planted
    objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol 1).HorizontalAlignment = xlCenter
    objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol 1).Font.Bold = True
    'objWorkBook.Worksheets(iWS).Cells(iRow 1, iCol).Columns.AutoFit

    rstColumns.MoveNext
    Next iCol

    iRow = 6
    objWorkBook.Worksheets(iWS).Cells(iRow, 1) = Days to Harvest Chr(13) Chr(10)
    objWorkBook.Worksheets(iWS).Cells(iRow, 1).Font.Color = -16764058
    'objWorkBook.Worksheets(iWS).Cells(iRow, 1).Font.Bold = True


    'Populate spreadsheet
    rstData.MoveFirst

    'Dim iTest As Integer
    iOffSet = 2
    Dim myRange As Range
    Do Until rstData.EOF
    iRow = iRow 1
    objWorkBook.Worksheets(iWS).Cells(iRow, 1).NumberFormat = mm/dd/yy;@
    objWorkBook.Worksheets(iWS).Cells(iRow, 1) = rstData![planting date]
    'objWorkBook.Worksheets(iWS).Cells(iRow, 1).Columns.AutoFit
    objWorkBook.Worksheets(iWS).Cells(iRow, 2).NumberFormat = mm/dd/yy;@
    objWorkBook.Worksheets(iWS).Cells(iRow, 2) = rstData![Sow date]
    'objWorkBook.Worksheets(iWS).Cells(iRow, 2).Columns.AutoFit
    objWorkBook.Worksheets(iWS).Cells(iRow, 3) = rstData![Acres]
    'objWorkBook.Worksheets(iWS).Cells(iRow, 3).Columns.AutoFit


    dPlantDate = rstData![planting date]
    Do Until rstData![planting date] dPlantDate
    Set myRange = objWorkBook.Worksheets(iWS).Cells.Find(what:=rstData!Variety)
    myRange.Offset(iOffSet, 0).Value = rstData![Plants Ordered]
    myRange.Offset(iOffSet, 1).Value = rstData![Plants Planted]
    rstData.MoveNext
    Loop
    'MsgBox iTest
    iOffSet = iOffSet 1
    dPlantDate = rstData![planting date]
    rstData.MoveNext

    Loop

    Next iWS

    God Bless, Mark A. Sam The Bible promises there will be NO computers in Heaven: Revelation 21:4

    DB:2.57:For-Next Loop Not Looping f3

    This is resolved. It has to do with an err that wasn't reporting, because of my trapping methods. The err was taking the execution out of the loop to the Exit label.
    God Bless, Mark A. Sam The Bible promises there will be NO computers in Heaven: Revelation 21:4