Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
I like autofit, but sometimes you want to hand-size a column. But then
when the numbers overflow, it's nice to be able to jiggle them. But how do I find which columns are not wide enough? Is there a trick? Maury |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
Maury,
You need to use the Text property of the range object: Sub FindHashes() Dim myC As Range For Each myC In Range("A1:C10") 'Change to suit If InStr(1, myC.Text, "#") 0 Then MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value End If Next myC End Sub HTH, Bernie MS Excel MVP "Maury Markowitz" wrote in message ... I like autofit, but sometimes you want to hand-size a column. But then when the numbers overflow, it's nice to be able to jiggle them. But how do I find which columns are not wide enough? Is there a trick? Maury |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
Of course, that will fail if your actual values have octothorps included.
HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Maury, You need to use the Text property of the range object: Sub FindHashes() Dim myC As Range For Each myC In Range("A1:C10") 'Change to suit If InStr(1, myC.Text, "#") 0 Then MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value End If Next myC End Sub HTH, Bernie MS Excel MVP "Maury Markowitz" wrote in message ... I like autofit, but sometimes you want to hand-size a column. But then when the numbers overflow, it's nice to be able to jiggle them. But how do I find which columns are not wide enough? Is there a trick? Maury |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
Bernie,
Well that broadened my command of the english language!! I've never heard of then called that before and in all honesty looked it up before responding. Now how can I slip that into conversation?? :) Mike "Bernie Deitrick" wrote: Of course, that will fail if your actual values have octothorps included. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Maury, You need to use the Text property of the range object: Sub FindHashes() Dim myC As Range For Each myC In Range("A1:C10") 'Change to suit If InStr(1, myC.Text, "#") 0 Then MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value End If Next myC End Sub HTH, Bernie MS Excel MVP "Maury Markowitz" wrote in message ... I like autofit, but sometimes you want to hand-size a column. But then when the numbers overflow, it's nice to be able to jiggle them. But how do I find which columns are not wide enough? Is there a trick? Maury |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
Mike,
I blame "A Word A Day" ;-) Bernie MS Excel MVP "Mike H" wrote in message ... Bernie, Well that broadened my command of the english language!! I've never heard of then called that before and in all honesty looked it up before responding. Now how can I slip that into conversation?? :) Mike "Bernie Deitrick" wrote: Of course, that will fail if your actual values have octothorps included. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Maury, You need to use the Text property of the range object: Sub FindHashes() Dim myC As Range For Each myC In Range("A1:C10") 'Change to suit If InStr(1, myC.Text, "#") 0 Then MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value End If Next myC End Sub HTH, Bernie MS Excel MVP "Maury Markowitz" wrote in message ... I like autofit, but sometimes you want to hand-size a column. But then when the numbers overflow, it's nice to be able to jiggle them. But how do I find which columns are not wide enough? Is there a trick? Maury |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
Wow, that should have been obvious in retrospect...
Thanks! Maury |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
On Sep 29, 1:26*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: For Each myC In Range("A1:C10") 'Change to suit Wait, is something missing here? Shouldn't that be myC.something? Perhaps I misunderstand the way this works? Also, is this going to loop over every single cell in the sheet? Or does .text do something clever? Maury |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
Maury,
Shouldn't that be myC.something? No. myC is a range object, so what those lines mean is "for every cell in the cells of this range do this" As written, the code will check only the 30 cells in the range A1:C10. You can change that to Do all the cells in a specific range: For Each myC In Range("A11:H200") Do all the cells in that have constants values: For Each myC In Cells.SpecialCells(xlCellTypeConstants, 23) Do all the cells in that have formulas: For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23) and so on. What cells do you want to check? HTH, Bernie MS Excel MVP "Maury Markowitz" wrote in message ... On Sep 29, 1:26 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: For Each myC In Range("A1:C10") 'Change to suit Wait, is something missing here? Shouldn't that be myC.something? Perhaps I misunderstand the way this works? Also, is this going to loop over every single cell in the sheet? Or does .text do something clever? Maury |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I find columns with #### in them?
On Sep 29, 11:24*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: What cells do you want to check? Oh it works fine, it was just my confusion over what Excel did if you For Each over a range. It seems to be reasonably fast too, that was my other concern, that looping over every cell on my large sheets would be expensive. Maury |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find an intersect of two columns | New Users to Excel | |||
Find the first value in a row and sum n columns | Excel Worksheet Functions | |||
find the max in different columns | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find columns | Excel Programming |