How to use XLSWRITE when XLRANGE depends on a cell

Hi,

I run a Matlab code that extracts data from an Excel file. My data is sorted by column (e.g. dates from 2000 to 2012 in column A, Volatility corresponding to each date in column B...). In Excel, I find the row with the row function which I store in a cell. I would like to export my answer (I have 2 answers) to another Excel file to a specific that specific row each time.

For example, if my volatility is in row 7165, I would like to write my answers to cell D7165:E7165 (xlswrite('file',answers,'Sheet2',D1:E1)) But when I re-run the code with volatility row 7166, I want the answer to write in cell D7166:E7166. I would like the row to change automatically without me changing manually it everytime.

Thank you very much for your help,

Answer 1

[Image Analyst]

Read your data. Figure out the row number - I'm not sure how you get that, but it seems like you know how to get it. Then use sprintf() to create a range, and write data back out.

range = sprintf('D%d:E%d', rowNumber);  % e.g. rowNumber = 7165.
xlswrite(fullFileName, answers, 'Sheet2', range); % Answers is your data array.

[André]

Thanks, it works when I use range = sprintf('D%d', rowNumber).

However since my Excel file is pretty big (the column I want is AG) would you know why if I use: range = sprintf('AG%ag', rowNumber) instead of copying to rowNumber (e.g. AG7165) it copies to cell AG1 (row 1)

Just to let you know my answer is a 1x2 matrix.

Thanks again.

[Image Analyst]

There is no %a or %ag format specifier. Look up fprintf() in the help and you can see what is available. Use %d like I showed you. sprintf('AG%d', rowNumber). That could be the problem.

You don't need to put the lower right cell. If you use just the upper left cell then it will place your array with the upper left of your array at that cell. For example, if range = 'AG42', and you have a 40 by 5 array, the upper left element of that array will go into AG42. You don't need to specify 'AG42:AK81' because AG42 is good enough.

[André]

Thank you, your help was much appreciated.

[Beverly]

Do you know how to change the column similarly to the row?

[Image Analyst]

What form is your column in? Is it in a variable? A number? A letter? Or do you just want to hard code it in?