Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Cell formula promlem - Excel macro

  1. Feb 20, 2008 #1

    I have been having a problem with a section of my code in the macro that I've created. The problem is that the formulae that I want to use aren't working and soe I get a "##Name" error in the cells where the formula is supposed to have worked.

    Patientrange = Range(PatAgeLet & "2" & ":" & PatAgeLet & Locator).Address
    For Count = 2 To 6
    FormulaRange1 = Range(Infocol & Count).Offset(0, 1).Address
    FormulaRange2 = Range(Infocol & Count).Offset(0, 2).Address
    NewRange = Range(FormulaRange1 & ":" & FormulaRange2).Select
    With Selection
    .NumberFormat = "0"
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    If Count = 2 Then
    ActiveCell.Formula = "=MIN(PatientRange)"
    ElseIf Count = 3 Then
    ActiveCell.Formula = "=MAX(Patientrange)"
    ElseIf Count = 4 Then
    ActiveCell.Formula = "=MEDIAN(Patientrange)"
    ElseIf Count = 5 Then
    ActiveCell.Value = Rangarrsize
    ElseIf Count = 6 Then
    ActiveCell.Formula = "=COUNTIF(Patientrange, ""<=40"")"
    End If
    Next Count

    As you can see by this code Patientrange is a dynamic range and so this is the range that I want to use in my formulae. However, it just isn't working at all.

    Any suggestions??
  2. jcsd
  3. Feb 21, 2008 #2


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    Try dropping the .address from your range specifications.

    I do not see where you have initialized Infocol.

    Do not give up keep reading the help on how to specify a range.
  4. Feb 23, 2008 #3
    Thanks for the reply Integral. Sorry about that but I was only providing a segment of the code so that's why you saw that infocol wasn't initialised.

    I'm not sure if you're interested at all, but I found out what the problem was. When you specify the formula with the dynamic range you must put the dynamic range in quotes so instead of having ActiveCell.Formula = "=MEDIAN(Patientrange)" you must have ActiveCell.Formula = "=MEDIAN(" & Patientrange & ")".
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Similar Discussions: Cell formula promlem - Excel macro
  1. VBA macro programming (Replies: 2)

  2. Vim Macros for Python (Replies: 3)