Advice: Macro Cell reference Range vs Active Cell

[mike575]

When recording a macro some of my instruction have RANGE to reference an cell locate that I want to modify with a macro. GOOD Example below: This is how I want my macro to identify the specific cell.


Range("B1").Select

ActiveCell.FormulaR1C1 = "Team Name"

My problem is some how ?Active Cell? is being used and that?s not what I want. How can I disable the Active Cell reference and just stick to RANGE.

Bad Example:


ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Teamlist,2)"

Range("C106").Select



I have looked at TOOLS, OPTIONS, General Tab, for the R1C1 reference settings and it is not checked.



Any help? :help



Mike in Wisconsin

[Zack Barresse]

Hi Mike,

You really don't need (almost never) to activate or select anything in code. Your first line can be shortened to:


Range("B1").Formula = "Team Name"

Whether or not you choose to use the R1C1 reference style is your thing. I don't like it. It doesn't matter if you use it in code or Excel, as they're not dependent on one another.

As for your bad example, I'm a bit confused. Are you saying it doesn't work? Anyway, if the cells are not going to move I suggest you either *hardcode* them (eg "A1", not RC) or claim them as variables and ensure they are dynamic in nature. Am I totally off?

[mike575]

When I create a macro i use the macro record option under TOOLS, Macro I don't create free hand. In the bad example if i don't havee the cursor in the proper square (the exact one when) when i started the recording. the macro used the current active cell to start it's initial reference point. that what screws me up. the macreo is completed but in the wrong location.

[Zack Barresse]

Hence you've found one of the largest limitations of the macro recorder. Everything in there is by activating *hardcoded* cells/references. I bet you, that if you posted an example spreadsheet, along with what you wanted done, you'd have some very efficient code to use/study. :yes

[mvidas]

It may be a case of Relative Reference being turned on.

When you start recording the macro, the Macro Recorder toolbar pops up. There's a stop button (black square), and next to it is an icon for Relative Reference. If that's selected, it will be a whole bunch of ActiveCell lines and Offset methods. That will throw the whole thing off if you start in a cell you don't want to start at. You could also try starting at cell A1 when you record, and just adding Range("A1").Select as the top line.

If Relative Reference is unselected, you will see a lot of:

Range("A1").Select
Selection.FormulaR1C1="text"

which you can manually change to
Range("A1").FormulaR1C1="text"

That sounds more like what you're looking for

Matt

[mike575]

I try and post the two macro I am working on. The first one format's the worksheet (TeamFormat) and the second one is the entry of formula to get data from an adjoining worksheet. I didn't use cut aand past because i am only getting data from every 5th line.


Sub TeamFormat()
' TeamFormat Macro
' Macro recorded 7/20/2004 by mike
Range("B1").Select
ActiveCell.FormulaR1C1 = "Team Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Event 1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Event 2"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Event 3"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Event 4"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Total"
Columns("B:B").Select
Selection.ColumnWidth = 20
Columns("C:G").Select
Selection.ColumnWidth = 8.5
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A3").Select
Selection.Copy
Range("A4:A102").Select
ActiveSheet.Paste
Range("A18").Select
ActiveWindow.LargeScroll Down:=1
Range("A51").Select
ActiveWindow.LargeScroll Down:=1
Range("A84").Select
ActiveWindow.LargeScroll Down:=1
Range("A117").Select
ActiveWindow.LargeScroll Down:=1
Range("A150").Select
ActiveWindow.LargeScroll Down:=1
Range("A183").Select
ActiveWindow.LargeScroll Down:=-1
Range("A150").Select
ActiveWindow.LargeScroll Down:=-1
Range("A106").Select
ActiveSheet.Paste
Range("A107").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A107").Select
Selection.Copy
Range("A108:A204").Select
ActiveSheet.Paste
Range("B109").Select
ActiveWindow.SmallScroll Down:=-9
Range("A101:A102").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=87
Range("B105").Select
ActiveCell.FormulaR1C1 = "Team Name"
Range("C105").Select
ActiveCell.FormulaR1C1 = "Event 1"
Range("D105").Select
ActiveCell.FormulaR1C1 = "Event 2"
Range("E105").Select
ActiveCell.FormulaR1C1 = "Event 3"
Range("F105").Select
ActiveCell.FormulaR1C1 = "Event 4"
Range("G105").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B105:G105").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
Range("I108").Select
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Range("I106").Select
End Sub

