Posts by braveheart

    I have multiple workbooks on a network drive that are Bills of Materials (BOMs) for controlling what type of components are mounted on a printed circuit. Occasionally components become obsolete. When they do, I have to find the BOM(s) that the component is used on. Each of the workbooks are formatted the same. There are 2 hidden worksheets and a worksheet called ‘CUSTOMER BOM’.
    With VBA code, I want to do a search (Where Used) across all of these workbooks looking at the ‘CUSTOMER BOM’ worksheet and in Column B starting at Row 15 look for a particular string of text that would be inputted by the user through a query box. If the string is found I would like to see the name(s) of the workbook(s) listed (if the workbooks can be hyperlinked that would be a plus but not necessary). The search string may be in more than one cell in the column but that does not matter – I’m only interested in the ‘Where Used’ workbook(s) after it finds the first instance. If the string is not found (i.e. a typo) then the feed back would be ‘String” not found.
    I’m attaching 3 small Excel files as examples.
    If I entered ‘ABC-00001’ into the query box, the response should be BOM1.xls and BOM2.xls.
    If I entered ‘DEF-00001’ into the query box the response should be BOM1.xls and BOM3.xls.
    If I entered ‘GHK-00001’ (typo – the K should be a J) into the query box the response should be GHK-00001 not found.
    Any help would sure be appreciated. Thank You



      (124.66 kB, downloaded 33 times, last: )

    Re: Search Numbers Within Ranges

    I apologize but I 'm not making myself clear. So let me try again. In column F I need to look at that number and compare it to the ranges that are in Column C to D. The total count of the numbers in column F found in these ranges should be placed next to the range. I'm attaching my file again with examples of what I'm looking for. For example in column F the numbers 60 and 150 fall within the range of 1 to 156 therefore the total next to this range would be 2. There are no numbers in Column F that match the next range 157-312 therefore the total would be 0 or blank. The next number in coulumn F is 378. This would fall in the range of 313-468 therefore the total next to it would be 1. Numbers 534 and 624 fall between the range of 469-624 therefore the total would be 2 next to that range. I hope this explains it a little better as to what I'm looking for. Thanks


    • Range Test.xls

      (27.14 kB, downloaded 26 times, last: )

    Re: Search Numbers Within Ranges

    Thanks Joseph, that helps for the data range in row 3, however I have multiple ranges in columns C and D and I don't know how to change the formula. Please see my attachment for examples of my lists.


    I have columns of data that I’m trying to count the number of times that one column’s numbers fall between 2 other columns range.
    For the numbers in column F I’m trying to get a count of the number of times that the numbers appear between the ranges in columns C to D. The total would increment in column E as these numbers were found in the range.
    Example: The numbers 60 (F2) and 150 (F3) would appear in the range 1 (C1) to 156 (D1). The count would be 2 which would appear in cell E1.
    Any help to come up with a formula would be greatly appreciated. If it is easier to do as a macro then I would accept that code also. Thanking You in advance.

    I'm looking for some VBA code that would look at a column of data, look at each cell value in that column and if the cell value starts with the letter 'G' and the 5th character in that cell is not 'Z' or 'X' I would like to highlight that cell. Example:G123-123 would be highlighted whereas G123Z-123 would not.
    Thanking You in Advance

    Re: Copy Worksheet to Sheet 2 of multiple Workbooks

    I have come up with scenario which works for me, however when I open up the files the data on sheet 2 is still highlighted. After the PasteSpecial what can I do to turn off the highlights? Thanks

    I have an Excel file that each day I add new data to it. I’m looking for some VBA code that would copy the worksheet and paste it into Sheet 2 of 5 different Workbooks throughout the company network then saving and closing those Workbooks after pasting. I've seen some code on the site that shows copying data from multiple Workbooks to one but not the other way around. Any guidance would be greatly appreciated. Thank You in Advance

    Re: Save output to a .txt file from Excel

    Apologies for the delay. Work is so hectic. I don't think I made myself clear. The file I read into Excel can be of varying length in name but always with a .exp extension (it is a text file). What I'm attempting to do is grab the left portion of the file name and append .txt to it and then do a Save As with that name so the user doen't have to type in the file name in the Save As box. I read in ABC123.exp into Excel, after manipulation with VBA code, I want to save the changes as ABC123.txt using VBA code. The 'ABC123' again can be of varying length (as I have many people using the macro) so I'm looking for some sort of Left Function ? before the .(period) and append txt to it. Hope this makes it clearer.

    I have a text file output from a program that is named “ABC123.exp”. I open up the file with Excel and after manipulating the data with VBA code which includes identifying the Source Path directory(strSourcePath As String ) and the File name (strFileName As String) I want to be able to open up a 'Save As' Window and save the manipulated data into the same Source Path directory(strSourcePath) with the File Name being the prefix of the original with a suffix .txt extension. Ex: “ABC123.txt” instead of “ABC123.exp” and the Save as type: Text (Tab delimited)(*.txt). This way my original data won’t be overwritten. Any VBA code to accomplish this would be most appreciated.
    Thanks in advance

    Re: Compare Columns and overwrite data when matched

    Batman, Just got in from having a wedding anniversary(34) dinner with my wife and I thought I'd just check in to Ozgrid. Yeah, it was O.K. with her. I was pleasantly surprised to see a response to my question on a holiday weekend. I copied your code and tried it on a different file and it worked. I can't thank you enough. What a great site.
    Best Regards, braveheart

    I have 2 Excel files. One will be the master list which will keep growing. I’ve added this data to Sheet 2 for my request rather than send 2 files. The second file (many customers) will contain data of varying width and length that I want to compare to the master list and replace cell values with new cell values from the master list. I’ve tried Vlookup, however I don’t want the new data in the cell next to the match but rather to replace the match.
    I would like to do this with a VBA macro.
    I need to find ‘Part Number’ in Row 2 (any column, it may not be the last as shown for this example) then compare this column to the data in column 1 of sheet 2. If there is a match then the cell(s) found on sheet 1 would be replaced by the cells in column 3 of sheet 2. I have copied my data twice on sheet 1.The first set uses Vlookup as an example of the data I’m looking for. I don’t want the text in the next column as it may actually overwrite data if ‘Part Number’ is not the last column. The 2nd set (my goal) I have highlighted in green when there was a match with the original cell values being overwritten. The green fill would be nice to have in the code but not necessary.
    Any help would be appreciated. Thanking you in advance.



      (18.92 kB, downloaded 75 times, last: )