VBA- add new rows if a condition is met

[Lanterns20]

Hi,
I am new to VBA and I would appreciate any help on this. My question is how I can write a routine to add rows in one Sheet if a condition which involves a different Sheet is met.

In Sheet 1 is a list of ("liquid") names (cells C4:C26) which will need to be expanded should the IF condition be satisfied. In row 27 I have a subtotal (whihc is using data from columns D onwards), and from row 29 down cells are not empty.

The condition I want to verify is in Sheet 4-column I, where it is reported whether a name is classified as "liquid" or "illiquid". If the number of "liquid" names in sheet4-column I is more than the number of names in Sheet1-C4:C26 I would like to add as many rows as the difference between the two lists. Also I would like to make sure that the subtotals that I have in row 27 (and that will be shifted down when new rows are added) will also inlcude the data in the new rows (the subtotal is taking data from columns D onwards).

Should the code need to inlcude the name of the tabs, Sheet 1 is called Summary and Sheet 4 CDS Data

Thank you

 

[sijpie]

Thanks for the pretty clear explanation of what you want and the workbook layout. That is helpful.
We can quickly get the number of liquids on the CDS-Data sheet using a countif() function.
Then we count the rows between the subtotals and the top to give the number of current liquid rows. Then if required we add more rows and adjust formulas.

• Let's start with the last item: Is it possible to have an empty row just above the subtotals? In that case your total formulas would include the empty row and as long as you insert new rows above the empty row the formulas will always be correct.
• Does column A (or B) contain titles, so we can look for the title of the SubTotals row to determine the size? Or is it possible to give the Ax (first cell of the totals row) a Named Range name?
• If the formulas need to be recreated, what is the formula in the subtotals row at present, and is it the same for each column?