Posts by pnocero

    This is a wierd one. If I start Excel 2007 by double-clicking on the file name, it opens differently at alternate times. One time it opens OK, showing the Personal macro if I do a View>Un-hide, and it shows the Google toolbar. The next time I open the same file, Excel does not give me access to un-hide the Personal macro nor does it show the Google toolbar. This happens on a fairly consistent alternate basis,one time allowing access to this information, the next time not, the next time allowing it, etc. I told you it was wierd. Has anyone ever seen this happen, or even better, has a solution?

    I have a macro that modifies the Excel file such that I want to make sure the user does NOT save it back as the original file. There are many times that the new file will be viewed and not saved, but if the user tries to save it later, it needs to request a new filename as Save As. How do I remove the current filename that is recorded for the file?

    Re: combine 4 if statements

    I'm not clear about your fourth criteria above -- is there already a formula in cell C3?

    If not, try:


    If there is already a formula in cell C3, then combine it with this formula (probably putting it in place of the "N/A" above). Note, the result in C3 will be "N/A" if cell A3 does not start with "A" or cell G3 does not contain one of the three words listed. Propagate this formula to all applicable rows.

    EDIT By MODERATOR: [COLOR="#FF0000"]Do not use code tags with formula - - they are reserved for use only when posting VBA code. Use the formula tags instead.
    Your code tags have been replaced.[/COLOR]

    I need to highlight every other row in a worksheet for readability, which is easy to do with a conditional format using the ISEVEN(ROW()) function. But this overrides any manually-entered cell color. Is there a way to include a test for no color (or any color) in the conditional format so this won't apply if the cell is manually colored?

    Is there a way to do the equivalent of a MATCH function (or similar) two determine whether or not a particular string is present in a multi-row, multi-column table? I don't need the resulting row or column number, just whether or not it matches any entry in the table. It would also be helpful to know how many items it matches, but this is a secondary need only.

    Re: VLOOKUP with dynamic creation of source file name

    I found the way to do it. I had used INDIRECT incorrectly. If you use VLOOKUP(ab1,INDIRECT("string"),1,0)) it works correctly. The "string" needs to include the full file and range reference, and can include references to other cells, concatenation, etc.

    I need to create a VLOOKUP function to extract data from another file, but the name of the file to examine needs to be created dynamically, as the specific file name depends upon other data in the worksheet. How do I create this dynamic name in VLOOKUP? I tried using the INDIRECT function, but I couldn't get this to work.

    Can WITH statements be nested, where the End With on the nested statements returns to the original With definition. That is:

    With Worksheets(Sheet1)
        .cell(...) references cell on Sheet1
        With Worksheets(Sheet2)
                .cell(...) references cell on Sheet2
        End With
        .cell(...) again references cell on Sheet1
    End With

    Re: Working with multiple selected rows or columns

    What I want to do is to allow the selection of multiple rows, and then do a "For Each..." type function so I can do my processing for each row individually. It's not a search/replace or anything like that -- my procedure processes certain cells in columns within each row for special purposes.

    I could do a standard "For Each cell in Selection" and only process when I hit column 1 in each row, but that means cycling thru 16k+ cells of cells to get each row, and over a million cells if they erroneously select a column (my procedure will need to check for this).

    Re: Populate Multiple Column Listbox on Userform

    Can this approach be used if I need to support multiple columns? That't where I am having the problem -- I know how to do this for a single-column listbox. Any recommendations here? I'm investigating using an array to fill the listbox (which works), and to somehow use a REDIM to change the array size, but I'm new to using REDIM and I'm having problems getting this to work. Thanks.

    I have an occasional problem when executing long-running macros. Sometimes, and there is no consistency to it, the Excel window blanks out entirely, just showing a white screen, till the macro completes. Next time it works fine, the next time it happens at a different time, etc.

    I have screen updating turned off during the execution of the macro, but I do have it turn on and then off again at times to show the interim results.

    Any ideas what is happening here, and how to prevent it?

    Re: Populate Multiple Column Listbox on Userform

    Thanks, but its the number of rows, not columns, I'm concerned about. I have an array with a variable number of entries, and I want the listbox to know how many to show. Right now it shows all the rows in the array, regardless of how many actually have data in them. I populate the first part of the array with data, and I want only those shown, not multiple pages of blank entries after the last item as is happening now.

    I understand you can populate a multiple-column listbox by building an array and then setting the Listbox.List parameter to this array. This works, but it uses the total number of rows in the array, regardless of how many actually have data.

    Here's the code I use, which works fine:

    But is there any way to limit the number of rows displayed in the listbox to the number in this array (iListCount)/ I tried doing a ReDim, but I couldn't get this to work. The compiler kept telling me that the array was already defined.