0

As we write Range(A4) as Cell(1,4).How should I need write Range(A2:AB256) into Cell format?

Thanks, Arup

Community
  • 1
  • 1
Arup Rakshit
  • 116,827
  • 30
  • 260
  • 317
  • @fencliff Can you help me in the following post of mine? -- http://stackoverflow.com/questions/13763603/excel-column-delete-using-vbscript/13765901#13765901 – Arup Rakshit Dec 07 '12 at 15:27

1 Answers1

1

Directly, you can't, but if your goal is to access a range of cells by their indexes, you can use the Range-method with two cells as arguments:

Set myRange = mySheet.Range(mySheet.Cells(1, 2), mySheet.Cells(28, 256))

Or you can parse the column letter from the cell address:

Dim columnNumber As Integer
Dim columnLetter As String

columnNumber = 5
columnLetter = Split(Cells(1, columnNumber).Address, "$")(1)
jevakallio
  • 35,324
  • 3
  • 105
  • 112
  • OK, But Is there any other way to get the Excel cloumn Alaphabetic value? Say I have a Value Under the column number 5. So if i know the column number, can i anyway get the Alphabetic value of the column which is actually "I"? – Arup Rakshit Dec 07 '12 at 11:06
  • Edited my answer. It's a bit of a hack, but it seems to work. There might be a better way, I just can't remember one. – jevakallio Dec 07 '12 at 11:22
  • I have multiple sheets in the spreadsheet,but i want it to work on my first sheet.So if i write it in this way columnLetter = Split(Sheet(1).Cells(1, columnNumber).Address, "$")(1) --- will it work for me? – Arup Rakshit Dec 07 '12 at 11:44
  • That method only returns the column letter, so it doesn't matter which sheet you use. The fifth column is letter E on every sheet. But yes, you can refer to a specific worksheet. I think the syntax is `ActiveWorkbook.Sheets(1)` – jevakallio Dec 07 '12 at 11:58
  • I worte the code as objSheet3.Range(objSheet3.Cells(1,1),objSheet3.Cells(DataCount,ParentColmnCount)).Copy objSheet6.Range(objSheet6.Cells(1,1),objSheet6.Cells(DataCount,ParentColmnCount)).PasteSpecial objSheet6.Activate --But it is copying only the first column values from the Sheet(3) to the first column of first column sheet(6)....Any idea why the full is not pasting out? – Arup Rakshit Dec 07 '12 at 12:09
  • One More Question any idea about the syntax of how to add a new column between two adjacent columns which are filled with data? – Arup Rakshit Dec 07 '12 at 12:26
  • If you have another question, I suggest you ask another question. – jevakallio Dec 07 '12 at 12:41
  • Can you help me in the following post of mine? http://stackoverflow.com/questions/13763603/excel-column-delete-using-vbscript/13765901#13765901 – Arup Rakshit Dec 07 '12 at 15:28
  • can you help me in the below post?----http://stackoverflow.com/questions/13746909/how-to-add-column-in-excel-using-vbscript – Arup Rakshit Dec 08 '12 at 14:56
  • can you help me in the below post? http://stackoverflow.com/questions/13788755/string-search-with-in-the-excel-column-values-row-wise/13788821#comment18964056_13788821 – Arup Rakshit Dec 09 '12 at 19:17