Posts by eduboys

    =SUM(IF(Data!$B$1:$B$5375='Actual time booked to projects'!I$1,IF(Data!$BE$1:$BE$5375='Actual time booked to projects'!$B47,IF(NOT(ISNUMBER(MATCH(Data!$BH$1:$BH$5375,{"Quality Assurance","Business Analysis","Arctic BA","MLITS_QA*"},0))),Data!$CA$1:$CA$5375,""),""),""))


    I'm using the following formula to lookup some data from a data tab in my workbook.


    Unfortunately the wildcard within "MLITS_QA*" is not working correctly, is it possible to use a wildcard in this scenario? If not is there anyway i can modify my formula?

    Re: Convert a Date to a String in VBA


    Quote from Dave Hawley

    See Also CStr Function


    The Cstr function did the trick. Thanks. Its amazing how much searching one can do, and still not find the simplest solution. I probably spent about an hour on the web and in VB help, and still could not find the CStr function. This board is the greatest!

    Re: Convert a Date to a String in VBA


    For some reason the formula:


    if c = 2/3/2006 and b = "hi"


    a = b + c


    was not working due to the date being read in from the excel file. Not quite sure why it isn't working, but it isn't.


    I can output both b and c separately, but when I try to add the two, it exits my function without completing.

    How do I convert a date to a string in VBA?

    Code
    Dim a As string
    
    
    'a is being read from a cell in excel that is a date
    a = "2/3/2006"


    So how do I get the variable a to act like a string. When I attempt to add to another string in excel I receive an error.


    Thanks in advance.

    I am attempting to find a method whereby I can populate a variable amount of rows based upon a lookup in a list.


    For example:


    I have a flat list of all names, IDs, and initiatives. An initiative may have multiple names and IDs. Each ID corresponds to only one name.


    At most an initiative may have 10 Names and their corresponding IDs.


    As I would prefer not to write a macro, I was wondering if there was any method I could use to create 10 formulas that would auto-populate only the names for the selected initiative. My input would be a cell where I use data validation that is tied to a list of initiatives.


    If I had an initiative that had 7 names associated with it, I would want formulas 8 through 10 to return a blank result.


    See attached example list:


    Thanks in advance for your thoughts.

    Re: Sum If Array Formula - Consolidation


    Any idea if this will improve performance as well? I have probably close to 1000 formulas like this on a worksheet, and when I wind up looking through about 1500 rows, my macro takes about 2 minutes to complete. It does several other modifications to the spreadsheet.

    I am having some problems trying to consolidate my Sum(If( array Formulas:


    Code
    {=SUM(IF('All Issues'!$F$1:$F$5="Business Planning",IF('All Issues'!$L$1:$L$5="Closed",IF('All Issues'!$C$1:$C$5="Low",IF('All Issues'!$W$1:$W$5="Duplicate",1,0)))))+SUM(IF('All Issues'!$F$1:$F$5="Business Planning",IF('All Issues'!$L$1:$L$5="Newly Closed",IF('All Issues'!$C$1:$C$5="Low",IF('All Issues'!$W$1:$W$5="Duplicate",1,0)))))+SUM(IF('All Issues'!$F$1:$F$5="Issue Management",IF('All Issues'!$L$1:$L$5="Closed",IF('All Issues'!$C$1:$C$5="Low",IF('All Issues'!$W$1:$W$5="Duplicate",1,0)))))+SUM(IF('All Issues'!$F$1:$F$5="Issue Management",IF('All Issues'!$L$1:$L$5="Newly Closed",IF('All Issues'!$C$1:$C$5="Low",IF('All Issues'!$W$1:$W$5="Duplicate",1,0)))))+SUM(IF('All Issues'!$F$1:$F$5="Technical Infrastructure",IF('All Issues'!$L$1:$L$5="Closed",IF('All Issues'!$C$1:$C$5="Low",IF('All Issues'!$W$1:$W$5="Duplicate",1,0)))))+SUM(IF('All Issues'!$F$1:$F$5="Technical Infrastructure",IF('All Issues'!$L$1:$L$5="Newly Closed",IF('All Issues'!$C$1:$C$5="Low",IF('All Issues'!$W$1:$W$5="Duplicate",1,0)))))}


    Basically I'm putting together a summary sheets that supply me with statistics from a dump of data. I'm actually running into issues that the formulas are too long, but when I tried to use an "AND" formula within array formula I did not receive the correct results. Is there any other way I can consolidate these formulas?


    Thanks in advance!

    I am attempting to write a formula to do conditial formatting based on a dynamic list name.


    The formula:


    Code
    =ISNA(MATCH($A$1,SUBSTITUE($A$2," ","_"),0)


    the substitute portion of the formula returns a string, but I really want to be referencing a range within the formula, so this is causing an error. Any way to avoid this erorr?

    Re: Ending a Looping Macro


    Try adding the following.. Works for errors when it asks you to overwrite a file


    Code
    Sub XXX_Loop() 
        Application.DisplayAlerts = False
        Do 
            XXX 
        Loop 
        Application.DisplayAlerts = True
    End Sub

    Re: Deleting cells in a for each range loop


    To speed the process up a bit I sorted my range by the second column. This way all 0's were together. Then I just deleted all rows that were 0 by changing the line:


    Code
    Range(Cells(r, 1), Cells(r, 2)).Delete Shift:=xlUp 
    'To
    Range(Cells(r, 1), Cells(FirstRow, 2)).Delete Shift:=xlUp



    Finzalized Code (Only one deletion needed)

    "Submitter" = A16:B30
    That range varies depending on the amount of data in the file.


    There will be certain cells within the B Column that contain a value of 0 as a result of a formula. I want to delete and shift cells up in columns A and B for every row that has a value of 0 in column B.


    The code below will not work if I have 2 consecutive rows that contain 0 values in column B, does anyone have a quick fix?



    Code
    Dim testCell As Range
        For Each testCell In Range("Submitter")
            If testCell.Value = 0 Then
                testCell.Select
                Range(Selection, Selection.End(xlToLeft)).Select
                Selection.Delete Shift:=xlUp
            End If
        Next