Multiplying a range with a constant in vba

[Brijesh]

I have an excel workbook with three sheets "Benefits", "Factors" and "Assumptions". I have to multiply a variable Range from Cell(14, 35) to Cell(13 + Term, 35) on sheet "Benefits" with a constant which is inputted in a cell defined on sheet "Assumptions" with name "FactorMultiple". After multiplying the new range of values is to be pasted on sheet "Factors" in cell (1,1).

I am using following code for multiplication in vba:

Set FactRange = Range(Cells(14, 35), Cells(13 + Term, 35))
FactRange = FactRange * [FactorMultiple]

This code gives error "Type mismatch". I have tried to get solutions on this error but there was no simple one line code for multiplication.

Some time before I have to do the same sort of operation in another excel workbook. The only difference was that the range in that workbook was defined as a named range in excel, say "Range1" and the constant with which this range was to be multiplied was also defined with a name in excel, Say "MulCon". In vba of that workbook I used following code and it worked fine:

[Range1]=[Range1 * MulCon]

Is there any such kind of single line code for the multiplication I have mentioned at start of this query.

 

[shrivallabha]

When you are setting Factrange using keyword "Set" it is setting the variable to Range Object. And next time you try to assign array result to it says type mismatch. One way of handling this is:

Set FactRange = Range(Cells(14, 35), Cells(13 + Term, 35))
vFactRange = Evaluate("=" & FactRange.Address & "*FactorMultiple")

vFactRange will hold an array of result which can be used further.

 

[Brijesh]

If I don't use keyword "Set" and use following code:

Range(Cells(14, 35), Cells(13 + Term, 35)) =Range(Cells(14, 35), Cells(13 + Term, 35))* [FactorMultiple]

Even with this code I get the same error "Type mismatch"

Further what is vFactRange? Is it anyfunction which uses FactRange or it is simply name assigned to new range after multiplication?

 

[Debraj]

just one more approach..

Range("FactorMultiple").Copy
Range(Cells(14, 35), Cells(13 + Term, 35)).PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
Application.CutCopyMode = False

or may be another approach..

With Range(Cells(14, 35), Cells(13 + Term, 35))
    .Value = WorksheetFunction.MMult(.Value, Range("FactorMultiple"))
End With

PS: Be sure that FactorMultiple is contain only one cell..

 

[shrivallabha]

vFactRange is a variable like FactRange which returns an array[Read last line of my reply]. It will be considered of type variant if not dimensioned.

Deb has given you some alternatives which you could try.

The square brackets at the end of the code you posted is the EVALUATE functionality.

 

[NARAYANK991]

Hi Brijesh ,

Try this :

Public Sub temp()
          Term = 5
          Set FactRange = Range(Cells(14, 35), Cells(13 + Term, 35))
          FactRange.FormulaArray = Evaluate("=" & FactRange.Address & "*" & Range("FactorMultiple").Address)
End Sub

I have assumed that Term is a VBA variable defined within the procedure. I have assigned an arbitrary value of 5 to it , so that the range is now 5 cells high.

I have also assumed that FactorMultiple is a physical named range , referring to a worksheet cell.

The following code works whether FactorMultiple is a physical or a virtual named range :

Public Sub temp()
          Term = 5
          Set FactRange = Range(Cells(14, 35), Cells(13 + Term, 35))
          FactRange.FormulaArray = Evaluate("=" & FactRange.Address & "*" & [FactorMultiple])
End Sub

Narayan