Retrieving the Column name (e.g. BT) from a numeric
Hi, I am importing a csv into Excel (Excel 2000) which will have a varying number of fields in the imported file. I can loop over a particular row to get the first blank cell in this row. As an example, the test file I am using comes back with the first blank cell as the numeric 72. By looking at the spreadsheet (or by dividing by 26 plus using the remainder), i can see that this translates to cell reference BT. I looked around in the VB help but couldn't find a property (like say .columname) which would allow me to use this for clearing contents and formatting the cells within the 'Active' worksheet. Here is my code to find the last valid column : For inti = 3 To 256 y = Worksheets("sheet1").Cells(4, inti).Value If y = "" Then intlastcol = inti inti = 257 End If Next inti Anyone know if there is such a beast out there ? Thanks in advance Niall Heelan |
Retrieving the Column name (e.g. BT) from a numeric
Hi Niall,
I'm not sure if this will help you or not, but when I'm doing this type of thing, I usually use .end(xltoRight).offset(0,1) to find the first empty You could set up a variable for the empty cell Dim emptyCell as range then do this: set emptyCell = range("A4").end(xltoRight).offset(0,1) That way you would have a reference to the first empty column and from there you could hop around wherever you want. Like I said, I don't know if it will apply to your situation. Jason " wrote: Hi, I am importing a csv into Excel (Excel 2000) which will have a varying number of fields in the imported file. I can loop over a particular row to get the first blank cell in this row. As an example, the test file I am using comes back with the first blank cell as the numeric 72. By looking at the spreadsheet (or by dividing by 26 plus using the remainder), i can see that this translates to cell reference BT. I looked around in the VB help but couldn't find a property (like say .columname) which would allow me to use this for clearing contents and formatting the cells within the 'Active' worksheet. Here is my code to find the last valid column : For inti = 3 To 256 y = Worksheets("sheet1").Cells(4, inti).Value If y = "" Then intlastcol = inti inti = 257 End If Next inti Anyone know if there is such a beast out there ? Thanks in advance Niall Heelan |
Retrieving the Column name (e.g. BT) from a numeric
VBA is pretty nice.
You don't need to use letters to refer to a column. Cells(1,"A") and Cells(1,1) both refer to A1. dim Inti as long dim IntLastCol as long intLastCol = -1 For inti = 3 To 256 y = Worksheets("sheet1").Cells(4, inti).Value If y = "" Then intlastcol = inti exit for End If Next inti if intlastcol = -1 then 'no blanks! else .cells(1, intlastcol).entirecolumn.clearcontents end if But if you want, here's a way to get the letter/string: dim ColLetter as string dim intlastcol as long intlastcol = 72 'or whatever you find 'this will return something like BT1 colletter = activesheet.cells(1,inlastcol).address(0,0) 'and strip the 1 from the string colletter = left(colleter, len(colletter)-1) msgbox colletter " wrote: Hi, I am importing a csv into Excel (Excel 2000) which will have a varying number of fields in the imported file. I can loop over a particular row to get the first blank cell in this row. As an example, the test file I am using comes back with the first blank cell as the numeric 72. By looking at the spreadsheet (or by dividing by 26 plus using the remainder), i can see that this translates to cell reference BT. I looked around in the VB help but couldn't find a property (like say .columname) which would allow me to use this for clearing contents and formatting the cells within the 'Active' worksheet. Here is my code to find the last valid column : For inti = 3 To 256 y = Worksheets("sheet1").Cells(4, inti).Value If y = "" Then intlastcol = inti inti = 257 End If Next inti Anyone know if there is such a beast out there ? Thanks in advance Niall Heelan -- Dave Peterson |
Retrieving the Column name (e.g. BT) from a numeric
Hi Dave, Thanks for the reply (you too Jason), that's what I was looking for. Regards, Niall Heelan On Apr 23, 4:17 pm, Dave Peterson wrote: VBA is pretty nice. You don't need to use letters to refer to a column. Cells(1,"A") and Cells(1,1) both refer to A1. dim Inti as long dim IntLastCol as long intLastCol = -1 For inti = 3 To 256 y = Worksheets("sheet1").Cells(4, inti).Value If y = "" Then intlastcol = inti exit for End If Next inti if intlastcol = -1 then 'no blanks! else .cells(1, intlastcol).entirecolumn.clearcontents end if But if you want, here's a way to get the letter/string: dim ColLetter as string dim intlastcol as long intlastcol = 72 'or whatever you find 'this will return something like BT1 colletter = activesheet.cells(1,inlastcol).address(0,0) 'and strip the 1 from the string colletter = left(colleter, len(colletter)-1) msgbox colletter " wrote: Hi, I am importing a csv into Excel (Excel 2000) which will have a varying number of fields in the imported file. I can loop over a particular row to get the first blank cell in this row. As an example, the test file I am using comes back with the first blank cell as the numeric 72. By looking at the spreadsheet (or by dividing by 26 plus using the remainder), i can see that this translates to cell reference BT. I looked around in the VB help but couldn't find a property (like say .columname) which would allow me to use this for clearing contents and formatting the cells within the 'Active' worksheet. Here is my code to find the last valid column : For inti = 3 To 256 y = Worksheets("sheet1").Cells(4, inti).Value If y = "" Then intlastcol = inti inti = 257 End If Next inti Anyone know if there is such a beast out there ? Thanks in advance Niall Heelan -- Dave Peterson |
All times are GMT +1. The time now is 10:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com