# array math without iteration

• ### 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

Rod Gill Project Management

Saturday, February 2, 2013 2:05 AM