EXCELISFUN1
New Member
- Joined
- Mar 11, 2019
- Messages
- 6
I have a variable row, multiple column worksheet divided into sections with totals.
I have written a macro that adds a blank row under a section total row that I'm finding by using a find on specific text in Column A.
I need to have a formula added to this blank row in column B that I will then copy to Column C, D, etc. which will calculate from the data in those columns.
The formula takes the total in the section and subtracts several rows. The problem is that the rows to use for the subtraction are variable, they may be there or they may not so I am unable to make the formula absolute (ie. Row 10 – sum(row 2 – row 3 – row 4) as the formula has to subtract only specific account data (if it's there).
I think I need VBA code to find a string of text in column A and capture that row number for multiple different strings of text. If the text string is found and the row number captured, it will use those row variables in the formula.
For example: final row number is 10. My added row will be 11 and the formula will be in column B.
If the code finds text 101.00* in column A, capture that row #, if finds text 102.00* in column A, capture that row 3, etc.
The formula that needs to be entered in Column B, row 11 would be the row above the added blank row (row 10) – row number found for 101.00* - row number found for 102.00*, etc.)
I would then copy that formula to the other columns in row 11.
Thanks for any help. I would show an example but I'm not able to download software or add-ins to my work computer.
I have written a macro that adds a blank row under a section total row that I'm finding by using a find on specific text in Column A.
I need to have a formula added to this blank row in column B that I will then copy to Column C, D, etc. which will calculate from the data in those columns.
The formula takes the total in the section and subtracts several rows. The problem is that the rows to use for the subtraction are variable, they may be there or they may not so I am unable to make the formula absolute (ie. Row 10 – sum(row 2 – row 3 – row 4) as the formula has to subtract only specific account data (if it's there).
I think I need VBA code to find a string of text in column A and capture that row number for multiple different strings of text. If the text string is found and the row number captured, it will use those row variables in the formula.
For example: final row number is 10. My added row will be 11 and the formula will be in column B.
If the code finds text 101.00* in column A, capture that row #, if finds text 102.00* in column A, capture that row 3, etc.
The formula that needs to be entered in Column B, row 11 would be the row above the added blank row (row 10) – row number found for 101.00* - row number found for 102.00*, etc.)
I would then copy that formula to the other columns in row 11.
Thanks for any help. I would show an example but I'm not able to download software or add-ins to my work computer.