Putexcel dynamic columns

[Filip VD]

Hi,

I read in the help files that it's possible to make the excel row numbers dynamic in putexcel:

forvalues i=3/24 {
2. putexcel D‘i’=formula(B‘i’+C‘i’)
3. }

If there something similar for the columns?
I basically want to be able to loop where the results after a complete loop move two columns further:

putexcel C`row' = `EU16_percent' D`row' = EU16[2,`col']

So C should become E and D become F after a loop was completed.

I tried some things but none of them seem to work out.
Any input is welcome.

Kind regards,
Filip

[Clyde Schechter]

Sure.

local j 3
forvalues i = 3/24 {
    excelcol `j'
    local ccol `r(column)'
    excelcol `=`j'+1'
    local dcol `r(column)'
    putexcel `dcol'`i' = formula(B`i' + `ccol'`i')
    local j = `j'+2
}

The -putexcel- commands this code would execute are:

putexcel D3 = formula(B3 + C3)
putexcel F4 = formula(B4 + E4)
putexcel H5 = formula(B5 + G5)
putexcel J6 = formula(B6 + I6)
putexcel L7 = formula(B7 + K7)
putexcel N8 = formula(B8 + M8)
putexcel P9 = formula(B9 + O9)
putexcel R10 = formula(B10 + Q10)
putexcel T11 = formula(B11 + S11)
putexcel V12 = formula(B12 + U12)
putexcel X13 = formula(B13 + W13)
putexcel Z14 = formula(B14 + Y14)
putexcel AB15 = formula(B15 + AA15)
putexcel AD16 = formula(B16 + AC16)
putexcel AF17 = formula(B17 + AE17)
putexcel AH18 = formula(B18 + AG18)
putexcel AJ19 = formula(B19 + AI19)
putexcel AL20 = formula(B20 + AK20)
putexcel AN21 = formula(B21 + AM21)
putexcel AP22 = formula(B22 + AO22)
putexcel AR23 = formula(B23 + AQ23)
putexcel AT24 = formula(B24 + AS24)

-excelcol- is written by Sergiy Radyakin and is available from SSC.

It seems a little odd, and perhaps I am misunderstanding what you want, because you are asking to move diagonally down the spreadsheet as you proceed through the loop. But if I have misunderstood that, you can easily modify this code to get the pattern you want. The key is the -excelcol- command which converts a numerical index (a column "number") into the corresponding Excel letter coding.

[Filip VD]

Awesome this is exactly what I need. I just need a way to make the columns dynamic.
Thank you very much Clyde (and Sergiy!)