User Profile
chancelin
Copper Contributor
Joined 7 years ago
User Widgets
Recent Discussions
The mode of a series of numeric values
Hello, I am looking for an excel function that will allow me to determine the mode of a series of nominal values. The MODE () function is used but only for numeric values. From a PivotTable or frequency distribution I can determine the mode. My problem is that I want to do it only from an Excel function. Thank you1.6KViews0likes5CommentsThe HasFormula property to Refer to a Range
I work a few hours with the property HasFormula and that always generates me an error message. But fortunately, I just found the solution to my problem. Indeed, The HasFormula property (which is read-only) returns True if the single cell range contains a formula. It returns False if the cell does not have a formula. If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don’t have a formula. The property returns a Null if there is a mixture of formulas and nonformulas in the range. Null is kind of a no-man’s land: The range contains a mixture of formulas and values. You need to be careful when you work with properties that can return Null. More specifically, the only data type that can deal with Null is Variant. And so that's the mistake I made. For example, assume that cell A1 contains a value and cell A2 contains a for- mula. The following statements generate an error because the range doesn’t consist of all formulas or all nonformulas: Dim FormulaTest As Boolean FormulaTest = Range(“A1:A2”).HasFormula The Boolean data type can handle only True or False. Null causes it to complain and throw up an error message. To fix this type of situation, the best thing to do is make sure that the FormulaTest variable is declared as a Variant rather than as a Boolean. The following example uses VBA’s handy TypeName function (along with an If-Then statement) to determine the data type of the FormulaTest variable. If the range has a mixture of formulas and nonformulas, the message box displays Mixed! Dim FormulaTest As Variant FormulaTest = Range(“A1:A2”).HasFormula If TypeName(FormulaTest) = “Null” Then MsgBox “Mixed!”1.1KViews0likes0CommentsTwo similar VBA Excel code
Here are two VBA property codes on the RANDE that mean the same thing: Range("A1:A10").Value = 99 Range(Cells(1, 1), Cells(10, 10)).Value = 99 In which case the second one really had to be used? In other words, in which case the property Cells, referring to the RANGE is really used?644Views0likes1Comment- 2.7KViews0likes0Comments
Re: Understand .Cells(.Rows.Count,“A”).End(xlUp).row
I understand better now. This line means: selects (.Select) the last non-empty cell (.End) up (xlUp) from the last cell (& Rows.Count) of column A (Range ("A")) So this command line behaves exactly as if you were in the last cell of column A (cell A1048576 for the 2007 version of Excel) and you press CTRL + up arrow. Why go from the bottom of the sheet to the top and not the other way around? Quite simply, to avoid a selection error if it turned out that the array contains empty cells.218KViews0likes3CommentsColumns and Rows Properties to Specify a Range
Hello family Here is a VBA code: FinalRow = Cells(Rows.Count, 1).End(xlUp).Row This line of code is supposed to search the last line of a sheet in which column A has a value and place the line number of that Range object in FinalRow. But I do not really understand this code712Views0likes0CommentsUse the Resize Property to Change the Size of a Range
The Resize property enables you to change the size of a range based on the location of the active cell. You can create a new range as needed. The syntax for the Resize property is Range.Resize(RowSize, ColumnSize) To create a range B3:D13, use the following: Range(“B3”).Resize(RowSize:=11, ColumnSize:=3) Or a simpler way to create this range: Range(“B3”).Resize(11, 3) But what if you need to resize by only a row or a column, not both? You do not have to enter both the row and column parameters. If you need to expand by two columns, use one of the following: or Range(“B3”).Resize(ColumnSize:=2) Range(“B3”).Resize(,2) Both lines mean the same. The choice is yours. Resizing just the rows is similar. You can use either of the following: or Range(“B3”).Resize(RowSize:=2) Range(“B3”).Resize(2) Once again, the choice is yours. It is a matter of readability of the code.1.5KViews0likes0CommentsUse the Offset Property to Refer to a Range
Offset enables you to manipulate a cell based off the location of the active cell. In this way, you do not need to know the address of a cell. The syntax for the Offset property is as follows: Range.Offset(RowOffset, ColumnOffset) The syntax to affect cell F5 from cell A1 is Range("A1").Offset(RowOffset:=4, ColumnOffset:=5) Or, shorter yet, write this: Range("A1").Offset(4,5) The count of the rows and columns starts at A1 but does not include A1. But what if you need to go over only a row or a column, but not both? You don’t have to enter both the row and column parameter. If you need to refer to a cell one column over, use one of these lines: Range("A1").Offset(ColumnOffset:=1) Range("A1").Offset(,1) Both lines mean the same, so the choice is yours. Referring to a cell one row up is similar: Range("B2").Offset(RowOffset:=-1) Range("B2").Offset(-1) Once again, you can choose which one to use. It is a matter of readability of the code.601Views0likes0CommentsReferencing Ranges in Other Sheets
Hello family, Referencing Ranges in Other Sheets, it's easier to use With ... End With, for exemple, With Worksheets(“Sheet2”) WorksheetFunction.Sum(.Range(.Range(“A1”), .Range(“A7”))) End With but a friend told me that we could also use the following code WorksheetFunction.Sum(Worksheets(“Sheet2”).Range(Worksheets(“Sheet2”). _ Range(“A1”), Worksheets(“Sheet2”).Range(“A7”))) what do you think?651Views0likes0Comments
Recent Blog Articles
No content to show