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.
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
checking data…in excel (2000)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » checking data…in excel (2000)
- This topic has 39 replies, 4 voices, and was last updated 21 years ago.
AuthorTopicWSvanoskyj
AskWoody LoungerApril 26, 2004 at 11:53 am #404120Viewing 6 reply threadsAuthorReplies-
WSsdckapr
AskWoody Lounger -
WSsdckapr
AskWoody Lounger -
WSvanoskyj
AskWoody LoungerApril 29, 2004 at 4:37 am #821205ok 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 Subnow 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????
-
WSHansV
AskWoody LoungerApril 29, 2004 at 6:27 am #821227Here 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 FunctionSub 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 -
WSHansV
AskWoody LoungerApril 29, 2004 at 6:27 am #821228Here 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 FunctionSub 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
-
-
WSvanoskyj
AskWoody LoungerApril 29, 2004 at 4:37 am #821206ok 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 Subnow 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????
-
WSvanoskyj
AskWoody Lounger -
WSHansV
AskWoody Lounger -
WSvanoskyj
AskWoody LoungerApril 29, 2004 at 9:12 am #821278LastCol = 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 -
WSsdckapr
AskWoody LoungerApril 29, 2004 at 9:30 am #8212821) 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
-
WSvanoskyj
AskWoody Lounger -
WSHansV
AskWoody LoungerApril 29, 2004 at 10:13 am #821288 -
WSvanoskyj
AskWoody Lounger -
WSvanoskyj
AskWoody LoungerApril 29, 2004 at 10:24 am #8213043: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 253total 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?
-
WSvanoskyj
AskWoody LoungerApril 29, 2004 at 10:24 am #8213053: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 253total 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?
-
WSHansV
AskWoody LoungerApril 29, 2004 at 10:25 am #821306When 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.
-
WSvanoskyj
AskWoody LoungerMay 15, 2004 at 3:10 am #827594HansV – this is my problem Every time I open the spreedsheet called data entry It not only updates todays date but yesterdays as well. How can I fix this? I need a solution ASAP before saturday night 05/15/04.
St. Louis
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 23 27 26 26 21 21
3:30 43 34 28 27 31 31
4:00 37 33 12 17 26 26
4:30 30 24 10 13 16 16
5:00 27 25 20 15 16 16
5:30 36 33 43 21 25 25
6:00 20 28 17 13 17 17 < ——see how it duplicates
6:30 46 59 39 32 59 59 the whole column
7:00 22 26 27 14 24 24
7:30 17 19 17 15 21 21
8:00 30 28 20 20 25 25Total Jobs 331 336 259 213 281 281 0
Ohio
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 21 23 27 20 25
3:30 30 53 24 62 29
4:00 43 51 23 46 27
4:30 50 39 57 50 28
5:00 50 31 16 42 23
5:30 49 25 12 31 20
6:00 40 20 7 27 15
6:30 23 17 7 20 21
7:00 38 23 16 26 19
7:30 22 17 21 15 22
8:00 28 23 21 25 21Total Jobs 394 322 231 364 250 0 0
-
WSsdckapr
AskWoody Lounger -
WSvanoskyj
AskWoody LoungerMay 15, 2004 at 11:53 am #827681this is the code i’m using.
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 FunctionSub Get12()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3″).End(xlToLeft).Column
If CompareCols(LastCol) = True Then
If MsgBox(” Please Check to ensure that the Jobs ran good. Is it past 10:15am EST? The new data is 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)
ActiveWorkbook.Save
End Subthis is my data that I import from a text file on another drive. (what you see is what it is).
21
31
26
16
16
25
17
59
24
21
25The data is imported into sheet1 column A rows 2 through 12. I then on Sheet 2 have the macro (up above) bring the data in and update my columns.
The problem resides in when ever I open the spreedsheet it is automatically updated but it updates todays column and yesterdays column. I just want it to update todays column. -
WSsdckapr
AskWoody Lounger -
WSsdckapr
AskWoody Lounger -
WSvanoskyj
AskWoody LoungerMay 15, 2004 at 11:53 am #827682this is the code i’m using.
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 FunctionSub Get12()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3″).End(xlToLeft).Column
If CompareCols(LastCol) = True Then
If MsgBox(” Please Check to ensure that the Jobs ran good. Is it past 10:15am EST? The new data is 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)
ActiveWorkbook.Save
End Subthis is my data that I import from a text file on another drive. (what you see is what it is).
21
31
26
16
16
25
17
59
24
21
25The data is imported into sheet1 column A rows 2 through 12. I then on Sheet 2 have the macro (up above) bring the data in and update my columns.
The problem resides in when ever I open the spreedsheet it is automatically updated but it updates todays column and yesterdays column. I just want it to update todays column. -
WSsdckapr
AskWoody Lounger -
WSHansV
AskWoody Lounger -
WSHansV
AskWoody Lounger -
WSvanoskyj
AskWoody LoungerMay 15, 2004 at 3:10 am #827595HansV – this is my problem Every time I open the spreedsheet called data entry It not only updates todays date but yesterdays as well. How can I fix this? I need a solution ASAP before saturday night 05/15/04.
St. Louis
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 23 27 26 26 21 21
3:30 43 34 28 27 31 31
4:00 37 33 12 17 26 26
4:30 30 24 10 13 16 16
5:00 27 25 20 15 16 16
5:30 36 33 43 21 25 25
6:00 20 28 17 13 17 17 < ——see how it duplicates
6:30 46 59 39 32 59 59 the whole column
7:00 22 26 27 14 24 24
7:30 17 19 17 15 21 21
8:00 30 28 20 20 25 25Total Jobs 331 336 259 213 281 281 0
Ohio
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 21 23 27 20 25
3:30 30 53 24 62 29
4:00 43 51 23 46 27
4:30 50 39 57 50 28
5:00 50 31 16 42 23
5:30 49 25 12 31 20
6:00 40 20 7 27 15
6:30 23 17 7 20 21
7:00 38 23 16 26 19
7:30 22 17 21 15 22
8:00 28 23 21 25 21Total Jobs 394 322 231 364 250 0 0
-
WSHansV
AskWoody LoungerApril 29, 2004 at 10:25 am #821307When 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.
-
WSvanoskyj
AskWoody Lounger -
WSHansV
AskWoody LoungerApril 29, 2004 at 10:13 am #821289 -
WSvanoskyj
AskWoody Lounger
-
-
WSsdckapr
AskWoody LoungerApril 29, 2004 at 9:30 am #8212831) 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
-
-
WSvanoskyj
AskWoody LoungerApril 29, 2004 at 9:12 am #821279LastCol = 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
-
-
WSHansV
AskWoody Lounger
-
-
WSvanoskyj
AskWoody Lounger -
WSyoyophil
AskWoody Lounger -
WSvanoskyj
AskWoody Lounger -
WSsdckapr
AskWoody LoungerMay 1, 2004 at 9:12 am #822079Based on your original question, I don’t think conditional formatting will do what you want.
It could highlight the cells in the new column, that are equal to the old column, they would be highlighted if any were the same, you are looking for whether all would be the same. You woul have to manually add the cond formatting after each copy or add it via macro code. It would not prevent the dupes only highlight them.
Hans’ code prevents the dupes, so I think that is a better approach, since you want to prevent them, not deal with after the fact (which is what your original question was).
Steve
-
WSsdckapr
AskWoody LoungerMay 1, 2004 at 9:12 am #822080Based on your original question, I don’t think conditional formatting will do what you want.
It could highlight the cells in the new column, that are equal to the old column, they would be highlighted if any were the same, you are looking for whether all would be the same. You woul have to manually add the cond formatting after each copy or add it via macro code. It would not prevent the dupes only highlight them.
Hans’ code prevents the dupes, so I think that is a better approach, since you want to prevent them, not deal with after the fact (which is what your original question was).
Steve
-
-
WSvanoskyj
AskWoody Lounger
-
Viewing 6 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Acronis : Tracking Chaos RAT’s evolution (Windows, Linux)
by
Alex5723
4 hours, 22 minutes ago -
Turning off OneDrive
by
CWBillow
8 hours, 51 minutes ago -
June 2025 updates are out
by
Susan Bradley
56 minutes ago -
Mozilla shutting Deep Fake Detector
by
Alex5723
19 hours, 8 minutes ago -
Windows-Maintenance-Tool (.bat)
by
Alex5723
4 hours, 31 minutes ago -
Windows 11 Insider Preview build 26200.5641 released to DEV
by
joep517
21 hours, 41 minutes ago -
Windows 11 Insider Preview build 26120.4250 (24H2) released to BETA
by
joep517
21 hours, 42 minutes ago -
Install Office 365 Outlook classic on new Win11 machine
by
WSrcull999
21 hours, 40 minutes ago -
win 10 to win 11 with cpu/mb replacement
by
aquatarkus
13 hours, 32 minutes ago -
re-install Windows Security
by
CWBillow
1 day ago -
WWDC 2025 Recap: All of Apple’s NEW Features in 10 Minutes!
by
Alex5723
1 day, 4 hours ago -
macOS Tahoe 26
by
Alex5723
22 hours, 51 minutes ago -
Migrating from win10 to win11, instructions coming?
by
astro46
10 hours, 56 minutes ago -
Device Eligibility for Apple 2026 Operating Systems due this Fall
by
PKCano
13 hours, 24 minutes ago -
Recommended watching : Mountainhead movie
by
Alex5723
14 hours, 8 minutes ago -
End of support for Windows 10
by
Old enough to know better
14 hours, 4 minutes ago -
What goes on inside an LLM
by
Michael Covington
8 hours, 17 minutes ago -
The risk of remote access
by
Susan Bradley
3 hours, 57 minutes ago -
The cruelest month for many Office users
by
Peter Deegan
20 hours, 20 minutes ago -
Tracking protection and trade-offs in Edge
by
Mary Branscombe
18 hours, 22 minutes ago -
Supreme Court grants DOGE access to confidential Social Security records
by
Alex5723
2 days, 3 hours ago -
EaseUS Partition Master free 19.6
by
Alex5723
1 day, 3 hours ago -
Microsoft : Edge is better than Chrome
by
Alex5723
2 days, 16 hours ago -
The EU launched DNS4EU
by
Alex5723
3 days, 4 hours ago -
Cell Phone vs. Traditional Touchtone Phone over POTS
by
280park
2 days, 19 hours ago -
Lost access to all my networked drives (shares) listed in My Computer
by
lwerman
3 days, 10 hours ago -
Set default size for pasted photo to word
by
Cyn
3 days, 16 hours ago -
Dedoimedo tries 24H2…
by
Cybertooth
3 days, 4 hours ago -
Windows 11 Insider Preview build 27871 released to Canary
by
joep517
4 days, 15 hours ago -
Windows 11 ad from Campaign Manager in Windows 10
by
Jim McKenna
2 days, 8 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.