Cell formula promlem - Excel macro

In summary, the user is having trouble with the formulae in their macro due to a "##Name" error. They have a dynamic range called Patientrange that they want to use in their formulae, but it is not working. They are seeking suggestions to fix the issue and have found a solution by putting the dynamic range in quotes in the formula.
  • #1
big man

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??
Technology news on Phys.org
  • #2
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.
  • #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 & ")".

1. What is a cell formula in Excel?

A cell formula is a set of instructions that tells Excel how to perform calculations or manipulate data in a specific cell. It uses mathematical operators, cell references, and functions to perform the desired task.

2. How do I write a cell formula in Excel?

To write a cell formula, you can either manually type it into the cell or use the formula bar. Type an equal sign (=) followed by the formula or function you want to use, and then press Enter. You can also click on the cell you want to use in the formula to automatically add its reference.

3. Can I use cell formulas in macros?

Yes, you can use cell formulas in Excel macros. Macros allow you to automate repetitive tasks and perform complex calculations using cell formulas. You can record a macro or write one using the Visual Basic for Applications (VBA) editor.

4. How do I fix a cell formula error?

If your cell formula is not working correctly, you may have to fix an error. Common errors in cell formulas include #REF (reference error), #VALUE (value error), and #DIV/0 (divide by zero error). To fix these errors, double-click on the cell to see which part of the formula is causing the error, and then make the necessary changes.

5. Can I use cell formulas to analyze data in Excel?

Yes, cell formulas are a powerful tool for analyzing data in Excel. You can use formulas to perform calculations, such as finding averages, sums, and percentages, as well as to manipulate data, such as sorting and filtering. With the use of functions, you can also perform more complex data analysis, such as regression analysis and pivot tables.

Similar threads

  • Programming and Computer Science