Menu
I recently found out that it is possible to set values inside each cell of a range with a single command like:
Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)) = MyMatrix
Where MyMatrix is a 2D matrix, with dimensions: Row2-Row1 and Column2-Column1.
Apparently though, if I do the same for applying a property to each cell (let's say .Font.Bold - when MyMatrix is a boolean matrix), it doesn't work:
Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)).Font.Bold = MyMatrix
The command above makes the whole range "bold-flicker" for a fraction of second and then nothing happens. How come?
I would definitely like to avoid the For cycle, because in my code it takes too long.
UPDATE: the same does not work even if I fill MyMatrix with the strings "normal" and "bold" and then write:
Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)).Font.FontStyle = MyMatrix
I also tried (and it doesn't work):
Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)).Cells.Font.FontStyle = MyMatrix
515
Use the “Dim” keyword and enter the name of the variable. Declare the keyword as Range. Type the name of the variable. Specify the range that you want to assign to the variable.
Steps to Add a Range into an Array in VBA First, you need to declare a dynamic array using the variant data type. Next, you need to declare one more variable to store the count of the cells from the range and use that counter for the loop as well. After that, assign the range where you have value to the array.
In VBA, Range is an object, but Cell is a property in an excel sheet. In VBA, we have two ways of referencing a cell object one through Range, and another one is through Cells. For example, if you want to reference cell C5, you can use two methods to refer to the cell C5.
As other answers have said, the .Font property can only be set to a scalar value, not a matrix, but that it can set bulk ranges at once.
One way to get around this would be to construct a String containing the cell references to all the cells that should have a certain font, rather than a matrix of True and False etc. Then just change the font for that range. E.g.
Dim strRange as String
strRange = "A1,B7,C3,D1" ' set this in a loop or whatever
Worksheet.Range(strRange).Font.Bold = True
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
