0

Excel cell - Cells(1, "Z") contains some value. Now I want to write 10 cells after Z. It would be Cells(1,"AJ"). Can I write macro which will add 10 in 'Z' and give me excel column name i.e. AJ ?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Vishal
  • 339
  • 1
  • 9
  • 26
  • 2
    Yes you can. I can give you the answer but I want you to try it first. You can use a `FOR` loop using `STEP`. Give it a try and if you are stuck then post the code that you tried and we will take it from there – Siddharth Rout Oct 31 '14 at 07:33
  • Also `cells(1,"Z")` can be written as `cells(1,26)` or `cells(1,i)` where `i` can be incremented in the `For` loop. :) – Siddharth Rout Oct 31 '14 at 07:43

1 Answers1

2

You might add two functions to your project:

  1. The first, transforming the letter into the respective column number;
  2. The second, re-transforming the number (after the addition) into letter.

So here you go:

Sub YourMacro()

columnLetter = ColLtr(ColNum("Z") + 10)
MsgBox columnLetter

End Sub

Function ColLtr(iCol As Long) As String
    If iCol > 0 And iCol <= Columns.Count Then
        ColLtr = Evaluate("substitute(address(1, " & iCol & ", 4), ""1"", """")")
    End If
End Function

Function ColNum(iCol As String) As Long
    ColNum = Range(iCol & 1).Column
End Function

Then re-write the code according to your needs, this is simply giving you back the alphabetic version of "10 columns after the Z".

Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89
  • Even if you can always work with column indexes by keeping them numeric, this is just if you really want to learn how to convert letter into numbers, doing your operations and then reconverting them back to letters. – Matteo NNZ Oct 31 '14 at 07:45
  • 3
    You didn't have to take so much pain in writing something which has already been covered in SO before ;) See [this](http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name/10107264#10107264) – Siddharth Rout Oct 31 '14 at 07:46
  • 2
    @SiddharthRout you're right, users should always make some effort first since some answers are really easy-to-find. Will try to refrain my will of writing code next time and wait a bit for user effort :) – Matteo NNZ Oct 31 '14 at 07:50