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:

[F]=if(left(C3)="A",1,if(G3="English",2,if(G3="French",3,"N/A"))),"N/A")[/F]

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 have a table where some cells are null, others have values. Is there a way to find the column number (or value from column header) of the first cell, left-to-right, that has any value present?

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: Look up and return matching value to left of the look up column

Thanks. I did do some searching first, but I never considered the phrase "left look up." I used VLOOKUP in the header because I was asking if there was a way to do this function with a left column, maybe by a negative reference of some kind.

Re: VLOOKUP on other than first column of table

Perfect! Works fine, and I did a timing test and it runs as fast as the equivalent VLOOKUP function.

Is there any way to do a lookup on a table, where the key is not the first column. I have a table where the key on which I need to search is in column C, and the value I need to extract is in column A. The value is text, so I can't use a SUMIF function.

Is there any way to disable the center button function whereby if I press the button (actually the roller), it changes the mouse to scrolling mode that moves all over the screen?

Re: Working with multiple selected rows or columns

I found the solution to my problem and it's simple. Just use "Find rcells in Selection.Rows" and it loops by row, setting rcell to the range for the row! Easy if you know how to do it....

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).

I know how to process multiple selections of cells, using "For Each rCell in Selection" processing. Is there a similar way to process each entire row or column selected, without having to manually loop thru thousands or millions of individual cells?

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:

Dim sListData(1 To 256, 1 To 3) As String
    With Worksheets("Basis")
        BasisCategory.ListBox1.ColumnCount = 3
        iListCount = 0
        For i = Range("BasisVendor").Row To iLoopMax Step 1
            If Len(.Cells(i, 2)) > 1 Then
                If .Cells(i, 4) <> "" Then sVendor = .Cells(i, 4)
                iListCount = iListCount + 1
                sListData(iListCount, 1) = Left(.Cells(i, 2) & Space(8), 8)
                sListData(iListCount, 2) = Left(sVendor & Space(18), 18)
                sListData(iListCount, 3) = Left(.Cells(i, 3 & Space(39), 39)
            End If
        Next
        BasisCategory.ListBox1.List = sListData
    End With
    BasisCategory.Show

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.

Thanks.