Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    checking data...in excel (2000)

    I have 14 columns of numeric data. The data is in rows 1-12 columa. It gets updated every day by adding a new column (say column 15). How do I have the data check column 14 to ensure that the data is not the same data I'm getting for my new day. In other words I don't want to put the same data I just inserted in column 14 within column 15.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: checking data...in excel (2000)

    You could compare it row by row, or use MATCH. How you do it will depend on how and when you want to check and what the source is.

    Could you give us more details?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    ok this might help... here is the actual code I'm using:
    Sub Get12()
    Dim LastCol As Long
    LastCol = Worksheets("data entry").Range("IV3").End(xlToLeft).Column + 1
    Worksheets("Sheet1").[A2:A13].Copy Destination:=Worksheets("data entry").Cells(3, LastCol)
    End Sub

    now I need to be able to check colum+1 against last column to ensure I don't have two colums with the exact same data in them. If it is the same data I need to inform user that the data is the same data from yesterday (last column).

    how do I do this in excel????

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: checking data...in excel (2000)

    Here is an ad hoc solution; you could make it more general if you like. It tests before copying, but you can change that.

    Function CompareCols(col As Long) As Boolean
    Dim i As Long
    Dim s As Long
    For i = 2 To 13
    s = s - (Worksheets("Sheet1").Cells(i, 1) = Worksheets("data entry").Cells(i + 1, col))
    Next i
    CompareCols = (s = 12)
    End Function

    Sub Test()
    Dim LastCol As Long
    LastCol = Worksheets("data entry").Range("IV3").End(xlToLeft).Column
    If CompareCols(LastCol) = True Then
    If MsgBox("The new data are equal to the last data. Do you want to proceed?", _
    vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If
    End If
    Worksheets("Sheet1").Range("A2:A13").Copy Destination:=Worksheets("data entry").Cells(3, LastCol + 1)
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    hans - first of all thank you for replying so fast. second of all where do I put your new macro at before my old one or after it. Pleae inform... I'm still a newbe at this thanks p.s. see prior mail for my macro...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: checking data...in excel (2000)

    The code I posted was meant as replacement for the macro you had. If you like, you can rename Test to Get12.

    You should copy both the Function and the Sub into your module. The Sub needs the Function to work.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    LastCol = Worksheets("data entry").Range("IV3").End(xlToLeft).Column

    receiving RUN time error "9"
    subscript out of range and it's pointing to the command above... what does X1toleft mean.... my columns go from left to right but I want to compare the left (new) column to the right (old column), also what does IV3 mean

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: checking data...in excel (2000)

    1) is there a worksheet in the activeworkbook named "data entry"? It would seem that you do not.

    It is "xlToLeft" not "x1ToLeft" It is an excel (xl) constant.

    The line starts in the "data entry" sheet in cell IV3 (3rd row the very last column) and then moves to the left (it is in the furthest right column) until the first non-blank column. This is the last column with an entry. It then gets the column number of that column and stores it in the variable called "LastCol"

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    every time I run the macro it put the same data in a different column still I need something that will prevent this. His test function isnt working is there something wrong with it?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: checking data...in excel (2000)

    >> His test function isnt working
    Perhaps he doesn't understand what you want?

    Could you post a small workbook that demonstrates what you are working with? Replace sensitive data with dummy data, and indicate in the workbook itself or in the post itself what should go where?

  11. #11
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    how do I do that?

  12. #12
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    3:00 77 65 65
    3:30 77 67 67
    4:00 77 72 72
    4:30 77 62 62
    5:00 77 49 49
    5:30 79 79 79
    6:00 77 91 91
    6:30 77 285 285
    7:00 77 321 321
    7:30 77 196 196
    8:00 77 253 253

    total jobs 849

    the first column is just the time of day. the other columns are filled with data. take notice that the 2nd and third columns have the same data in them. somebody ran the macro before the jobs were finished... hence it put out yesterdays data thats why the two columns are the same. I need to prevent this from happening again. but how?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: checking data...in excel (2000)

    When you are composing a new post or a reply, there is a box where you can enter the path to a file to attach; you can also click the Browse button. See screenshot.

    You can attach files of up to 100 KB in size; if the workbook is larger than that, create a ZIP file from it and attach that.

    Warning: previewing a post or reply clears the "Attach a file" box, so you must fill the box just before actually posting.

  14. #14
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    Couldn't you simply add conditional formatting which would highlight the new entry if it equals the cell to its left?

    Seems simple enough unless I am missing something here.

    -yoyo

  15. #15
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: checking data...in excel (2000)

    ok but to a newbe how would I do that?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •