VBA Code - Obtain variable row numbers; use in formula

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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Thanks, footoo for posting the link. I'll review and see if I can figure out how to do what I need from the information in the link. If not, I'll try to determine how to put an example of what I need on this thread.

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top