Solved: Finding a variable cell on another sheet for a formula

[pcarmour]

Hi,
I have a worksheet, called 'Summary', that keeps a daily record of our commission.
The month's running total is also recorded on a separate 'Year_Summary' sheet.

I am writing a VBA program that starts a new month. In the code I have a formula that is a link from the 'Year_Summary' sheet to the running total on the 'Summary' sheet.

This formula works OK:
ActiveCell.FormulaR1C1 = "=Summary!R[63]C"

BUT the row changes as each day is added.

I need to use a formula that uses - Range("A65536").End(xlUp).Offset(10, 1).Select - to find the correct running total cell instead of R[63]C but can't get it right.

Any help would be very much appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

[mancubus]

hi.

try:

FormulaRow = Range("A" & Rows.Count).End(xlUp).Offset(10, 1).Row
ActiveCell.FormulaR1C1 = "=Summary!R[" & FormulaRow & "]C"


or to get just the value of cell (if the formula result is not changing because of further calculations)
ActiveCell.Value = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(10, 1).Value

[pcarmour]

Hi mancubus,
Thank you for your prompt response.
I do need the formula and not just the value but thanks for the additional information.
I have tried your formula but I'm sorry to say it's finding the wrong row.
My data is currently in rows 2-21 for June then 39-49 for July. A22 to A38 are empty. Row 1 is for headings. With the new month I place a date in Column A, 7 rows below the last date in column A and then need to find a cell 10 rows down in column B from here.
Your formula is finding cell B34 which I can't quite see why.
Changing - "A" & Rows.Count - to "A65536" gave the same result.
Perhaps - FormulaRow needs to be directed to the Summary Sheet?
I can at least see it can be done.
Thank you again.

Please let me know if you have any further thoughts.

[mancubus]

you are welcome.

first Column Letter & Rows.Count returns the last cell in worksheet regardless of the excel version. for excel 2003, yes, they are identical.

i used this bit of info you provided in post # 1:
I need to use a formula that uses - Range("A65536").End(xlUp).Offset(10, 1)

if you can post your workbook, we can work on it.

[pcarmour]

Hi mancubus,

Thanks again for your time with this.
Please find a file attached although with this file the layout is a little different, as with the new month I place a date in Column A, 17 rows below the last date in column A and then need to find a cell 6 rows down in column B from here.

There is a macro button 'New Month' at the top far right. after running this I want the new month August cell B9 in Year_Summary to link to cell B72 in Summary.

I trust that all makes sense.

[mancubus]

hi peter,

i think this is a table imported from another application, possibly in txt or csv format.

the first thing you should do is to redesign the worksheet with some standards. (for ex, if it's imported on a mothly basis, i would create worksheets for each month.)
here, some tips:
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm



for the current scenario:

create formulas for columns B:AM in Year_Summary that will return the same column values in Summary.

so you only need to learn the row number in Summary which will correnpond to month value in column A in Year_Summary.

A)
if you can write column totalling formulas at certain increments in rows (for ex: june row 25, july row 50, august row 75, september row 100) for each month we may use loops.



B)
if not, if you can make these two values identical we can use FIND method to return the row number.

Summary!B26 value = 06.01.2013
Year_Summary!A7 = JUNE

Summary!B53 value = July2013
Year_Summary!A8 = JULY


Dim fRow As Long
fRow = Worksheets("Summary").Range("B2:B50000").Find(Worksheets("Year_Summary").Range("A7"), LookIn:=xlValues).Row
Range("B" & ActiveCell.Row & ":AM" & ActiveCell.Row).Formula = "=Summary!B" & fRow


C)
or user inputs row number.

Dim fRow As Long
fRow = Application.InputBox("Please input the row number of totals in Summary", "ROW NUMBER", , , , , , 1)
Range("B" & ActiveCell.Row & ":AM" & ActiveCell.Row).Formula = "=Summary!B" & fRow

[pcarmour]

Hi mancubus,
Yes the data is imported from our systems database in CSV format.
I'm afraid that my program is too far advanced to now re design it and apart from getting the running monthly totals showing in the year summary everything else is working nicely.
I would prefer to continue with a variation of your original formula which is very close to achieving the answer.
Thank you very much for all your help.
Regards,
Peter.

[mancubus]

i mean manually correcting related cell values which i believe added after importing into excel.

for example
Summary!B26 value = JUNE
Year_Summary!A7 = JUNE

then use the find method.

if you are referring to R1C1 notation formula in post 2, pls keep in mind that ['s in the notation makes cell references relative.

so if activecell is B7 Summary!R[3]C means 3 rows down by activecell row.

if it is B25 it's better using Summary!R25C2
and code will be

ActiveCell.FormulaR1C1 = "=Summary!R" & FormulaRow & "C2"

[mikerickson]

If your cell of interest contains text
=INDEX(A:A, MATCH("zzzzz",A:A),1) is the excel formula equivalent of Range("A65536").End(xlup)

so
=INDEX(B:B, MATCH("zzzz",A:A)+10, 1) is the excel formula equivalent of
Range("A65536").End(xlUp).Offset(10, 1).


If it contains a number, use 99E+99

[pcarmour]

Hi mikerickson,
Thank you for your suggestions but I am making good progress with the earlier thread.

Hi mancubus.

Thank you, your explanation and the variation on the code WORKS!
I am now using:
ActiveCell.FormulaR1C1 = "=Summary!R" & FormulaRow & "C2"

and I get the correct link 'BRILLIANT'. My problem now is that the formula is =Summary!$B$73 and I need =Summary!$B73 to enable the formula to be pasted across to the right. How can I change this?

Fantastic coding - Thank you again.

[mancubus]

hi.
just remove the column number 2 from C2

Range("B" & ActiveCell.Row & ":AM" & ActiveCell.Row).FormulaR1C1 = "=Summary!R" & FormulaRow & "C"

from B to AM formulas will look:
=Summary!B$25
=Summary!C$25
...
...
=Summary!AM$25

[pcarmour]

Hi mancubus,

That's it! Thank you again, the link and my program are working - FANTASTIC.
VBA Express Forum really is the best place to get answers!
:beerchug:

[mancubus]

you're welcome.

i'm glad it helped.

:beerchug: