Need a macro Thanks

[PCOR]

I want to start from C5 and find the FIRST BLANK to the right, in row 5
Once I have found that blank(let say I find that blank at G5)
I now want to get the difference between the first col to the left of the
blank-F5 and and the second col to the left- E5.
This data would be displayed at L5
I then want to do the same with Row 6
I would appreciate any help
Ian M

 

[Frank Kabel]

Hi
one way: try the following array formula (entered with
CTRL+SHIFT+ENTER)in L5:
=INDEX(C5:K5,MATCH(TRUE,ISBLANK(C5:K5),0)-1)-INDEX(D5:IV5,MATCH(TRUE,IS
BLANK(C5:K5),0)-2)

Note: this won't work if the first blank is left to E5 or if no blank
cell is found. So no error checking included. If you want to include
this, try (also array entered):
=IF(ISNA(MATCH(TRUE,ISBLANK(C5:K5),0),"no blank cell
found",IF(MATCH(TRUE,ISBLANK(C5:K5),0)<3,"Can't
calculate",INDEX(C5:K5,MATCH(TRUE,ISBLANK(C5:K5),0)-1)-INDEX(D5:IV5,MAT
CH(TRUE,ISBLANK(C5:K5),0)-2)))

 

[PCOR]

Thanks but I tried it and all I get is N/A
Would you have a nice macro to do the same
Thanks

Hi
one way: try the following array formula (entered with
CTRL+SHIFT+ENTER)in L5:
=INDEX(C5:K5,MATCH(TRUE,ISBLANK(C5:K5),0)-1)-INDEX(D5:IV5,MATCH(TRUE,IS
BLANK(C5:K5),0)-2)

Note: this won't work if the first blank is left to E5 or if no blank
cell is found. So no error checking included. If you want to include
this, try (also array entered):
=IF(ISNA(MATCH(TRUE,ISBLANK(C5:K5),0),"no blank cell
found",IF(MATCH(TRUE,ISBLANK(C5:K5),0)<3,"Can't
calculate",INDEX(C5:K5,MATCH(TRUE,ISBLANK(C5:K5),0)-1)-INDEX(D5:IV5,MAT
CH(TRUE,ISBLANK(C5:K5),0)-2)))

 

[hrlngrv - ExcelForums.com]

PCOR wrote..

Thanks but I tried it and all I get is N/A Would you have a nic macro t
do the sam ..

..

Excel macros that would duplicate this sort of formula are much les
nice that the formulas which could do this

Frank's formula and his alternative don't handle the situation i
which there's data all the way out to column K, just before column
where you said you wanted these formulas. Presumably if there wer
data out to column K, you'd want column K's value minus column J'
value. If you don't have holes in your ranges (e.g., C6, E6 and I
contain values but D6, F6, G6, H6, J6 and K6 are blank) and yo
always have data in column C, then you could restate your specs a
the rightmost number minus the next rightmost number. If all of thes
assumptions hold, use the formul

L6
=SUMPRODUCT({-1,1},OFFSET(C6,0,MATCH(1E300,C6:K6)-2,1,2)

Fill up or down as needed

 

[Frank Kabel]

Hi
this should work. Try entering it as array formula with CTRL+SHFT+ENTER

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks but I tried it and all I get is N/A
Would you have a nice macro to do the same
Thanks

 

[Frank Kabel]

Frank's formula and his alternative don't handle the situation in

which there's data all the way out to column K, just before column L
where you said you wanted these formulas. Presumably if there were
data out to column K, you'd want column K's value minus column J's
value. If you don't have holes in your ranges (e.g., C6, E6 and I6
contain values but D6, F6, G6, H6, J6 and K6 are blank) and you
always have data in column C, then you could restate your specs as
the rightmost number minus the next rightmost number. If all of these
assumptions hold, use the formula

L6:
=SUMPRODUCT({-1,1},OFFSET(C6,0,MATCH(1E300,C6:K6)-2,1,2))

Hi Harlan
making these (reasonable) assumptions you could also use:
=SUMPRODUCT({-1,1},OFFSET(C6,0,COUNTA(C6:K6)-2,1,2))

With the benefit of returning zero instead of #NA in case no cell is
filled.

Frank

 

[hrlngrv - ExcelForums.com]

Frank Kabel wrote..
..

hrlngrv wrote..

making these (reasonable) assumptions you could also use
=SUMPRODUCT({-1,1},OFFSET(C6,0,COUNTA(C6:K6)-2,1,2)

With the benefit of returning zero instead of #NA in case no cel

is filled

Begging the question whether #NA would be a useful diagnostic if th
formula were filled too far down

 

[PCOR]

This worked VERY well. Many thanks
Ian M

PCOR wrote...
..

Excel macros that would duplicate this sort of formula are much less
nice that the formulas which could do this.

Frank's formula and his alternative don't handle the situation in
which there's data all the way out to column K, just before column L
where you said you wanted these formulas. Presumably if there were
data out to column K, you'd want column K's value minus column J's
value. If you don't have holes in your ranges (e.g., C6, E6 and I6
contain values but D6, F6, G6, H6, J6 and K6 are blank) and you
always have data in column C, then you could restate your specs as
the rightmost number minus the next rightmost number. If all of these
assumptions hold, use the formula

L6:
=SUMPRODUCT({-1,1},OFFSET(C6,0,MATCH(1E300,C6:K6)-2,1,2))

Fill up or down as needed.