Changing excel range reference

6 views (last 30 days)
Jeff
Jeff on 1 Feb 2014
Answered: Image Analyst on 1 Feb 2014
Hi
I'm trying to write a loop that reads a specific range of a worksheet each time, then for the next loop, the range shifts down 48 cells.
filename = xxx
sheet = 1
xlRange = D2:D49
f=xlsread(filename,sheet,xlRange)
the range then changes to D50:D97, then D98:D145 ...
I tried concatenate 2 strings to form the cell reference but it didn't work:
sRow = 2
lRow = sRow+47
sRowRef = strcat('D',num2str(sRow))
lRowRef=strcat('D',num2str(lRow))
xlRange='sRowRef:lRowRef'
Then I would just do sRow + 48
Unfortunately, xlsread didn't recognise this new xlRange. Is there another way to do it? Thanks

Accepted Answer

Amit
Amit on 1 Feb 2014
Edited: Amit on 1 Feb 2014
Your xlRange will produce string 'sRowRef:lRowRef' not 'D2:D49'. You should try:
xlRange = [sRowRef ':' lRowRef];
  3 Comments
Amit
Amit on 1 Feb 2014
which line do you get the error from? did you used
xlRange = [sRowRef ':' lRowRef];
or otherwise you can use,
xlRange = strcat(sRowRef, ':', lRowRef) % IF this is more familiar to you
Jeff
Jeff on 1 Feb 2014
I got it. Forgot to put space in between. THANKS!!

Sign in to comment.

More Answers (1)

Image Analyst
Image Analyst on 1 Feb 2014
Try this:
startingRow = 2;
for k = 1 : 10
cellRef = sprintf('D%d:D%d', startingRow, startingRow + 47);
fprintf('cellRef = %s\n', cellRef); % Show in command window.
startingRow = startingRow + 48;
end
In command window:
cellRef = D2:D49
cellRef = D50:D97
cellRef = D98:D145
cellRef = D146:D193
cellRef = D194:D241
cellRef = D242:D289
cellRef = D290:D337
cellRef = D338:D385
cellRef = D386:D433
cellRef = D434:D481

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!