Macro to Auto-populate column in a range specified by two cells in another sheet

[surgiles]

Hello all,

Unfortunately the only experience I have with excel macros are using the record macro tool that comes with Excel (I am using 2007) so I hope that you would be so kind as to help me.

I need to populate a range of cells in column O with a formula. The cells that I need to populate are specified in another sheet and appear in the cells as follows:
"Restorations!B$2" and then "Restorations!B$100"
the 100 is a value that will be varying and that is why I need the macros to specifically recognize this cell. The 2 will always be the same, however.

I also have another cell that simply displays the numerical 2 and 100 if this helps.

Thank you in advance for any and all of your efforts!

 

[Joe4]

What does the formula that you want to place in these cells look like?

 

[surgiles]

There are several so I will list them,

=IF(ISERROR(MONTH($E2)),AD2,TEXT($E2,"dd/mm/yyyy"))
=IF(AL2="00/01/1900","",IF(ISERROR(MONTH($F2)),AK2,TEXT(F2,"dd/mm/yyyy")))
=IF(P2="",DATE(YEAR(Summary!B$2),MONTH(Summary!B$2)+1,0)-O2,P2-O2)
=MID(E2,SEARCH("2011",E2),4)
=MID(E2,SEARCH("2011",E2)-3,2)
=LEFT(E2,SEARCH("/",E2)-1)
/ (yes literally just a /)
=AA2&AC2&AB2&AC2&Z2
=O2*1
=MID(F2,SEARCH("2011",F2),4)
=MID(F2,SEARCH("2011",F2)-3,2)
=LEFT(F2,SEARCH("/",F2)-1)
=IF(ISERROR(MONTH($F2)),AH2&AJ2&AI2&AJ2&AG2,TEXT($F2,"dd/mm/yyyy"))
=IF(AL2="00/01/1900",0,MONTH(AL2))

For the sake of being thorough I listed them all. I am sure most of them will have no issues except for the " needing to be doubled up correct?

thanks!

 

[Joe4]

I need to populate a range of cells in column O with a formula. The cells that I need to populate are specified in another sheet and appear in the cells as follows:
"Restorations!B$2" and then "Restorations!B$100"
the 100 is a value that will be varying and that is why I need the macros to specifically recognize this cell. The 2 will always be the same, however.

So, does "Restorations!B2" contain a cell reference (of which row you want to start on) or the formula you want to copy?

Does "Restorations!B$100" contain the cell reference or a formula?
Are yoy saying this could change (could be "Restorations!B$84")?
If it changes, how do you find it? Is it always the last cell in column B?

I also have another cell that simply displays the numerical 2 and 100 if this helps.

Where are these cells located?
Are they always located in the same place, or might they change too?

 

[surgiles]

Column B is going to be a list of "low" "medium" "high"
2 is the first row that will have these keywords in them (row 1 is the title row)
Restorations!B2 is what appears in cell G42 of my Summary sheet (This cell achieves "Restorations!B2 by the formula =D$51&E42&E$50&B$52)

cell J42 contains
Restorations!B$100 (the 100 changes based on a formula in another cell which is =COUNTIF(Restorations!$L$2:$L$64000,"=NH IT Team")+1)

Yes, Restorations!B$100 is always the last cell in column B.




The numericals 2 and 100 (which is based on the countif formula above) are always in the same place and are located in cells B52 and B51


I sincerely apologize for the wordiness and confusion and found it very difficult to explain this well. I was confused myself in trying to explain this properly.

 

[Joe4]

Yep, you have me pretty much confused! I really don't follow at all.
A visual representation might go a long way here. You can post screen images using the tools mentioned here: http://www.mrexcel.com/forum/showthread.php?t=508133

From what little I have gleaned, let's see if the following is any help at all.
Here is a simple macro that based on the numbers listed on cells B51 and B52 on the "Restoration" tab, will enter a formula in column O that adds columns A and B together for that particular row.

    Dim myStartRow As Long
    Dim myEndRow As Long
    
    myStartRow = Sheets("Restoration").Range("B51")
    myEndRow = Sheets("Restoration").Range("B52")
    
    Range("O" & myStartRow & ":O" & myEndRow).FormulaR1C1 = "=RC[-14]+RC[-13]"

Is that something along the lines of what you are looking for, how to auto-populate an unknown number of rows with a specific formula?

 

[surgiles]

I would like to try and take a hack at the basic formula you provided first. Much more likely to learn this way. So here goes:

At the moment I have

Dim myStartRow As Long
Dim myEndRow As Long
myStartRow = Sheets("Summary").Range("B51")
myEndRow = Sheets("Summary").Range("B52")
Sheets("Restorations").Select
ActiveSheet.Range("O" & myStartRow & ":O" & myEndRow).ForumlaR1C1 = "=IF(ISERROR(MONTH(RC5)),RC[15],TEXT(RC5,""dd/mm/yyyy""))"

The last line (the one that starts with ActiveSheet.Range) is giving me an error
"Run-time error '438':
Object doesn't support this property or method

I am sure it is a syntax error, would you provide some insight please?

 

[Joe4]

You have a typo.
It should be FormulaR1C1 not ForumlaR1C1.

The ActiveSheet before the Range in your last line is unnecessary do (if you leave the sheet reference off, the default is the ActiveSheet).

 

[surgiles]

What a shocker, the problem was between the keyboard and the chair That is the millionth time today that I have typed that word wrong.

The macro works perfectly!
As for the rest, will I be able to just copy and paste this over and over again for the rest of the columns that I need to populate with the other formulas *took a second look at how I spelled that* or do I need to have some additional code in between?

Whether you respond to that question or not your help has been much appreciated! Many thanks for your help (and immense patience)

 

[Joe4]

The logic should be the same, but obviously you will need different lines of code for the different formulas.