Inserting forumla using vba
 

Can anyone help with the following problem?
I have a worksheet 'StockList' which I am using to perform a calculation on
how many times a specific text value appears on another worksheet column.
My 'Stocklist' worksheet has the following layout
Item CurrentStock Issued MaxStock ReOrderLevel StockInput
StockInput -repeated

Item is a text description of my stock item
CurrentStock is a simple SUM of the current row range from all the
'StockInput' columns
Issued is a formula to count the number of times Item description from
another worksheet within the range C2:C500 is made
=COUNTIF(Issue!C2:C500,A2)
=COUNTIF(Issue!C2:C500,A3)
etc etc going down the cells.

As I may need to add more stock 'Item'(s) there is a need to take the whole
range A1:IV500 and sort it by column A (Item) but on doing so, any new
'item' rows have the COUNTIF formula altered with the table cell row number.
For example a new item gets sorted and appears in the list in row 10 the
formula gets changed to

=COUNTIF(Issue!C10:C510,A10) and I need the references to the C column to
remain constant.
Is there any way to re-populate the column C from C2:C500 with the correct
formula?

Inserting forumla using vba
 

Look in Excel help at absolute and relative addressing

=COUNTIF(Issue!$C$2:$C$500,A2)
=COUNTIF(Issue!$C$2:$C$500,A3)

--
Regards,
Tom Ogilvy

"Guardian" wrote in message
. uk...
Can anyone help with the following problem?
I have a worksheet 'StockList' which I am using to perform a calculation
on
how many times a specific text value appears on another worksheet column.
My 'Stocklist' worksheet has the following layout
Item CurrentStock Issued MaxStock ReOrderLevel StockInput
StockInput -repeated

Item is a text description of my stock item
CurrentStock is a simple SUM of the current row range from all the
'StockInput' columns
Issued is a formula to count the number of times Item description from
another worksheet within the range C2:C500 is made
=COUNTIF(Issue!C2:C500,A2)
=COUNTIF(Issue!C2:C500,A3)
etc etc going down the cells.

As I may need to add more stock 'Item'(s) there is a need to take the
whole
range A1:IV500 and sort it by column A (Item) but on doing so, any new
'item' rows have the COUNTIF formula altered with the table cell row
number.
For example a new item gets sorted and appears in the list in row 10 the
formula gets changed to

=COUNTIF(Issue!C10:C510,A10) and I need the references to the C column to
remain constant.
Is there any way to re-populate the column C from C2:C500 with the correct
formula?