Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 for a whole bunch of people...) Any ideas...? Me *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 -- HTH... Jim Thomlinson "Darin Kramer" wrote: 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 for a whole bunch of people...) Any ideas...? Me *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 wrote: 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 for a whole bunch of people...) Any ideas...? Me *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 wrote: 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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
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 wrote: 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 *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
Thanks - where should I send it too....? :) *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
F2 Cells in a column
The e-mail on my profile will be fine.
Darin Kramer wrote: Thanks - where should I send it too....? :) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Copying a column of single cells into a column of merged cells | Excel Discussion (Misc queries) | |||
macro to transpose cells in Column B based on unique values in Column A | Excel Programming |