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 20 years, 10 months 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
-
Windows 11 Insider Preview Build 22635.5170 (23H2) released to BETA
by
joep517
3 hours, 22 minutes ago -
Has the Microsoft Account Sharing Problem Been Fixed?
by
jknauth
6 hours, 48 minutes ago -
W11 24H2 – Susan Bradley
by
G Pickerell
8 hours, 44 minutes ago -
7 tips to get the most out of Windows 11
by
Alex5723
6 hours, 45 minutes ago -
Using Office apps with non-Microsoft cloud services
by
Peter Deegan
6 minutes ago -
I installed Windows 11 24H2
by
Will Fastie
3 hours, 46 minutes ago -
NotifyIcons — Put that System tray to work!
by
Deanna McElveen
12 hours, 11 minutes ago -
Decisions to be made before moving to Windows 11
by
Susan Bradley
2 hours, 39 minutes ago -
Port of Seattle says ransomware breach impacts 90,000 people
by
Nibbled To Death By Ducks
20 hours, 25 minutes ago -
Looking for personal finance software with budgeting capabilities
by
cellsee6
4 hours, 38 minutes ago -
ATT/Yahoo Secure Mail Key
by
Lil88reb
4 hours, 52 minutes ago -
Devices with apps using sprotect.sys driver might stop responding
by
Alex5723
1 day, 13 hours ago -
Neowin – 20 times computers embarrassed themselves with public BSODs and goofups
by
EP
1 day, 22 hours ago -
Slow Down in Windows 10 performance after March 2025 updates ??
by
arbrich
1 day ago -
Mail from certain domains not delivered to my outlook.com address
by
pumphouse
1 day, 6 hours ago -
Is data that is in OneDrive also taking up space on my computer?
by
WShollis1818
1 day, 17 hours ago -
Nvidia just fixed an AMD Linux bug
by
Alex5723
3 days, 8 hours ago -
50 years and counting
by
Susan Bradley
7 hours, 3 minutes ago -
Fix Bluetooth Device Failed to Delete in Windows Settings
by
Drcard:))
9 hours, 51 minutes ago -
Licensing and pricing updates for on-premises server products coming July 2025
by
Alex5723
3 days, 19 hours ago -
Edge : Deprecating window.external.getHostEnvironmentValue()
by
Alex5723
3 days, 19 hours ago -
Rethinking Extension Data Consent: Clarity, Consistency, and Control
by
Alex5723
3 days, 19 hours ago -
OneNote and MS Word 365
by
CWBillow
3 days, 21 hours ago -
Ultimate Mac Buyers Guide 2025: Which Mac is Right For You?
by
Alex5723
3 days, 21 hours ago -
Intel Unison support ends on Windows 11 in June
by
Alex5723
3 days, 22 hours ago -
April 2025 — still issues with AMD + 24H2
by
Kevin Jones
1 day, 13 hours ago -
Windows 11 Insider Preview build 26200.5518 released to DEV
by
joep517
4 days, 9 hours ago -
Windows 11 Insider Preview build 26120.3671 (24H2) released to BETA
by
joep517
4 days, 9 hours ago -
Forcing(or trying to) save Local Documents to OneDrive
by
PateWilliam
4 days, 18 hours ago -
Hotpatch for Windows client now available (Enterprise)
by
Alex5723
4 days, 6 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.