# Posts by tinyjack

Re: Getting Size of a 2-D Array

You could use Rows.Count and Columns.Count or you could use this:

[vba]
Dim varArray As Variant

varArray = Range("a1:b2").Value
[/vba]

HTH

TJ

Re: Conditional Formula

In AN26

=IF(AN13<5,AN13+1,AN13-1)

TJ

Re: Matching Formula

Totally confused now, as that is what the formula would do.

What is wrong with the formula given?

TJ

Re: Conditional Formula

You have a typo:

If AN13 = 8 , then AN26 = 7
If AN13 = 8 , then AN26 = 8

But maybe have a look at CHOOSE

TJ

Re: Matching Formula

In M10 and copy down you could use:

=IF(\$E\$12=K10,K10,"")

TJ

Re: update formula to last row

I would always pick the one column you are most sure of having complete information, so that there is the greatess chance of all your dynamic ranges being the same.

TJ

Re: update formula to last row

Your dynamic ranges are not the same size.

I would use COUNTA instead of COUNT and then make the COUNTA work on one column only (A), you also need to use absolute referencing throught:

=OFFSET(Sheet1!\$A\$24,0,0,COUNTA(Sheet1!\$A\$24:\$A\$65536),1)
=OFFSET(Sheet1!\$J\$24,0,0,COUNTA(Sheet1!\$A\$24:\$A\$65536),1)
=OFFSET(Sheet1!\$L\$24,0,0,COUNTA(Sheet1!\$A\$24:\$A\$65536),1)

HTH

TJ

ps remember if anything is typed under your table in column A it is going to throw your dynamic range

Re: update formula to last row

You need to put 65536 after the last L to tell it to count to the end.

TJ

Re: Which code procedure came first.

I would guess they both happened at the same time, but why the question?

TJ

Re: formular1c1 with decimals

VBA works with American number and date formats. I am guessing that yo is not declared and as such the value will be stored as text, try declaring yo As Double and the problem should go away.

TJ

Have read the question properly I can see you have tried Double. The other thing to do is use the Value2 property rather than Value as it holds the base data without any regional info.

Re: Non-Unit Increments in FOR Loops

It would have been nice to see the solution, rather than delete the question, so that others who will possibly face the same problem could benefit.

TJ

Re: Greater than, less than

You could use Month() and Year(),

=IF(AND(MONTH(J2)=1,YEAR(J2)=2005),"Hit","Miss")

TJ

ps You could also have a look at DATEVALUE() and DATE()

Just go in and remove the **** from the password boxes.

TJ

Re: Intersection Cell

I understand what you mean by intersect, but you still have not told us have the row and column are defined (your attachment is not your actual data, I assume)

Do you mean something like:

TJ

## Files

Re: VBA Function using an Exponent

As Integer means the function is going to return an Integer, try changing it to As Double.

TJ

Re: sheet unprotect help

TJ

Re: Hide Formulae in cells

You can also do:

Format - Cells - Protection - Hidden = checked

but this only has an effect if the worksheet is protected.

TJ

Re: protecting + vba/macro

Have a look at the UserInterfaceOnly option of protect.

TJ

Re: Intersection Cell

How are you defining the row and column?

TJ