Posts by thedogg

    I am trying to figure out the code which will generate txt file from my active workbook with Encoding: Unicode. Do you have any ideas how to do so? I have chinese characters in XLSX file and I must keep them in txt.

    DS_LOOKUIP_.xlsx - yes this is the file stored on network, ids and pn inside.
    Part numbers can be duplicated but ID is always different. Vlookup is taking the first available ID. sorted: part number lowest to largest and ID largest to lowest.
    File has more worksheets, related to different functions.


    In destination file I want to have ID in column I.
    In destination workbook part numbers can be also repeted multiple times.

    1. My macro is prepareing internal file with amound bill of materials defined by user. Every BOM is in separated worksheet. I can have just one or more than one, even 1k.
    2. In column B I have part number and I want to find related ID for that part number from file strored on network.
    3. In the file stored on network I have worksheet called IDs with all part numbers and IDs which are related to them.
    4. I am runing the macro which includes formulas from 1st post and the macro is checing IDs for all numbers.
    5. The loop is between all worksheets.
    6. The excel sometimes crashs in that vlookup step. Sometimes it is for worksheet 1 sometimes it is for worksheet 20, so 20 times it works byt suddenly it crashes. It is always the same vlookup function from 1st post but for different worksheet only.

    Don't you have all this information in previous code? In general it works well but when I have one BOM in one worksheet and I have for example 100 BOMs - 100 worksheets then I run the code 100 times for each worksheet in entire workbook then it crashes excel, sometimes in BOM 10 (worksheet 10) sometimes for BOM 99 (worksheet 99), this is my problem

    I have a code like below. The same code is runing several times in one macro, sometimes excel crashs during this step. It is only happen if I am using the excel for a long time and I have done many operations before. With fresh excel it always works I can do it even 100 times. I am wondering why? Can be that it is related to RAM usage in that moment. Is there any possibility to replace my code by something more stable?


    Code
    Dim rngS As Range Set rngS = Range("S11:S" & END_Row - 2)      
    With rngS  
    .FormulaR1C1 = "=VLOOKUP(RC[17],'\\ad.igt.com\igt\TEAMS\ TOOL_ADMIN\###IAT Management###\[DS_LOOKUIP_.xlsx]IMDS IDs'!C2:C8,7,0)"  
    .Copy  
    .PasteSpecial xlPasteValues    
    End With  
      Application.CutCopyMode = False

    The problem is when I close the workbook. The macro stops working.


    I want to close all workbooks with sheet1 <> BOM. and sheet1.tab.color <>5296274 or 49407 or 49407.


    I have been fighting with it for two days already. I can just met one condition, impossible to have it for both together. Could you please support?

    I'd like to count how many of my sheets in workbook has tab in green. I need this check just for visible sheets.
    For example:


    I have 5 sheets +1 hiden, two sheets are green and two white. I would like to display how may tabs is green and how many is white except hiden sheet.

    I have following function


    Code
    .FormulaR1C1 = "=VLOOKUP(RC[-13], 'K:\ME\20-IM\[IM_LOOKUIP.xlsx]DW IDs'!C1:C7,7,0)"
    
    
    .FormulaR1C1 = "=VLOOKUP(RC[-13], '\\ad.com\czoch\Departments\ME\20-IM\[IM_LOOKUIP.xlsx]DW IDs'!C1:C7,7,0)"


    How can I replace K: drive on UNC path. I have used the full drive name but every time when vlookup is staring Excel asked me to select the file. Can I avoid it somehow? I have created my tool for more than one user and Id like to avoid to have drive letter in path.

    Is there any way to make icon in my command bar for command buttons in ribbon a little bit bigger? I am adding buttons using code below.


    I have multiple sheets. In each of this sheets structure is always the same.
    In column B, starting from B12 to B.value=END
    In column I is ID number or text MISSING (if ID is not available) related with number from column B.
    In column E is weight.
    In column K is supplier ID.


    I need macro which will be able to transfer new IDs inserted manually in column ID for the rest sheets where Number from column B appears. The condition only is that weight and supplier have to be the same. Id like to have it for all sheets except the last one.


    For example.
    I have 3 sheets.


    In sheet 1 I have data in range(B12 to B20 (END)). In sheet 2 I have data in range(B12 to B50 (END)). In sheet 3 I have data in range(B12 to B20 (END)).
    Considered number is in sheet 1 in Cell B 14, and in sheet two in cells B16 and B11. In sheet 3 in cell 14 too. Next considered number is in sheet 2 in B14, and in sheet 3 in cell B15.
    The issue is, when I replace value MISSING by numeric value in one of sheets (active sheet only) Id like to transfer the same value to the rest of sheets if weight and supplier and number are the same.
    Active sheet should be always MASTER.


    Could you support?