Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the formula an array formula?
I'd like to insert a cell's formula into a comment. My
only issue is testing to see if it's an array formula or normal formula. As of now, the macro always says it's an array formula. Thanks for any help you can provide. Sub FormulaInComment() Dim ConfirmBox As String Dim FormulaType As String ConfirmBox = MsgBox("Proceed?", vbYesNo) If ConfirmBox = vbNo Then Exit Sub With ActiveCell If .HasFormula Then If IsNull(.FormulaArray) Then FormulaType = "Formula:" Else FormulaType = "Array Formula:" End If Else MsgBox "No formula in cell!" Exit Sub End If .AddComment .Comment.Text Text:=FormulaType & Chr(10) & .Formula .Comment.Visible = False .Copy .PasteSpecial (xlPasteValues) End With Application.CutCopyMode = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the formula an array formula?
Sub FormulaInComment()
Dim ConfirmBox As String Dim FormulaType As String ConfirmBox = MsgBox("Proceed?", vbYesNo) If ConfirmBox = vbNo Then Exit Sub With ActiveCell If .HasFormula Then If Not .HasArray Then FormulaType = "Formula:" Else FormulaType = "Array Formula:" End If Else MsgBox "No formula in cell!" Exit Sub End If .AddComment .Comment.Text Text:=FormulaType & Chr(10) & .Formula .Comment.Visible = False .Copy .PasteSpecial (xlPasteValues) End With Application.CutCopyMode = False End Sub -- Regards, Tom Ogilvy "Jason Morin" wrote in message ... I'd like to insert a cell's formula into a comment. My only issue is testing to see if it's an array formula or normal formula. As of now, the macro always says it's an array formula. Thanks for any help you can provide. Sub FormulaInComment() Dim ConfirmBox As String Dim FormulaType As String ConfirmBox = MsgBox("Proceed?", vbYesNo) If ConfirmBox = vbNo Then Exit Sub With ActiveCell If .HasFormula Then If IsNull(.FormulaArray) Then FormulaType = "Formula:" Else FormulaType = "Array Formula:" End If Else MsgBox "No formula in cell!" Exit Sub End If .AddComment .Comment.Text Text:=FormulaType & Chr(10) & .Formula .Comment.Visible = False .Copy .PasteSpecial (xlPasteValues) End With Application.CutCopyMode = False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is the formula an array formula?
I had one of these thingies in a workbook. I thought it would be very useful to
document what happened before (and if I had to revert). If you're thinking of doing the same thing, you may want to use .formular1c1 I inserted rows (not columns) in my data and every "retrieved" formula was now pointing at the correct column in the wrong row. It was not as useful as I hoped. Jason Morin wrote: I'd like to insert a cell's formula into a comment. My only issue is testing to see if it's an array formula or normal formula. As of now, the macro always says it's an array formula. Thanks for any help you can provide. Sub FormulaInComment() Dim ConfirmBox As String Dim FormulaType As String ConfirmBox = MsgBox("Proceed?", vbYesNo) If ConfirmBox = vbNo Then Exit Sub With ActiveCell If .HasFormula Then If IsNull(.FormulaArray) Then FormulaType = "Formula:" Else FormulaType = "Array Formula:" End If Else MsgBox "No formula in cell!" Exit Sub End If .AddComment .Comment.Text Text:=FormulaType & Chr(10) & .Formula .Comment.Visible = False .Copy .PasteSpecial (xlPasteValues) End With Application.CutCopyMode = False End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
A formula needed - probably an array formula | Excel Programming |