Posts by DWildman

    Re: Astricks for the first five numbers in a SSN

    Just insert a new column next to your SSN column and use a formula to isolate the last 4 digits. For example if your SSN's are in column A, place the following formula in a new column...


    You can then just hide the full SSN column before printing.

    If this isnt what you are looking for then please provide additional details regarding how your data is set up.

    Re: Script To Query, Or

    Hi Veradus,

    Unfortunately this type of request doesnt have a straightforward solution but I threw something together for one of the sites you specified in the attached file. I rearranged the columns of your sheet a little and for some address+zip code combos there is no exact match, in which case the sub will result in a blank estimate. I havent been able to contibute to the forum lately so if anyone else would like to jump in here that would be great. Otherwise, if you can provide more test cases I will try to hook you up with a full solution when I can. Here is the code used....

    This is still a little unrefined but let me know how it works. I have found that you may get different results depending on OS and XL version.

    Hope it helps

    Re: Bold Common Text In 2 Strings

    Hi gruebz,

    I went with a bit of a different method, which is fairly solid but also needs a few adjustments.

    • Given 2 strings (str1 and str2)
    • Extract first word from each string and compare
    • If words are same, get next word
    • If words are different
    • Bold str2 word and then see if any other str2 words match the str1 word
    • If any other str2 words match, then assume that words/phrases have been inserted into the middle of the original phrase
    • Remove that word from the str2 if it doesnt exist
    • Continue

    I apologize if Im being a bit vague with my explaination. I think I went with a bad approach by strictly manipulating strings (I think it might be easier storing each word in an array and working with that).

    Anyway...The flaw exists if there are duplicate words in String2 Ill check it tomorrow.

    Cool problem

    Re: Bold Common Text In 2 Strings

    Quote from gruebz

    I know this is a tricky one and may be impossible to do the way I am wanting to do it.

    Well, that will be a bit of a challenge. Certainly not impossible (hard for me to believe anything is).... I will give it a rip.

    Re: Bold Common Text In 2 Strings

    Hi gruebz,

    As per previous post it seems that a word comparison will suit your needs rather than a true string compare. The following sub compares each word in string2 to the corresponding word in string1 and bolds the string2 word if it does not occur at the same position in string1.

    String1 must be in cell A2 and string2 in cell A3.

    Let me know how it works out for you.

    Re: Compare 2 Strings and Bold the Result

    Just to clarify...are you actually just looking for a word comparison? How precise are you looking to get. For example:

    Str1 = "Hello World"
    Str2 = "Hello Wurld"

    Would you like all of "Wurld" bolded or just the "u"? Or shall we assume that every word will always be correctly spelled?


    Re: Referencing Worksheets With Variable Name

    Hi Sam - Welcome to the forum!
    If resp is a string variable which contains a worksheet name, then you can reference the worksheet using that variable with...


    Also, Im pretty sure that assigning a range to a single cell doesnt work (someone correct if Im wrong). So changing your last line to...

    ActiveCell.FormulaR1C1 = Sheets(resp).Range("A1:A200")

    wont quite do what your looking for. Instead I think a copy-paste is what you need. This code should work out for you...

    Sub Test()
        Sheets("Data Table").Range("IV4").End(xlToLeft)(1).Offset(0, 1) = resp
        Sheets("Data Table").Range("IV6").End(xlToLeft)(1).Offset(0, 1).PasteSpecial
    End Sub

    Note that using Select/Activate/ActiveCell can be eliminated

    Hope it helps

    Re: Concatenate & Paste Visible Cells


    For anyone else reading this, I would appreciate any corrections or simplifications that could have been made. Thanks.

    Just as a simplification, using Select/Selection/Activate is hardly (if ever) necessary. Also, if you are maintaining a "Concatenate" column, you would want to paste over only the values and not the formula as this would yield incorrect results. I would also go one step further and reference the CONCAT column and column D dynamically so if rows are added, the code would not need to be changed. So something like this...

    Hope it helps
    -DWildman[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Sorry...I am assuming that a CONCATENATE column is being maintained in columns C

    Re: Open CSV Files By Date

    Hi Leif,

    Sry for the late reply...

    I cant see why you are getting a "file not found" error if the workbook containing the macro is in the same folder as the file that you need to open. The previous code I provided works fine for me but you can try this...

    Workbooks.OpenText filename:=ThisWorkbook.path & "\" & sNewest

    Hope it helps

    Re: Open CSV Files By Date

    Ooppps...Sorry, your right. I made a mistake (see code).

    My previous code was calling the Dir() function again at that line and getting the next file prematurely. This should now be what your looking for.


    I am wondering if the solution could be made simpler from the fact that I have discovered that the file that I want to identify and open is always the last file in the list in that folder

    Probably yes. However, I would stick with this as it is independent of the file postion in the folder, and will be more reusable.

    Let me know how it works out.

    Re: Open CSV Files By Date

    Hmmm - It works fine for me.

    Is the workbook that contains the code in the same folder as your .csv files?

    Do all of your file names end in .csv?

    Re: Update Worksheet Column Number After Removing Row From A Userform


    How is your actual spreadsheet set up?

    If the plan numbers will always be relative to the row numbers then you can sub the plan numbers with a simple worksheet formula to keep them updated when you delete a row.

    For example, say column A is your Plan# column. If the numbers always start at 1 (in A2) and are sequential...then you could use something like =ROW() - 1.

    If this is not clear then perhaps you could post an example

    Re: Open CSV Files By Date


    This sub needs to be in a workbook that is in the same directory as all your .CSV files. It loops through all .CSV file in said directory, and the variable sNewest will hold the name of the most recently modified .CSV file.

    I used the link below as a reference…k/html/office09072000.asp