Re: get the value of range
Just watch that Integer dimension. If the number of rows can exceed 32,768, then dimension it as Long. A good practice is to always dimension row related variables as Long.
Re: get the value of range
Just watch that Integer dimension. If the number of rows can exceed 32,768, then dimension it as Long. A good practice is to always dimension row related variables as Long.
Re: Leap Years + Copying to Other formats
Welcome to OzGrid.
I don't understand question 1. What do you want to accomplish exactly? Are you incrementing dates forward? Calculating elapsed days? How do leap days screw you up? Can you attach a small workbook showing what your inputs are and what your expected outputs are?
Re: Array Formula
Try this in F1 and copy down (not an array formula)
=SUMPRODUCT(--($C$1:$C$18=C1),--($D$1:$D$18="Pass"))
Re: IF function with dates
Do you have a "passed the test" indicator? If so, you could add an AND condition to the conditional formating.
Re: Sorting a column that contains formulas
Here's one less than pretty solution. Change the formula in column B to return "zz" rather than blank. Then use conditional formatting to change the font to white if the cell contains zz.
Re: Know If 3 Or More Columns Are Equal
How about:
=IF(AND(A1=B1,B1=C1),"YES","NO")
Change cell addresses to suit.
Re: % Formula request
To get the answer you got, you can use 3.42-.25*2.06. If 2.06 is in A1 and 3.42 is in A2 and 25% is in A3 then you get = A2-A1*A3
You get here with a little algebra from here:
2.06+((3.42-2.06)/2.06-2.06)
=A1+((A2-A1)/A1-A3)
Re: Vlookup Formula
Rob,
Here is a sample spreadsheet that uses a double index/match to get what you asked for. Hope it translates to the real problem.
Seti
[edit]Welcome to OzGrid[/edit]
Re: Find last row or empty cell
Here is code to do the first portion. You should be able to adapt it for the second.
Re: Covert Numeric Value to Alpha Code
I get KOKE for 5750, but be that as it may, here is a UDF that I think will do what you asked for. It goes in a standard module and you would use it like =ENCODE(A1) in A2.
Re: basic math
One way to get a non-unique solution would be to use Solver. I have attached sample for you. It assumes that the solution could be 1 to 20 numbers that sum to 300.
Re: Selecting a range
How about
activecell.currentregion.select
although you don't usually need to select to operate on the range.
Re: cell formula
Check for cells formatted as text.
Re: Creating Flat TextFile from an Spreadsheet
I tried this and it put the file in my Default File Location as specified in Tools|Options|General. (My Documents)
If you want it in a specific place, precede the file name with a drive and path.
Re: Formulas are not a value
Are you saying that you want to find the first cell in the range that is not a formula and not a blank? Must it be a number or can it be text?
Re: Formulas are not a value
I would change the variable named Value to something else since it is a keyword. May not solve the issue, but it will make for slightly better code.
Re: VLOOKUP on two variables
Are you looking for an exact match? If so, maybe try sumproduct. But maybe we can help with the "&" approach. Can you attach a sample workbook that is not working? Maybe with just dummy values, since this approach works in many cases.