Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm an intermediate C# programmer, but I'm just starting out with Office automation, specifically Excel for now. I've got to say, the Office API is lacking, or at least it forces you to think about problems differently. One thing that's driving me nuts is cell numbers, such as A1 and B5 and so on. I'm forced to manipulate them often, but there's no easy way to do this. For example, if I'm on column C7 and want to copy or move something to B7, I can't just use --C7. Instead I have to figure out the numerical value of C, decrement it, turn it back into a letter then concatenate it with the row number again.

I could write methods to do this myself (e.g. decrementColumn(), decrementRow(), addColumns( String currentCellName, int howManyToAdd) ), but I don't want to reinvent the wheel. Does a library of functions exist for such oft-needed conversions or am I going to have to roll my own?

share|improve this question

1 Answer

up vote 2 down vote

To copy/move values easily, you can use the .Offset method, which returns a Range.

For example, if the range/cell you are working with is C7, where rng represents this Range object:

rng.Offset(0,-1).Value = rng.Value

This returns the range, offset by -1 colums.

rng.Offset(10,15) would return a cell/range 10 rows below, and 15 columns right, etc.

You may also look at R1C1 address style in Excel, although I have never been fond of that. This link for Excel 2007 but should be mostly appropriate for any version of Excel.

http://msdn.microsoft.com/en-us/library/office/ee264226(v=office.12).aspx

share|improve this answer
Thanks, this will help a lot! – Frecklefoot yesterday
1  
Also, you can always refer to a range by its cell location, e.g., Sheets(1).Cells(1,1) is equivalent to Sheets(1).Range("A1"), and .Cells(109,13) is equivalent to .Range("M109"), etc. That's probably messier than using the .Offset method, but it's at least another option for you to explore. – David Zemens yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.