+ Reply to Thread
Results 1 to 132 of 132

Trim oldest entries from text file to allow for new

  1. #1
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192

    Trim oldest entries from text file to allow for new

    I am writing to a text file and only want to allow 10,000 lines. There is only one value on each line. How can I trim the top of this text file to allow for more entries to be appended to it and still keep it at no more that 10,000 entries?

    The lines look like

    "1001-1"
    "1002-3"
    "1003-3"
    "1004-4"
    "1005-5"
    "1002-6"
    "1001-10"
    "1004-7"
    "1015-5"
    "0002-6"

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Perhaps a little more detail as to how you are opening and amending the file?

  3. #3
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Well, I havent gotten to that part just yet lol

    But basically,

    Please Login or Register  to view this content.
    The part in red will be where I get my data from my worksheet but thats basically what I'm using to append to the file. Now how to trim it before adding more data?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I would ...

    ... open the existing file (file1) for read, and a new file (file2) for write.

    ... read and discard records in file1 until the appropriate point

    ... read file1 and write to file2 until the end of file1

    ... append records to file2

    ... close and kill file1 (or rename as file1.bak)

    ... rename file2 as file1

  5. #5
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Well, that sounds like what I want to do but Im not sure I have the VB skills to pull it off... Can you or anyone give an example code of what I can do to accomplish this?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    How about you start and I help?

  7. #7
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    That sounds good to me! But it will have to wait till a little later in the evening. I have a ton of "other" work I need to get done before I can mess with it. Thanks for the offer!

    I'll post what I come up with (probably not much) as soon as I get a little time.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I'll post what I come up with (probably not much)...
    You already know how to open and write to files, clayton -- you have most of what you need. Struggle a little -- that's how you learn to code.

  9. #9
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    from your previous post the part that confuses me is how to disregard what I don't want.. I have no clue as to how to filter the oldest records...

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I assumed that the oldest records are at the front of the file, and the newest at the end. That's why you read records for a while and do nothing with them, then start copying to the second file.

    If that's not true, then you need to do something else.

  11. #11
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    hmm.. The oldest files are at the "top" of the file, newest are being appended to the end of the file in one column like I showed in my first post.

    I guess what I need to do is count how many records are in the file, have a set number as to how many to delete and somehow select the first 'set number' of records and delete them.. man, now my head is swirling lol

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Clayton, you don't delete them; you can't delete them. You just do nothing, as in don't copy them to the second file, the file that is going to replace the first file.

    If the file has 10,000 records, and you want to add 10, you read and do nothing with the first 10, copy the next 9990 to the second file, and then add 10 more.

  13. #13
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    OK, I'm starting to understand. I'm not editing the existing, I'm creating a new one from the existing and then overwriting the existing one with the new one.. OK, now that i got that in my head I'll try to work with that a little later. It's going to be a busy night but I think I will be able to sqeeze some time in for this. Probably in about 2 to 3 hours from now. Thanks again.

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Please reread my post #4 before starting.

  15. #15
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I have printed it out for reference

    Just so I know I am going down the right path for what I am trying to do here let me tell you the scenario.

    I have a worksheet where people put in lot and roll numbers for that lot. So, they are entered like:
    "1008-2"
    "1008-3"
    "1007-27"
    "1008.16"
    etc..

    I want to highlight cells that have a number matching what is in the text file. The text file is comprised of all past numbers that have been entered (up to 10,000)

    I will have the worksheet itself see if there are duplicates on the current worksheet and let it do its own highlighting for that. But if one exists in the text file then to highlight that cell as well.

    Do you think I am going about this in a optimal manner?

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    How about if we keep this thread related to the file operations, and you can address how you decide what gets added and deleted in a separate thread?

  17. #17
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    lol! Ok, I'll take that as a yes, I am on the correct path for what I am trying to accomplish

    Thanks again. I'll post as soon as I think I have something

  18. #18
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Good. So for this purpose, assume that the file already has the correct number of records, and that you have a contiguous range of cells whose contents are to be added at the end of the file, with a like number of records to be deleted from the beginning. So your sub declaration could look like this:
    Please Login or Register  to view this content.
    Don't rush on my behalf, clayton, it may be a day before I get back to it.

  19. #19
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Not rushing. Just driven to get it working

    This is what I've come up with so far. It sort of works but instead of trimming the first names it's trimming the last ones off the bottom... This is not what I want it to do as you know lol
    Anyway, here is what I've got so far. I'm only working with a max of 10 files for this test.

    Had to use a Sub because I'm not familiar with how to call a function too well..

    Please Login or Register  to view this content.
    Last edited by clayton; 05-01-2008 at 06:32 PM.

  20. #20
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Does that compile, clayton?

    Then put Option Explicit at the top, and compile again.

  21. #21
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by shg
    Does that compile, clayton?

    Then put Option Explicit at the top, and compile again.
    When i put Option Explicit at the top it will not run. Says "Compile error: variable not defined.. But it runs without that at the top.

    When you say 'compile' you mean does it run?

    EDIT:

    I added Option Explicit to the top, and defined the variables (probably not correctly but...)
    and this is what I have now. It runs.
    Please Login or Register  to view this content.
    Last edited by clayton; 05-01-2008 at 07:15 PM.

  22. #22
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Compiling is what happens when you do Debug > Compile VBA Project. It checks sytnax (statements are well-formed) and other errors that can be detected before the code is run. (In other languages, it creates an executable file in native machine language, but VBA is interpreted.)

    What does FreeFile return?

    What is the purpose of calling it?

    And given the above, what are #111 and #222 in your code?

    What kind of data does countit contain -- a string, a floating-point number, a logical value, an integer? What data type is best suited to the purpose? Ditto for all the other variables.

    You loop through the first file until the end. Why? What do you want to do, per the post you printed?

    I could write this in less time than helping you write it, but am happy to spend the time if you're willing. What's your objective -- I'll go either way.
    Last edited by shg; 05-02-2008 at 12:27 AM.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    I have printed it out for reference

    Just so I know I am going down the right path for what I am trying to do here let me tell you the scenario.

    I have a worksheet where people put in lot and roll numbers for that lot. So, they are entered like:
    "1008-2"
    "1008-3"
    "1007-27"
    "1008.16"
    etc..

    I want to highlight cells that have a number matching what is in the text file. The text file is comprised of all past numbers that have been entered (up to 10,000)

    I will have the worksheet itself see if there are duplicates on the current worksheet and let it do its own highlighting for that. But if one exists in the text file then to highlight that cell as well.

    Do you think I am going about this in a optimal manner?
    I haven't read all this thread, so I didn't quite understand what you are really after in the end,but try the code for the above.
    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by shg
    Compiling is what happens when you do Debug > Compile VBA Project. It checks sytnax (statements are well-formed) and other errors that can be detected before the code is run. (In other languages, it creates an executable file in native machine language, but VBA is interpreted.)
    Wow, I've never used that lol! Now that I know its there I will for sure.


    What does FreeFile return?
    Not sure exactly. I've gathered code from various places on the web and that was part of a snipit I got somewhere. It works so I havent questioned it.

    What is the purpose of calling it?
    Not really sure?

    And given the above, what are #111 and #222 in your code?
    Well, just made up names for the file being opened to be used in the script?
    Again, that is the way I found the code. I just named them 111 and 222 for the heck of it.


    What kind of data does countit contain -- a string, a floating-point number, a logical value, an integer?
    Its a number. I don't understand what all the Long, Object, etc mean. I know String is text and integer is a number but thats about it. Whats the difference between an integer and a floating-point number?
    Variant was something in existing code that seems to work for most everything lol. But, not sure what exactly I'm doing by using Variant...
    I need a web page that describes these.


    What data type is best suited to the purpose? Ditto for all the other variables.
    This I do not know...

    You loop through the first file until the end. Why? What do you want to do, per the post you printed?
    The first loop is just counting how many lines are in the file to see if its over the max_files limit (should be named differently I guess.. not a file after all).


    I could write this in less time than helping you write it, but am happy to spend the time if you're willing. What's your objective -- I'll go either way.
    I of course want to learn how to do stuff myself. I seem to have a hard time remembering all the little ins and outs of coding though... Maybe its my age... I don't know.. Anyway, I like the coaxing along. It makes me work for it.
    Actually, I think my main problem is figuring out exactly what I want the code to do before I try to get it to do it lol!

  25. #25
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by jindon
    I haven't read all this thread, so I didn't quite understand what you are really after in the end,but try the code for the above.
    Please Login or Register  to view this content.
    Thank you for your suggestion but I'm going to turn a blind eye to this post just for a few. Going to try the hard way first. (by myself with a little help on the side)
    But I SO just want to grab the code and run with it LOL!

  26. #26
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Help is your friend. You should look up every statement you don't understand.

    FreeFile gives you a number (an Integer) that you use as a 'handle' to a file. Since other files may be open by the application, FreeFile makes sure you get an unused ('free') one. You have absolutely no need to know what the number is; that it is unique when you receive it is all you need to know. So everywhere you use #111 and #222, you're circumventing the use of the filenumber you got from FreeFile.

    See Help for "Data Type Summary" for a concise description of data types. Variants will work as a substitute for anything, but (a) you can get in trouble with confusing errors using Variants if you aren't careful, and (b) good practice is to assign data types appropriate to the type of data a variable will store. (An Object variable is a pointer to a data structure called a VTtable, within which are entry points to an object's properties and methods -- but that's getting a little ahead at this point.)

    I seem to have a hard time remembering all the little ins and outs of coding though... Maybe its my age...
    10:1 I'm 10 years older than you are, clayton The hard part of coding is thinking clearly and logically. The balance is just bending the language to your will. It keeps your brain limber.

    BTW, Jindon's post is responsive to your second question, the one I deferred to your next thread. If you're inclined to try it, you owe it to yourself to to suss out how it works. Unfortunately, it uses some stuff that you wouldn't likely learn about in your first several months of learning VBA and Excel's object model.

    Here's a start. How about you see if you understand everything going on. It is completely untested, so there may well be errors (but it compiles).
    Please Login or Register  to view this content.
    Last edited by shg; 05-02-2008 at 02:31 PM.

  27. #27
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Well.. No. I don't understand 'everything' that is going on. Like one thing is where you use "Dim iFileInp As Integer"
    Wouldn't the contents of the text file be considered text since it has a - in it?
    That is why I used write instead of print so it puts the "" around it.

    Im gonna take this all home with me and probably mess with it over the weekend. Thanks for your help!


    BTW, I ran the code you posted but it didn't seem to do anything. Even when I stepped threw it. But, it probably isn't meant to do anything just yet right?

  28. #28
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    So look up the Dim statement in Help. What is iFileInp used for in the code?

    Create a text file named TestFile.xls in the same directory as the workbook, type in several lines, save it, and close the file. As you step through the loop, you'll see those lines appear in sBuf. It reads 10 lines and leaves the file open (which is bad behavior, but will be corrected later in the code).

  29. #29
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by jindon
    I haven't read all this thread, so I didn't quite understand what you are really after in the end,but try the code for the above.
    Please Login or Register  to view this content.
    I just tried this code but didn't see that it is doing anything. I mean, I walk threw it using F8 and i see that it reads the whole file then breaks it down into individual bits. The second For, Next loops doesn't seem to be working. It never finds the matches for some reason.

  30. #30
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Clayton, I have the week from hell coming up, so please forgive me for posting the rest of the code. I'll be happy to answer questions.
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Forgive you? Hell! I want to thank you! You opened my eyes on a few things so I want to thank you for that too. I'll check this out tomorrow as its 2AM here and I have to work tomorrow myself. Thanks again

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    I just tried this code but didn't see that it is doing anything. I mean, I walk threw it using F8 and i see that it reads the whole file then breaks it down into individual bits. The second For, Next loops doesn't seem to be working. It never finds the matches for some reason.
    If you are comparing TxtCompare(non case sensitive)
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    And the code is assuming txt file only has one column of data.

    P.S
    For the safety
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Last edited by jindon; 05-05-2008 at 02:11 AM.

  33. #33
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by shg
    Clayton, I have the week from hell coming up, so please forgive me for posting the rest of the code. I'll be happy to answer questions.
    Please Login or Register  to view this content.

    This code doesn't seem to be working the way I think you intended it to. Or, more likely I didn't describe what I wanted to do well enough.

    I am finding that it will remove the existing numbers in the file (sometimes) and replace them with the new numbers. Sometimes it leaves part of the numbers and adds the new numbers.. I'm confused lol

    I know this code is only for writing the file and not doing the formatting but just for clarification this is the scenario.

    User inters numbers in the form of 1008-12 all in one column.

    When the workbook is closed all the numbers in that one column are written to a file.

    The workbook is opened again. More numbers like the above are entered. If a number is entered on this sheet that matches one in the previously written text file numbers then it should highlight that cell yellow.

    When the workbook is closed it writes (adds) the new numbers to the text file.

    IF the text file has more than 10,000 entries then it should trim the oldest entries and add the newest ones to it.

    I just thought of something though.. If the user lets this number exist (the duplicate) and it writes to the text file then it will find two matches if that number is entered again.. I guess that is not a big deal though as long as it works
    Last edited by clayton; 05-05-2008 at 09:03 PM.

  34. #34
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    I have printed it out for reference

    Just so I know I am going down the right path for what I am trying to do here let me tell you the scenario.

    I have a worksheet where people put in lot and roll numbers for that lot. So, they are entered like:
    "1008-2"
    "1008-3"
    "1007-27"
    "1008.16"
    etc..

    I want to highlight cells that have a number matching what is in the text file. The text file is comprised of all past numbers that have been entered (up to 10,000)

    I will have the worksheet itself see if there are duplicates on the current worksheet and let it do its own highlighting for that. But if one exists in the text file then to highlight that cell as well.

    Do you think I am going about this in a optimal manner?
    Can you just tell me
    1) How many columns in the text file ?
    2) Which column of which sheet to be compared ?

  35. #35
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    try
    Please Login or Register  to view this content.

  36. #36
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by jindon
    Can you just tell me
    1) How many columns in the text file ?
    2) Which column of which sheet to be compared ?
    Only 1 column in the text file. It would be sheet 1 column A.

  37. #37
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by jindon
    try
    Please Login or Register  to view this content.
    I get "Expected Array" and it highlights UBound(x) using the above code. I replaced my sheet1 and range with my own.

  38. #38
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    Only 1 column in the text file. It would be sheet 1 column A.
    Then try
    Please Login or Register  to view this content.

  39. #39
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    This seems to be working great!
    I modified it a tiny bit so it would clear the highlighted cells background if they corrected there mistake. Thanks!

    Please Login or Register  to view this content.
    THe only other thing I had to do is change the way the text file was written. I had to use print instead of write because your code would not work with the "" around the numbers. Now, I'm thinking this might mess with SHG's code... hmmm... I have to wait for him to reply about the problems I'm having with that part of it but for now this works like a charm :D
    Last edited by clayton; 05-05-2008 at 11:40 PM.

  40. #40
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    OK
    Can you summarise what you want to do again for me?
    1) you have txt file with IDs
    2) compare with IDs on the sheet
    3) then what ?

  41. #41
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Quote Originally Posted by jindon
    OK
    Can you summarise what you want to do again for me?
    1) you have txt file with IDs
    2) compare with IDs on the sheet
    3) then what ?
    The only other thing was what SHG and I were working on which is adding the entries in column A to a text file. If that text file has more than 10,000 lines then take out the oldest ones and add the new ones. Not sure what to do about duplicates though.. Not sure it really matters much.

  42. #42
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    clayton, please stop quoting entire posts. It wastes space to no benefit.

    When you're done with jindon's help, please let me know what residual problems you're having.

  43. #43
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    OK sure will. Thanks again! Hope your week from hell is a little less heated than you were anticipating.

  44. #44
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Then try
    Please Login or Register  to view this content.
    Code Edited
    Last edited by jindon; 05-06-2008 at 12:21 AM.

  45. #45
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Correction
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  46. #46
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Ok, I just got a chance to try your code. I got an error.
    Says "Run-time error '75':
    path/File access error.

    It highlights:
    Please Login or Register  to view this content.
    I run Vista at home which is where I'm at right now so I'm not sure if that has something to do with it or not..

    EDIT: I tried messing around with your code a bit and got it to do something.

    I changed
    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.
    This writes the new lines to the test.txt file just fine except that there are many spaces in between each number. In column A there will be many spaces in between each number. I should have mentioned that. sorry.
    Last edited by clayton; 05-06-2008 at 12:19 PM.

  47. #47
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I think this might need to be broken down into two parts because I am using this in the Worksheet_Change event. Since I am doing that it writes the values to the text file the first time and then the next numbers entered triggers the event again. Since the file was written to with the current numbers it finds the previously entered number and highlights it. Do you see what I mean?

    Part one should be the detection part in the Worksheet_Change event. If a number entered matches a number in the text file then highlight it. (I already have matching numbers on current sheet covered)

    Part two should be a sub that can be called that will write the new numbers to the text file. Preferably no duplicates unless the user has chosen to ignore the yellow highlight in which case it will write another number in the text file.
    Also have to think about when the file is RE opened... It can't write the same numbers again.. Oh brother...


    Man, I never thought this number checker thing would be so complicated!

  48. #48
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    It can be done, but

    1) Why are you make things more difficult for yourself?

    2) What is the need of storing IDs in a text file ?

    3) Who's gonna maintain the code which you have no idea about what it is doing ?

  49. #49
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    1) Why are you make things more difficult for yourself?
    Um.. Am I?

    2) What is the need of storing IDs in a text file ?
    So when entering the numbers on the worksheet it will see if that number has ever been entered before(10,000 is about 4 years worth of numbers)

    3) Who's gonna maintain the code which you have no idea about what it is doing ?
    Well, once the code is correct there shouldn't be any maintaining to it really.

  50. #50
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    I meant
    This writes the new lines to the test.txt file just fine except that there are many spaces in between each number. In column A there will be many spaces in between each number. I should have mentioned that. sorry.
    I need to see your file.
    Attach it without zip otherwise I can't see.

  51. #51
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Ok, its the Base rolls column that I am comparing. I've added some bogus data so you can see why there are spaces.

    Well crap.. My file is more than 100K so I will post a picture... sorry.
    Attached Images Attached Images
    Last edited by clayton; 05-07-2008 at 09:18 PM.

  52. #52
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Run this first and see if this works as you wanted.
    Back up the text file first, coz it will be overwriting to the text file.
    Please Login or Register  to view this content.

  53. #53
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Almost. When I run it it puts a space under the last number in the text file. If I run it again then there is a space between the last set and the new set of numbers.

    If I only have this run (writing the text file part) when the file is being closed then it shouldn't be a problem really. But it might throw the count off if there is spaces?

    The checking/highlighting part will run each time there is a change in the worksheet.

    So, this kinda needs to be in two parts don't you think?

  54. #54
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    try
    1) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Do you want it to check and update the text file each time you change/enter the value in ColA?

  55. #55
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Ok, with that change a little strange thing is happening. It got rid of the spaces but if there is nothing in the file it puts a space at the beginning. If there is numbers in the file it puts the first number at the end if the last number in the file. Like this: last number in the file is 1004-5, next number after running the script is 33 so it ends up being 1004-533. Then the rest of them are added correctly.

    Do you want it to check and update the text file each time you change/enter the value in ColA?
    Today 09:49 PM
    Yes It needs to check the text file each time there is a change to see if there is a match but it doesn't need to write the file until I close the workbook.
    But this brings up a problem I think.. What if I reopen the file... Make changes and such. Wont that mess things up?

    And the actual range will be B6:B1003.

  56. #56
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  57. #57
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Wiith a blank text file I got an error at:
    Please Login or Register  to view this content.
    After adding a line to the file it worked. Well, it sort of works lol

    If I run it once it gets the numbers and puts them in the file with no space at the beginning.
    IF I run it a second time it deletes the existing numbers in the file and replaces it with the new numbers. If I run it a third time the numbers are inversted. The numbers in the text file are replaced by the ones on the worksheet, minus the ones in the text file. Did that make sence?

  58. #58
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    How about
    Please Login or Register  to view this content.

  59. #59
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    welp, now we got the spaces back. But only one. If I run it with an empty file It works correctly. If I run it again it works correctly but leaves a space between the last number and the first new number.
    I'm sorry this is being such a pain...

  60. #60
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    1) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    2) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  61. #61
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Please Login or Register  to view this content.
    Got error Expected Expression: and it highlighted the red part.
    So, I removed that "," and tried again. Then I got an error saying: Argument not optional and it highlighted the below in red.
    Please Login or Register  to view this content.

  62. #62
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Shold be
    Please Login or Register  to view this content.
    Confused....
    should be
    Please Login or Register  to view this content.
    Last edited by jindon; 05-08-2008 at 01:28 AM.

  63. #63
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Just guessing,
    Please Login or Register  to view this content.

  64. #64
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Ok, it looks to be working correctly except, lol. Except that it is putting the newest entries at the top of the file. I guess this wont be a problem really as long as it trims off the correct ones when it reaches its max. I'll have to check that part out tomorrow though. Gotta get to sleep. Thanks for all your help and I'll check back here tomorrow.

  65. #65
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    Ok, it looks to be working correctly except, lol. Except that it is putting the newest entries at the top of the file. I guess this wont be a problem really as long as it trims off the correct ones when it reaches its max. I'll have to check that part out tomorrow though. Gotta get to sleep. Thanks for all your help and I'll check back here tomorrow.
    Read my last post again

  66. #66
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I gave it a quick shot and its putting the space inbetween again. I gotta hit it. Thanks for the help guys!
    I'll check back tomorrow

  67. #67
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Good morning!
    I am assuming that the
    Please Login or Register  to view this content.
    if what determins how many records to keep so I change the 9999 to 9 and tested to see if it would trim the excess older numbers.

    I found that if the file is blank and I put in 9 numbers and run it writes correctly. I put in a 10th number and run it will put a space between the last number and the newly added one. I put in an 11th number and run it puts a space and the 11th number.
    Its not trimming any of the numbers. Here is what my txt file looks like after adding a set of 8, then one then one then one then one.
    1001-1
    1002-2
    1003-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8

    1009-9

    1010-10

    1011-11

    1012-12
    I also just found that if there are no numbers in the range at all then I get a vb error: Runtime error 1004, No cells were found.

    @
    Please Login or Register  to view this content.
    Last edited by clayton; 05-08-2008 at 11:07 AM.

  68. #68
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Hi

    Thanks for the post and I understand now.
    1) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    2) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  69. #69
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    OK, now the spaces are gone but a new problem has appeared. If I run it with an empty file it puts all the numbers in correctly without any spaces in bettween. Now, if I add a number it will add it to the list. Now the 3rd time I run it (without adding any new numbers) it puts the previous number after the last number.

    for example
    First run it puts in
    1001-1
    1001-2
    1001-3
    1001-4


    Next run after adding 1001-5 it looks correct like this

    1001-1
    1001-2
    1001-3
    1001-4
    1001-5

    Now if I run it again without adding any new numbers I get this:

    1001-1
    1001-2
    1001-3
    1001-4
    1001-5
    1001-4

    Thats how it looks in the text file. But when I just copied and pasted what was actually in the file it pastes like this:

    1001-1
    1001-2
    1001-3
    1001-4

    1001-5

    1001-4


    strange huh?

  70. #70
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    OK
    Can you show me the code so far ?

  71. #71
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Please Login or Register  to view this content.

  72. #72
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    try
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  73. #73
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    That takes us back to:

    1001-1
    1001-2
    1001-3

    1001-4

    1001-5


    1,2,3 first run. 4 second run and 5 3rd run

  74. #74
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    1) Open that text file via Excel
    2) B1 enter =A1="" and C1 =Code(A1)
    3) B1:C1 copy down

    And see what it tells (True/False) in ColB and Error or number in ColC

  75. #75
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Here are the results. I leave work in about 5 minutes so I wont be able to reply after this.
    Attached Images Attached Images

  76. #76
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Hummm, Now I can't think of any to avoid space(s).
    can you try
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  77. #77
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    OK! Now that part looks to be working perfectly :D

    But now there are other problems that I can see happening. One is if I edit an existing number it doesn't change it in the text file.

    Second is when I go over 10 (I changed the 9999 to 9) entries it is not trimming the old ones out.

  78. #78
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    OK! Now that part looks to be working perfectly :D

    But now there are other problems that I can see happening. One is if I edit an existing number it doesn't change it in the text file.
    This should be the next step.
    We should finalize the main sub completely before this.
    Second is when I go over 10 (I changed the 9999 to 9) entries it is not trimming the old ones out.
    There was a typo. CountOfOldODs
    Please Login or Register  to view this content.

  79. #79
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    OK, using the above fixed code I ran the script without adding any new numbers. It didn't do anything. So, I added a new number. It did a few strange things. It took off the two oldest number AND the first digit of the next oldest number.? Not only that but the new number being added was added to the side of the last number in the list.
    It wont paste here correctly so you can see what is going on so I will again attach an image.
    Attached Images Attached Images

  80. #80
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    OK
    Let's find out what it is
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and then can you show me the result?

  81. #81
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    After first run:
    Hello1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10


    AFter 2nd run and adding two new numbers:

    Hello1001-1
    1011-11
    1012-12

  82. #82
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    OK Then how about ?
    Please Login or Register  to view this content.

  83. #83
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    First run:

    1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10

    Second run (added two numbers):

    01-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10

    1011-11
    1012-12


    Third run adding one more number:

    06-6
    1007-7
    1008-8
    1010-10

    1011-11
    1012-12

    1001-1
    1001-2
    1010-10
    1013-13

  84. #84
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Then how about
    Please Login or Register  to view this content.

  85. #85
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    First run:
    1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10


    Second run:
    1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10

    1011-11

    Third run:

    10-10

    1011-11

    1012-12

    1001-1
    1001-2
    1001-3
    1013-13
    1014-14


    I removed one of the "temp As String" because it was doubled.
    Last edited by clayton; 05-12-2008 at 10:19 PM.

  86. #86
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    I just don't understand why
    can you just run this and show me the result ?
    Please Login or Register  to view this content.

  87. #87
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Got an error:
    Method not valid without sutiable object

    and it highlights:
    Please Login or Register  to view this content.

  88. #88
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    Got an error:
    Method not valid without sutiable object

    and it highlights:
    Please Login or Register  to view this content.
    should be

    Please Login or Register  to view this content.

  89. #89
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    First run:
    Hello
    Good-bye

    second run:
    Hello
    Good-bye
    Hello
    Good-bye

    Third run:
    Hello
    Good-bye
    Hello
    Good-bye
    Hello
    Good-bye

  90. #90
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    OK
    Now the reason is clear...
    can you just run the code and read the message ?
    Please Login or Register  to view this content.

  91. #91
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    invalid procedure call:

    Please Login or Register  to view this content.

  92. #92
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Can you run it with the text file with some data (not blank)?

  93. #93
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    sorry, I should have thought of that.

    The message box says
    Right(txt,1=0
    Asc(Right(txt,1))=48

  94. #94
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    How about
    Please Login or Register  to view this content.

  95. #95
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    First run:

    1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10

    Second run(added one):

    1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10
    1011-11

    Third run (added two):

    01-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1010-10
    1011-11
    1012-12
    1013-13

  96. #96
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  97. #97
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    1st run:
    1001-1
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1009-9
    1010-10

    2nd run:
    I got an error: Type Missmatch
    Please Login or Register  to view this content.

  98. #98
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Ahh
    That line should be
    Please Login or Register  to view this content.

  99. #99
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    whow! You might be on to something here

    Second run:
    1001-2
    1001-3
    1004-4
    1005-5
    1006-6
    1007-7
    1008-8
    1009-9
    1010-10
    1011-11

    Third run:
    1005-5
    1006-6
    1007-7
    1008-8
    1009-9
    1010-10
    1011-11
    1001-1
    1012-12
    1013-13


    Until the third run I was excited lol
    How did that 1001-1 get back in there?

  100. #100
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    Until the third run I was excited lol
    How did that 1001-1 get back in there?
    Because it is only avoiding duplicates, so the New list contains the data that is not in the text file, it adds to it.

    I'll just have a lunch break. (30 min perhaps)
    Last edited by jindon; 05-12-2008 at 11:58 PM.

  101. #101
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Sounds good to me

    You made a lot of progress tonight. I'll test it much more tomorrow as I'm leaving work now.
    THANKS!

  102. #102
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Then
    to sheet1 Worksheet module
    after paste the code, select other sheet just once and come back to the sheet.
    Please Login or Register  to view this content.

  103. #103
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I just got a chance to try the above and I got an error when entering a new number. Its in the Worksheet_Change event.

    Error was: Object required
    and it higlights:
    Please Login or Register  to view this content.

  104. #104
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    If Tareget.Column <> 1 Then

    isnt that target?

  105. #105
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    In your reply I just noticed the typo. I fixed that and it ran. I'll check the results in a few. Lots of work to get done today

  106. #106
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I fixed that, THEN added a new number and got the same error @

    Please Login or Register  to view this content.
    And the data is in column A for my test workbook.

  107. #107
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    I fixed that, THEN added a new number and got the same error @

    Please Login or Register  to view this content.
    And the data is in column A for my test workbook.
    Then how it goes ?

  108. #108
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Well, I change the "target" to "trget" since thats the way it was set and it all worked.

    I think this is doing everything it is intended to do.

    I have to test it in a more "real world" environment to see if its going to do what I wanted it to.

    Thanks for your help. I'll post back when I've checked it out more. Which, I'm going to try to do now.

  109. #109
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Ok

    Test it hard and post back when you find any thing wrong.

  110. #110
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I am unable to get this to work in my real workbook...

    I've change the ranges to match the B column but maybe I've done something wrong.. This is what I have now. My numbers to be checked are in column B. The range is b6:b1103.

    This is in the worksheet_change sub.

    Please Login or Register  to view this content.
    did I do something wrong there?

  111. #111
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    I am unable to get this to work in my real workbook...

    I've change the ranges to match the B column but maybe I've done something wrong.. This is what I have now. My numbers to be checked are in column B. The range is b6:b1103.

    This is in the worksheet_change sub.

    Please Login or Register  to view this content.
    did I do something wrong there?
    change to
    Please Login or Register  to view this content.

  112. #112
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    OK I did that and it is still not writing the txt file.

    This is what I've got so far.
    In the Workbook_change sub

    Please Login or Register  to view this content.
    In workbook_activate I have:

    Please Login or Register  to view this content.
    and at the very top of the code for the worksheet I have:

    Please Login or Register  to view this content.
    Do you see anything wrong with the way I have it set up?

  113. #113
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Try
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  114. #114
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    That worked

    So, I started messing with it. Adding, taking away and stuff. Looks like you covered many bases :D But, I just got an error and I'm not sure why. I was just adding more numbers and I got this error:

    Script out of range
    Please Login or Register  to view this content.
    Should I put an On error resume next or something?

    I have to leave at midnight so I only have about 10 minutes left here.

  115. #115
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  116. #116
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    That fixed it right up! I'll test it more tomorrow. Thanks again

  117. #117
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    Before I leave I wanted to ask one more question. When it highlights a cell is it possible to add a comment to that cell as well?

  118. #118
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Yep!
    I'll add that functionality to the code.

  119. #119
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    try
    Please Login or Register  to view this content.

  120. #120
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I tried that but got an error.
    Please Login or Register  to view this content.
    I tried dic1 and dic2 but regardless I am now not getting anything written to the text file and it is not highlighting the existing numbers either..

    EDIT: I commented out the "On error resume next" and found that it was stopping at:
    Please Login or Register  to view this content.
    because the form is protected. So, I unprotected it and it got past that point.
    Now, I''m getting another error. Object variable or With block variable not set and it stops at:
    Please Login or Register  to view this content.
    Last edited by clayton; 05-14-2008 at 04:56 PM.

  121. #121
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Seems few typos...
    Please Login or Register  to view this content.

  122. #122
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I tried it and put a STOP in it so I could see what it was doing. Its not getting past this part
    Please Login or Register  to view this content.
    It thinks the range is nothing...

  123. #123
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Oh my gosh!!!

    Over 122 posts in this thread....

    That's ridiculous.....

    How much of this can involve the original question?

  124. #124
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by davesexcel
    Oh my gosh!!!

    Over 122 posts in this thread....

    That's ridiculous.....

    How much of this can involve the original question?
    Should we stop or what ?

  125. #125
    Forum Contributor
    Join Date
    01-07-2004
    Posts
    192
    I didn't know there was a limit

    Jindon, maybe we should converse threw the PM system. What do you think?

    Or email. Whichever works for you.
    Last edited by clayton; 05-14-2008 at 09:01 PM.

  126. #126
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    Quote Originally Posted by clayton
    I didn't know there was a limit

    Jindon, maybe we should converse threw the PM system. What do you think?

    Or email. Whichever works for you.
    Let's stop here anyway.

  127. #127
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    Oh my gosh!!!

    Over 122 posts in this thread....

    That's ridiculous.....

    How much of this can involve the original question?
    i think perhaps if someone stepped in here with a better solution that would be a fair comment.
    however jindon is doing an explanatory example of help and should be congratulated

  128. #128
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Gentlemen, Firstly let me congratulate shg and Jindon for a classic example of how a forum like this should be run, perfect in every way, helpful, informative and patient the original poster would certainly be happy and recommending this site to every would be coder he comes across!

    Secondly, there is no limit to the amount of posts a thread takes to come to a conclusion, after i read through all the posts (yep every one!) i didn't see the thread side track from the original issues only develop in a more structured way!

    Gents, continue with your thread if you feel it pertinent and feel the Op would benefit further from it!

    If you wish, start a new thread with the last useful posts from this one and a link to it.

    Keep up the fantastic work guys!
    Not all forums are the same - seek and you shall find

  129. #129
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    This line:
    Please Login or Register  to view this content.
    is looking for a specialcells of the type 2,2, i don't believe you can specify the cell type with a number, if it were possible type 2 would be xlCellTypeBlanks and the 2 after the comma would be the value of that special cell i.e a blank cell with a value of 2? not possible!. Using SpecialCells (x,x) isn't the same as using Cells(x,x).

  130. #130
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Please Login or Register  to view this content.
    is the same as
    SpecialCells(xlCellTypeConstants, xlTextValues)
    The disadvantage of using literals is that it makes code impossible to read.

  131. #131
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465
    martindwilson and Simon,

    Thanks for the message and I really appreciate it.
    If OP comes back here, I have no objection to continue.

    Please Login or Register  to view this content.
    Should read the appropreate range, due to the numbers are actually look like

    101-1
    101-2

    They are not real numbers...

  132. #132
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hmmmmm, guess i misread that one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1