Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find alpha in column 1 numeric column 2 | Excel Discussion (Misc queries) | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
Retrieving data from a corresponding row from a different column | Excel Discussion (Misc queries) |