Sub TeamGetData()
' TeamGetData Macro
' Macro recorded 7/20/2004 by mike
Range("B106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Teamlist,2)"
Range("C106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,7)"
Range("C106").Select
Selection.Copy
Range("D106:G106").Select
ActiveSheet.Paste
Range("D106").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,8)"
Range("E106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,9)"
Range("F106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,10)"
Range("G106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,11)"
Range("B106:G106").Select
Selection.Copy
Range("B107:B204").Select
ActiveSheet.Paste
Range("B106:G204").Select
Range("G204").Activate
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I12").Select
End Sub


These two macro were recorded 10min apart. I tested the TeamFormat a couple of times. then created the TeamGetData and some how between the two the cell reference style was changed.

[Zack Barresse]

Okay Mike maybe this would do ya, btw, check out the vba tags when posting, it'll make your code look like below...


Sub TeamFormat()
' TeamFormat Macro
' Macro recorded 7/20/2004 by mike
Range("B1").Value = "Team Name"
Range("C1").Value = "Event 1"
Range("D1").Value = "Event 2"
Range("E1").Value = "Event 3"
Range("F1").Value = "Event 4"
Range("G1").Value = "Total"
Columns("B:B").ColumnWidth = 20
Columns("C:G").ColumnWidth = 8.5
With [1:1]
.Font.Bold = True
End With
With [1:1]
.HorizontalAlignment = xlCenter
End With
Range("A2").Value = "1"
Range("A3").Formula = "=B2"
Range("A3").Copy Range("A4:A102")
Range("A106").Paste
Range("A107").Formula = "=B106"
Range("A107").Copy Range("A108:A204")
Range("A101:A102").ClearContents
With [B2]
ActiveWindow.FreezePanes = True
End With
Range("B105").Value = "Team Name"
Range("C105").Value = "Event 1"
Range("D105").Value = "Event 2"
Range("E105").Value = "Event 3"
Range("F105").Value = "Event 4"
Range("G105").Value = "Total"
With [B105:G105]
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintGridlines = True
.Zoom = 100
End With
Application.CutCopyMode = False
Range("I106").Select
End Sub

Sub TeamGetData()
' TeamGetData Macro
' Macro recorded 7/20/2004 by mike
Range("B106").Formula = "=VLOOKUP(A106,Teamlist,2)" 'exact match or no?
Range("C106").Formula = "=VLOOKUP(RC1,Teamscore,7)" 'exact match or no?
Range("C106").Copy Range("D106:G106")
Range("D106").Formula = "=VLOOKUP(RC1,Teamscore,8)" 'exact match or no?
Range("E106").Formula = "=VLOOKUP(RC1,Teamscore,9)" 'exact match or no?
Range("F106").Formula = "=VLOOKUP(RC1,Teamscore,10)" 'exact match or no?
Range("G106").Formula = "=VLOOKUP(RC1,Teamscore,11)" 'exact match or no?
Range("B106:G106").Copy Range("B107:B204")
Range("B106:G204").Copy
Range("B2").Select.PasteSpecial (xlPasteValues)
Range("I12").Select
Application.CutCopyMode = False
End Sub

Haven't tested this yet though, let us know how it works. :)

[mike575]

Is this how I should have posted my previous samples?



Sub TeamFormat()
' TeamFormat Macro
' Macro recorded 7/20/2004 by mike
Range("B1").Select
ActiveCell.FormulaR1C1 = "Team Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Event 1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Event 2"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Event 3"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Event 4"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Total"
Columns("B:B").Select
Selection.ColumnWidth = 20
Columns("C:G").Select
Selection.ColumnWidth = 8.5
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A3").Select
Selection.Copy
Range("A4:A102").Select
ActiveSheet.Paste
Range("A18").Select
ActiveWindow.LargeScroll Down:=1
Range("A51").Select
ActiveWindow.LargeScroll Down:=1
Range("A84").Select
ActiveWindow.LargeScroll Down:=1
Range("A117").Select
ActiveWindow.LargeScroll Down:=1
Range("A150").Select
ActiveWindow.LargeScroll Down:=1
Range("A183").Select
ActiveWindow.LargeScroll Down:=-1
Range("A150").Select
ActiveWindow.LargeScroll Down:=-1
Range("A106").Select
ActiveSheet.Paste
Range("A107").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A107").Select
Selection.Copy
Range("A108:A204").Select
ActiveSheet.Paste
Range("B109").Select
ActiveWindow.SmallScroll Down:=-9
Range("A101:A102").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=87
Range("B105").Select
ActiveCell.FormulaR1C1 = "Team Name"
Range("C105").Select
ActiveCell.FormulaR1C1 = "Event 1"
Range("D105").Select
ActiveCell.FormulaR1C1 = "Event 2"
Range("E105").Select
ActiveCell.FormulaR1C1 = "Event 3"
Range("F105").Select
ActiveCell.FormulaR1C1 = "Event 4"
Range("G105").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B105:G105").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
Range("I108").Select
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Range("I106").Select
End Sub

[Zack Barresse]

Yeah, that's ok. :) It's just easier to read/copy/paste that way. So how do those work for ya?

[mike575]

I got it to work but don't know how. Have 3 other worksheet to complete the same type maccro but from different sources. But on thing i try to do is always make my manual edit entry a of "Range ("A1")ClearContents. That way all my recorded events will have to start at A1 until i figure the relative reference problen i have.
Thanks for your code haven't had time to study it yet.

mike in Wisconsin

[Zack Barresse]

No problem Mike. Glad the code worked. :yes

If you're wanting to do something like this for every sheet, upload a sample file (zip it first) and we can take a look at it. Try and give a few examples of what your data will look like and the ultimate goal. It's fairly easy to step through every sheet in a book, or make a summary sheet. Whatever you want to do, only limited by your imagination. ;)

[mike575]

I searched the Microsoft Knowledge data base and found a work around but not a fix except to buy a newer version. Here the link: http://support.microsoft.com/default.aspx?scid=kb;en-us;186047&Product=xlw2K

the 186047 is the article number. I report if i can figure out a better work around other then creating a new worksheet just before recording a new macro. My initial thought is to go to a blank cell in the worksheet that will not be used and Turn on the recorder and enter a number , change the color of the font to white, then create the rest of the macro. Once I review the macro on the code page make the reference to the first entry absolute by entering $ in the cell reference. Just a thought.

mike in Wisconsin :type

[parry]

Hi Im not sure what your problem is. The article you posted says this...

SYMPTOMS: The Microsoft Visual Basic for Applications FormulaR1C1 property does not set a formula reference properly if a chart is active.
Maybe theres more to it but the obvious answer to me would be to make sure that the chart isnt active when setting the formula.

[mike575]

Here are two examples of just inserting text into cells. The first one was created using the Microsoft suggested fix listed a couple messages above.


Sub Example1()
' Exp1 Macro
' Macro recorded 7/27/2004 by mike
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"Cell C5 this macro was created using Microsoft Example of creating a new worksheet"
Range("C6").Select
ActiveCell.FormulaR1C1 = "Cell c6 Just befor recording this macro stop"
Range("C10").Select
ActiveCell.FormulaR1C1 = "last cell c10"
Range("C11").Select
End Sub

This second example was created after looking at the code of the first example. Run the macro a couple of times and and change the active cell by using your arrow keys. see the results change because the starting point changes with the active cell.



Sub Example2()
' Exaample2 Macro
' Macro recorded 7/27/2004 by mike
ActiveCell.FormulaR1C1 = _
"Cell b14 start location example of creating a macro without creating a new worksheet first"
Range("B15").Select
ActiveCell.FormulaR1C1 = "end b15"
Range("B15").Select
End Sub

[mvidas]

Hi Mike,

I am also a bit confused by what you're needing. If you don't like the recorded macro to start out with ActiveCell then just click on the cell you're entering data into before you enter it (even if you've already selected the cell). If it's just because of the ActiveCell notation at all, you can just edit the macro after you've recorded it. Personally I never immediately run a macro after recording, I will always review/edit it for efficiency, demonstrated by Zack above, as well as the following:

If you record a macro and it looks like:


Sub Macro6()
' Macro6 Macro
' Macro recorded 07/28/2004 by mvidas
Range("C10").Select
ActiveCell.FormulaR1C1 = "I am typing in cell C10"
Range("C15").Select
ActiveCell.FormulaR1C1 = "I am typing in cell C15"
Range("C20").Select
ActiveCell.FormulaR1C1 = "I am typing in cell C20"
Range("C25").Select
ActiveCell.FormulaR1C1 = "Last C25"
End Sub

Then a quick edit will bring that to:


Sub Macro6()
' Macro6 Macro
' Macro recorded 07/28/2004 by mvidas
Range("C10").FormulaR1C1 = "I am typing in cell C10"
Range("C15").FormulaR1C1 = "I am typing in cell C15"
Range("C20").FormulaR1C1 = "I am typing in cell C20"
Range("C25").FormulaR1C1 = "Last C25"
End Sub[/vba]

That not only looks better, but is also much more efficient (as selecting cells takes up valuable runtime), as well as being easier to trace.

But as I said, if the problem is just that the macro starts out with ActiveCell.FormulaR1C1... without having the selected cell written into code, just click the selected cell again after you begin recording to add it. That way you won't have to buy a new version and you'll have the recorded macro the way you want it.

Matt