Posts by eduboys

How do I change the scaling in page setup in VBA? I can't find the option when I look at a recorded macro.

Re: Wildcard not working in a MATCH statement in an array formula

That did the trick Domenic I was experimenting with putting the left in the match condition, but it wasn't working.

Re: Wildcard not working in a MATCH statement in an array formula

I'll look into DSUM, but is there any reason why the MATCH formula would not work within the array?

=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?

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.

Re: Populate a variable amount of rows based upon a lookup

Absolutely amazing! I will need to disect the formula and figure out what you did.

Thanks!

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.

Re: Sum If Array Formula - Consolidation

That did the trick... thanks!

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

{=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!

Re: Formula referencing a dynamic range

Awesome, Thanks.

I typed the post a bit too quick when I wrote the word "Substitue", but it was correct in my sheet.

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

The formula:

=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: list all combinations?

Saved me a whole lot of time... thanks for the code!

Re: Hight Multi-Column Rows Containing 0?

I think the original poster is asking how to conditially format an entire row based on the contents of a single column in that row.

Re: Ending a Looping Macro

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

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:

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)

Sub test() 
    Dim r As Long, FirstRow As Long, LastRow As Long 
    FirstRow = Range("Submitter").Row 
    LastRow = FirstRow + Range("Submitter").Rows.Count - 1 
    For r = LastRow To FirstRow Step -1 
        If IsNumeric(Cells(r, 2)) Then 
            If Cells(r, 2) = 0 Then 
                Range(Cells(r, 1), Cells(FirstRow, 2)).Delete Shift:=xlUp 
            End If 
        End If 
    Next r 
End Sub

Re: Deleting cells in a for each range loop

Thanks... Worked like a charm, but it definitely adds a few seconds to my processing time to remove about 5 rows.

"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?

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