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


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


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 autopopulate 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:
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!

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:
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 MultiColumn 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: 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:
CodeRange(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)
Code
Display MoreSub 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?