Posts by hawkinsXL

Re: Prevent display of event error message

Too bad. Thanks for the reply.

Re: Prevent display of event error message

I have not written any code for this worksheet. Do I need to?

Is there a way to not get the Name conflict dialog box to appear when copying a worksheet within a workbook. I currently have a worksheet called 'MASTER', and I copy this out for each day of the month. The MASTER sheet has 21 different named formulas. When I copy the MASTER sheet, the message appears, "A formula or sheet you want to move or copy contains the name 'whatever', which already exists on the destination worksheet. Do you want to use this version of the name?

I have to click 'Yes" 21 different times before the sheet will copy. When I copy the worksheet, I will always select the "Yes" option. I was wondering if there is a setting in XL to automatically do this without having the conflict box appear for each defined name.

I am using version 2003.

Thanks in advance.

Re: Sum with multiple Criteria

I'd love to upgrade, unfortunately our company uses 2003, so I'm stuck with it.

Your solution worked perfectly. 2 IF statements...go figure. Very, very thankful for your help!

Re: Array formula to SUM between numbers

Thanks for the response Ger,

I am using XL 2003. SUMIFS is an undefined function. Is this an XL 2007 function? Tried using SUMIF, but did not work.

I have written a formula [=SUM(IF...] to give me the sum of the numbers in B column based on criteria in A column, dependent on the number values in B column. The array formula works if I use only a single argument for the value in B column (such as <=12, or >18). However, when I try to use this same formula to get a value between two numbers using the AND function (i.e. AND((B15:B31>12,B15:B31<=18)), it does not return a valid result.

I have attached an example for review, which should explain it better.

If there is a better formula to use to do this, please let me know. Keep in mind, the actual report has non-contiguious ranges, so I am unable to do pivot tables, data tables, etc...

Thanks in advance!

Re: Count By Condition Between Occurrences

Love the code, and the formula. I tried every way to make this happen...couldn't figure it out. You guys rock!

Thanks.

Wanting to count the number of days that transpire in column A based on data entered in Column B for a specific day. Hope that the example clarifies what I'm asking for. Thanks.

Re: vlookup with cell instead of a number it's text

It shouldn't matter whether you are looking up text or a number. How are you entering your formula?

Re: formatting formula and text in same cell

Ahhhh, you are clever. Didn't think of doing it that way. Now get some sleep!!

Thank you much!

Re: formatting formula and text in same cell

Good night and have a good rest.....thanks for your assistance....
Example is attached.....

Re: Nested If statement

Made a error in previous post. This should work.

Re: formatting formula and text in same cell

That does not work for me. No matter what format I try to use, the result of the formula remains the same [in this case] ...result is 0.5 (providing a2=50 & a1=100).

Re: Nested If statement

Let me know if this works for you..............

Is it possible to apply formatting to a formula in a cell when you are combining that formula with text? As an example, I want to format the following as a percentage: ="The result is"&" "&(a2/a1)
Currently, it is returning [e.g.] ...result is 0.5, instead of ...result is 50%

Re: Copying Text from Within a Cell

A simple copy and paste should work. What results are you getting when you copy and paste?

Re: copying non-adjacent selections to end of range

Had I been paying attention, I would have picked up on the error, as well.
Updated code works like a charm! Many, many thanks for your assistance and knowledge!

Re: copying non-adjacent selections to end of range

Bryce,
This example yielded the same results as my original code (not the one I posted) in that it is also copying the data in cells E3,G3,H3, and I3. Apply your code to my worksheet that I am attaching and you will see what I mean.

Thanks.

Re: copying non-adjacent selections to end of range

Bryce,
Thanks for your response. I've just now had time to take a look at this. Unfortunately, the subroutine Formulas is not what I need. When copying the formulas from cells D3, F3, and J3 to the end of the range, I do not want to affect the data in columns E, G, H or I. I sort of understand your explanations on using LastRow, but after trying several variations, still could not make it work. Should I set each area (D3,F3,J3) to copy to end of range individually instead of trying to combine them into 1 statement? Forgive my lack of knowledge in this area.......... Thanks.

I am using code that will copy several formulas in adjacent cells to the end of range; however, when I try to use it to select and copy formulas in non-adjacent cells, I get an error. Tried to modify, but I'm new to VBA...still learning. Here is the code that I currently have.
Thanks for the help.

Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("D3").Formula = "=RC[4]/RC[3]"
Range("F3").Formula = "=RC[-1]*RC[1]"
Range("J3").Formula = "=RC[-1]/RC[-3]"
Range("D3,F3,J3").Copy Range("D3,J" & LastRow)