Hi, The follouiwing Excel macro runs smoothly. However, I would like to change t

Question

Hi,

The follouiwing Excel macro runs smoothly. However, I would like to change the cell name C113 to a variable in the code Range ("C3:C113").Select so I could change C133 (or define a universal range) in the begging of the program in stead of changing the cell names in the entire programs (there are more sheets that I have not yet included). Thanks in advance for yiour assistance.

Sub Macro3()

ActiveCell.FormulaR1C1 = _
        "=IF(AND(ISNUMBER(NSF!RC),ISNUMBER(NSF!RC[-1])),100*(NSF!RC/NSF!RC[-1]-1),"""")"
    Range("C2").Select
    Selection.Copy
    Range("D2:R2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C2:R2").Select
    Selection.Copy
    Range("C3:C113").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
  
    Sheets("EXH-%Change").Select
   Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(ISNUMBER(EXH!RC),ISNUMBER(EXH!RC[-1])),100*(EXH!RC/EXH!RC[-1]-1),"""")"
    Range("C2").Select
    Selection.Copy
    Range("D2:R2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C2:R2").Select
    Selection.Copy
    Range("C3:c113").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Explanation / Answer

Solution:

A possible solution is Cell names. We can name a cell using Name box (which is left to the formula bar). This name is used as the variable name inside macro programming.

Explanation:

In your program, you can name C113 as "cellRange". select cell C113 and go to the name box. Type the name "cellRange" without quotes and press enter. So you can change the line Range("C3:c113").Select into

Range("C3:cellRange").Select.

Later if you want to change the cell range to C133, you can simply remove the cell name for C113. Give the 'cellRange' Name to C133.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.