VBA Manually Transpose Variant of Arrays

Question

Below is my variant of Arrays and my for loop where I tried to manually transpose it, but it didn't work. I am not able to use the Application.Transpose b/c I will have some levels within the arrays longer than the 255 char. I am also trying to do this within the same function. Any suggestions would be appreciated.

data(0) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[1048574]C[-18]=RC[-18],COUNTIF(RC[-9]:R[1048574]C[-9],0)=0),AVERAGE(RC[-9]:R[1048574]C[-9]),0)", "=IF(AND(R[1048574]C[-19]=RC[-19], COUNTIF(RC[-9]:R[1048574]C[-9],0)=0),AVERAGE(RC[-9]:R[1048574]C[-9]),0)", "=IF(AND(R[1048574]C[-20]=RC[-20],COUNTIF(RC[-9]:R[1048574]C[-9],0)=0),AVERAGE(RC[-9]:R[1048574]C[-9]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[6]:R[1048571]C[6],0)=0),AVERAGE(RC[6]:R[1048571]C[6]),0)", "=IF(R[1048571]C[-23]=RC[-23],AVERAGE(RC[6]:R[1048571]C[6]),0)", "=(INTERCEPT(RC[-11]:R[5]C[-11],RC[-15]:R[5]C[-15])+(RC[-15]*SLOPE(RC[-11]:R[5]C[-11],RC[-15]:R[5]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
data(1) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-9]:RC[-9],0)=0),AVERAGE(R[-2]C[-9]:RC[-9]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-9]:RC[-9]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-9]:RC[-9],0)=0),AVERAGE(R[-2]C[-9]:RC[-9]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[-1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-23]=RC[-23],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=(INTERCEPT(R[-1]C[-11]:R[4]C[-11],R[-1]C[-15]:R[4]C[-15])+(RC[-15]*SLOPE(R[-1]C[-11]:R[4]C[-11],R[-1]C[-15]:R[4]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
data(2) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[-1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-23]=RC[-23],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=(INTERCEPT(R[-2]C[-11]:R[3]C[-11],R[-2]C[-15]:R[3]C[-15])+(RC[-15]*SLOPE(R[-2]C[-11]:R[3]C[-11],R[-2]C[-15]:R[3]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
data(3) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[-1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-23]=RC[-23],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
data(4) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-5]C[-21]=RC[-21],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-22]=RC[-22],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-23]=RC[-23],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
data(5) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-5]C[-21]=RC[-21],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-22]=RC[-22],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-23]=RC[-23],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
data(6) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-5]C[-21]=RC[-21],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-22]=RC[-22],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-23]=RC[-23],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")

Xlower = LBound(data(0))
Yupper = LBound(data)

Xupper = UBound(data(0)) '15
Yupper = UBound(data) '6
ReDim tempdata(Xupper, Yupper)

For x = Xlower To Xupper
    For y = Ylower To Yupper
        tempdata(x, y) = data(y)(x)
    Next y
Next x


repXlower = LBound(tempdata(0))
repYupper = LBound(tempdata)

repXupper = UBound(tempdata(0))
repYupper = UBound(tempdata)
ReDim reptempdata(repXupper, repYupper)

For x = repXlower To repXupper
    For y = repYlower To repYupper
        reptempdata(x, y) = tempdata(y)(x)
    Next y
Next x

Answer 1

You're working with Jagged arrays, and they're 0 based.

Try this:

Option Explicit


Sub foo()
  Dim data(6)
  Dim Xupper
  Dim Xlower
  Dim Yupper
  Dim Ylower
  Dim x
  Dim y

  data(0) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[1048574]C[-18]=RC[-18],COUNTIF(RC[-9]:R[1048574]C[-9],0)=0),AVERAGE(RC[-9]:R[1048574]C[-9]),0)", "=IF(AND(R[1048574]C[-19]=RC[-19], COUNTIF(RC[-9]:R[1048574]C[-9],0)=0),AVERAGE(RC[-9]:R[1048574]C[-9]),0)", "=IF(AND(R[1048574]C[-20]=RC[-20],COUNTIF(RC[-9]:R[1048574]C[-9],0)=0),AVERAGE(RC[-9]:R[1048574]C[-9]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[6]:R[1048571]C[6],0)=0),AVERAGE(RC[6]:R[1048571]C[6]),0)", "=IF(R[1048571]C[-23]=RC[-23],AVERAGE(RC[6]:R[1048571]C[6]),0)", "=(INTERCEPT(RC[-11]:R[5]C[-11],RC[-15]:R[5]C[-15])+(RC[-15]*SLOPE(RC[-11]:R[5]C[-11],RC[-15]:R[5]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
  data(1) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-9]:RC[-9],0)=0),AVERAGE(R[-2]C[-9]:RC[-9]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-9]:RC[-9]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-9]:RC[-9],0)=0),AVERAGE(R[-2]C[-9]:RC[-9]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[-1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-23]=RC[-23],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=(INTERCEPT(R[-1]C[-11]:R[4]C[-11],R[-1]C[-15]:R[4]C[-15])+(RC[-15]*SLOPE(R[-1]C[-11]:R[4]C[-11],R[-1]C[-15]:R[4]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
  data(2) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[-1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-23]=RC[-23],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=(INTERCEPT(R[-2]C[-11]:R[3]C[-11],R[-2]C[-15]:R[3]C[-15])+(RC[-15]*SLOPE(R[-2]C[-11]:R[3]C[-11],R[-2]C[-15]:R[3]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
  data(3) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[1048571]C[-21]=RC[-21],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[-1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-22]=RC[-22],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=IF(AND(R[1048571]C[-23]=RC[-23],COUNTIF(RC[-6]:R[1048571]C[-6],0)=0),AVERAGE(RC[-6]:R[1048571]C[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
  data(4) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-5]C[-21]=RC[-21],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-22]=RC[-22],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-23]=RC[-23],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
  data(5) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-5]C[-21]=RC[-21],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-22]=RC[-22],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-23]=RC[-23],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")
  data(6) = Array("=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]/DAY(EOMONTH(RC[-2],0))", "=RC[-2]", "=RC[-3]+RC[-2]/6", "=RC[-4]+RC[-3]/14", "=IF(AND(R[-2]C[-18]=RC[-18],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(R[-2]C[-19]=RC[-19],AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-2]C[-20]=RC[-20],COUNTIF(R[-2]C[-3]:RC[-3],0)=0),AVERAGE(R[-2]C[-3]:RC[-3]),0)", "=IF(AND(R[-5]C[-21]=RC[-21],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-22]=RC[-22],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=IF(AND(R[-5]C[-23]=RC[-23],COUNTIF(R[-5]C[-6]:RC[-6],0)=0),AVERAGE(R[-5]C[-6]:RC[-6]),0)", "=(INTERCEPT(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])+(RC[-15]*SLOPE(R[-3]C[-11]:R[2]C[-11],R[-3]C[-15]:R[2]C[-15])))-RC[-11]", "=IF(RC[-11]=0,0,IF(RC[-1]>RC[-11],0,1))", "=IF(RC[-1]=1,RC[-11],0)", "=0", "=0")

  Xlower = LBound(data(0))
  Ylower = LBound(data)

  Xupper = UBound(data(0)) '15
  Yupper = UBound(data) '6
  ReDim tempdata(Xupper, Yupper)

  For x = Xlower To Xupper
      For y = Ylower To Yupper
          tempdata(x, y) = data(y)(x)
      Next y
  Next x
End Sub