F2 Cells in a column

[Darin Kramer]

Howdie,

For whatever reason excel is "not happy" with the data in column K. If
I merely press F2 (edit mode) and then enter, it fixes the problem and
Excel is happy! Clearly I dont want to press F2 on each cell....
(currently the cell says mailto:[email protected] for a whole bunch of
people...)

Any ideas...?

Me

*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

Not too sure what you mean by not happy but here is some code to hopefully
fix the problem... Not that it will not overwrite formulas, it only changes
constants...

Sub FixStuff()
Dim rng As Range
Dim rngToFix As Range

On Error Resume Next
Set rngToFix = Range("K:K").SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rngToFix Is Nothing Then
For Each rng In rngToFix
rng.Value = rng.Value
Next rng
End If
End Sub

 

[JW]

Sounds like it is reformatting it when you F2 into the cell and press
enter. Do you have them formatted as hyperlinks ot just text? If just
text, you should be able to make Excel happy by inserting a new column
and just doing something like this:
=TEXT(A1,"@")

 

[Darin Kramer]

Jim Sorry for not being clearer....by not happy I mean when I copy and
paste the data across into a "list" and try and synchronise the list I
get an error message. If I F2 the cell, change nothing I dont anymore...
I ran the macro... does not seem to have solved the problem....


*** Sent via Developersdex http://www.developersdex.com ***

 

[JW]

And the formula I provided doesn't work either? Odd. What format is
the value in the cell? You could always strong arm Excel and cycle
through your column of data with a VBA routine hitting F2 and enter,
but that would be a last resort IMO because it forces a lot of
overhead through Excel. If you decide to go this route, make sure
that you execute the code by going to Tools--Macro-->Macros.
Otherwise, the code will fire in the VBE and simply show the Object
Browser.
This is assuming your bad data column is A, or 1. Simply change the
col variable to the column number containing your bad data.
Sub strongarm()
Dim col As Integer, r As Range
col = 1 'column number containing bad data
For Each r In Range(Cells(2, col), _
Cells(Cells(65536, col).End(xlUp).Row, col))
Application.SendKeys ("{F2}~")
Next r
End Sub

 

[Darin Kramer]

Thanks for the effort.. I know get a "search string must be specified
error?
VB is getting stuck on the line Application.SendKeys ("{F2}~")


?

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

 

[JW]

Darin, that code runs fine for me on my end. Not sure why it would be
giving you an error. Feel free to send me your spreadsheet and I'll
take a look at it.

 

[Darin Kramer]

Thanks - where should I send it too....?


*** Sent via Developersdex http://www.developersdex.com ***

 

[JW]

The e-mail on my profile will be fine.