array math without iteration RRS feed

  • Question

  • Hi,

    I'm looking for a fast way to multiply two arrays in excel without having to do a for loop or similar iteration. I found the following code which works if the two ranges are on the same page:

    Sub Norm()
        Dim s As Worksheet
        Dim rngX As Range
        Dim rngY As Range
        Set s = Sheet1
        Set rngX = s.Range("A2:A15")
        Set rngY = s.Range("C2:C15")
        rngY = Application.Evaluate("=" & rngX.Address & "/Max(" & rngX.Address & ")")
    End Sub

    However, if I try to set rngY onto Sheet2, I end up with an array of zeroes. Is there any way to use a similar approach while avoiding the range reference issues?

    Friday, February 1, 2013 11:54 PM

All replies

  • By default, Address returns only the cell address without specifying the sheet. You can return the complete address of a range, including workbook and worksheet by using Address(External:=True). For example:

    Sub Norm()
        Dim rngX As Range
        Dim rngY As Range
        Set rngX = Worksheets("Sheet1").Range("A2:A15")
        Set rngY = Worksheets("Sheet2").Range("C2:C15")
        rngY = Application.Evaluate("=" & rngX.Address(External:=True) & _
            "/Max(" & rngX.Address(External:=True) & ")")
    End Sub

    Regards, Hans Vogelaar

    Saturday, February 2, 2013 12:25 AM
  • Thanks for that Hans, I hadn't spotted the parameters for Address before. Will save me doing workarounds in the future!!

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Saturday, February 2, 2013 2:05 AM