Offset formula using active cell

Hi, is anyone able to help me to get this equation to work. The reason for using ActiveCell is because it is part of a macro and the cell will vary.

=(OFFSET((ActiveCell),0,-1))

If anyone is able to help solve the problem that this equation forms part of that would be great too.
It is in the VBA part of the forum called "formula-in-and-offset-cell-based-on-location-of-text" (Not allowed to post links yet)

The VBA code on the post above is:

Please Login or Register  to view this content.

Intent: Go down column A until "Grand Total" found, then place formula in offset cell (0,2). The IF formula wants to reference a cell offset (0,-1) as part of the equation.

Thanks all!!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional.)


And please provide a sample workbook. See the yellow banner at the top of the page.

Thanks, changes made. I want to delete my other post but cannot seem to.

I don't quite understand what you are doing, but here's a partial solution:

Please Login or Register  to view this content.

I don't know what Last refers to, it's not initialized in your code. Same for Cell.
ACTIVECELL is not a worksheet function so you can't build a formula with that.

Then there's: "... reference a cell offset (0,-1) ..." since you are looking in column A, you can't move to the left

Hi, sorry about that, the origional post is on another part of the forum, and was trying to avoid duplicating. Trying to get the other one deleted.

Its a mess as I have tried to manipulate something from another part of the macro. Most of the other macro's I have set up are from using the record function, so you could quite happily disregard it all together.
I have Columns A & B, the length of which will vary, but the last value in Column A will be 'Grand Total', adjacent to which is a sum of the values in Row B. This has come from a subtotal function, but is now just text after a copy and paste.

What I want to do is create a formula in the offset cell 0,2 relative to Grand Total. If Grand Total was in Cell A5, the formula would be in C5 "=B5=Sheet1!N55". The other problem is that "B5" will obviously vary depending on how many rows there are.
The intent is to check that the total at the end of the macro still matches that from the original page.

Does that make sense?

Please Login or Register  to view this content.

And not clear why IF is here

Please Login or Register  to view this content.

the result is TRUE or FALSE also

Ok, so after the above couple of posts this is what I am now trying to run with no result?

Please Login or Register  to view this content.

The purpose of the IF is that TRUE and FALSE will be replaced with text. TRUE will be "Total Matches Original Value" and FALSE will be the opposite.

Happy to get rid of the i=last part etc (I dont understand it anyhow) if there is a better way to do the search? Am I better using something like the search function?

There will only be one "Grand Total"

Sorry there was typo in the "Formula R1C1". it must me "FormulaR1C1"

If you sure that Garnd Total is always present and it always last row then

Please Login or Register  to view this content.

If not then

Please Login or Register  to view this content.

If spreadsheet is not big then

Please Login or Register  to view this content.

The other thread has been closed and this one has been moved to the correct sub-forum. Don’t duplicate queries again.

Please Login or Register  to view this content.

Glad to help and good luck with your project.

Thanks for rep+.