macros and hlookup

[katie]

I have a macro that enters a new row and then carries out
a series of instructions based upon this inserted row, for
example entering 'filled down' calculations in the new row
and other similar processes.
I would also like the macro to amend a HLOOKUP function
(as when the row is inserted the array size increases by
one, as does the required row).
For example, before the macro is run the function looks
like this:

=HLOOKUP('Project Summary'!B1,'Associates Hours (actuals)'!
S5:AD76,72,TRUE)*30

but then after i need the function to look like this:
=HLOOKUP('Project Summary'!B1,'Associates Hours (actuals)'!
S5:AD76,72,TRUE)*30

However i am not sure how to get round this problem. Any
ideas?

 

[Frank Kabel]

Hi
both functions look the same?

 

[Guest]

sorry!
but then after i need the function to look like this:
=HLOOKUP('Project Summary'!B1,'Associates Hours
(actuals)'!
S5:AD77,73,TRUE)*30

 

[Frank Kabel]

Hi
if you have no blank lines in between try
=HLOOKUP('Project Summary'!B1,OFFSET('Associates Hours
(actuals)'!$S$5:$AD$5,0,0,COUNTA('Associates Hours
(actuals)'!$AD$5:$AD$1000)),73,TRUE)*30

 

[kate]

Thats returning a #VALUE when i try the suggested code.

 

[Frank Kabel]

Hi
do you have blank lines/cells in the range AD5:AD75?

 

[Guest]

Actuall yes, do these need to go? I only have them as
presently i had no other way of adding rows and still
keepin the hlookup formula working, shall i try your
formula wihtout these empties?

 

[Frank Kabel]

Hi
yes, try it without empty cells in between