Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing format of cells
Hello! I have have a macro that seaches several spreadsheets for certain
cells. The problem is that some of the spreasheets has cells with strange formatting e.g. some customized formats. If a cell that I am searching for has one of these formats it cannot be found. The cells are identfied by their content that is unique in the sense that only one cell on a certain spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of the cell contents e.g. "FX POS" but they are not to be found. I search like this: Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues, LookAt:=xlWhole) This works except when the cells that I am looking for has strange formatting. I tried recording a macro that changes the formatting of a spreadsheet and applied it to my macro and it works. However some cells on the spreadsheet has a ceratin formatting e.g. "Date" and these are destroyed when setting a new format (I used "General"). Therefore I want to search the spreadsheet for cells that have "strange formatting" and if they have that I want to change the cells formatting to general. I tried writing code for this but without success: Sheets("FX").Select Cells.Select If Selection.NumberFormat = "Selection.NumberFormat = "[$-41D]dd/mmm;@" Then Selection.Format = "General" End If This does not work (I am not surprised myself). How do you write the code? Please I really need help with this! Thank you! Selection.NumberFormat = "General" Selection.NumberFormat = "[$-41D]dd/mmm;@" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing format of cells
Hi Victor,
This works except when the cells that I am looking for has strange formatting. What is the strange formatting? Give an eample of cell content and formatting that is not found by your search. --- Regards, Norman "Viktor Ygdorff" wrote in message ... Hello! I have have a macro that seaches several spreadsheets for certain cells. The problem is that some of the spreasheets has cells with strange formatting e.g. some customized formats. If a cell that I am searching for has one of these formats it cannot be found. The cells are identfied by their content that is unique in the sense that only one cell on a certain spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of the cell contents e.g. "FX POS" but they are not to be found. I search like this: Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues, LookAt:=xlWhole) This works except when the cells that I am looking for has strange formatting. I tried recording a macro that changes the formatting of a spreadsheet and applied it to my macro and it works. However some cells on the spreadsheet has a ceratin formatting e.g. "Date" and these are destroyed when setting a new format (I used "General"). Therefore I want to search the spreadsheet for cells that have "strange formatting" and if they have that I want to change the cells formatting to general. I tried writing code for this but without success: Sheets("FX").Select Cells.Select If Selection.NumberFormat = "Selection.NumberFormat = "[$-41D]dd/mmm;@" Then Selection.Format = "General" End If This does not work (I am not surprised myself). How do you write the code? Please I really need help with this! Thank you! Selection.NumberFormat = "General" Selection.NumberFormat = "[$-41D]dd/mmm;@" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing format of cells
Hello Norman! Here is an exaple of a formatting that disables my search:
Selection.NumberFormat = "[$-41D]dd/mmm;@" Now the problem is that I have not done the indata and it keeps coming new indata and someone else (poor soul) is supposed to use my macro without having to be bothered by changing formats etc. "Norman Jones" skrev: Hi Victor, This works except when the cells that I am looking for has strange formatting. What is the strange formatting? Give an eample of cell content and formatting that is not found by your search. --- Regards, Norman "Viktor Ygdorff" wrote in message ... Hello! I have have a macro that seaches several spreadsheets for certain cells. The problem is that some of the spreasheets has cells with strange formatting e.g. some customized formats. If a cell that I am searching for has one of these formats it cannot be found. The cells are identfied by their content that is unique in the sense that only one cell on a certain spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of the cell contents e.g. "FX POS" but they are not to be found. I search like this: Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues, LookAt:=xlWhole) This works except when the cells that I am looking for has strange formatting. I tried recording a macro that changes the formatting of a spreadsheet and applied it to my macro and it works. However some cells on the spreadsheet has a ceratin formatting e.g. "Date" and these are destroyed when setting a new format (I used "General"). Therefore I want to search the spreadsheet for cells that have "strange formatting" and if they have that I want to change the cells formatting to general. I tried writing code for this but without success: Sheets("FX").Select Cells.Select If Selection.NumberFormat = "Selection.NumberFormat = "[$-41D]dd/mmm;@" Then Selection.Format = "General" End If This does not work (I am not surprised myself). How do you write the code? Please I really need help with this! Thank you! Selection.NumberFormat = "General" Selection.NumberFormat = "[$-41D]dd/mmm;@" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing format of cells
Hello again Norman! Sorry I forgot to give an example of cell content. I have
found one instance having the same formatting as in my previous reply i.e. Selection.NumberFormat = "[$-41D]dd/mmm;@" and the cell content that I am searching for is "Guld (Ränta)". Thank you very much for your help Norman! "Norman Jones" skrev: Hi Victor, This works except when the cells that I am looking for has strange formatting. What is the strange formatting? Give an eample of cell content and formatting that is not found by your search. --- Regards, Norman "Viktor Ygdorff" wrote in message ... Hello! I have have a macro that seaches several spreadsheets for certain cells. The problem is that some of the spreasheets has cells with strange formatting e.g. some customized formats. If a cell that I am searching for has one of these formats it cannot be found. The cells are identfied by their content that is unique in the sense that only one cell on a certain spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of the cell contents e.g. "FX POS" but they are not to be found. I search like this: Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues, LookAt:=xlWhole) This works except when the cells that I am looking for has strange formatting. I tried recording a macro that changes the formatting of a spreadsheet and applied it to my macro and it works. However some cells on the spreadsheet has a ceratin formatting e.g. "Date" and these are destroyed when setting a new format (I used "General"). Therefore I want to search the spreadsheet for cells that have "strange formatting" and if they have that I want to change the cells formatting to general. I tried writing code for this but without success: Sheets("FX").Select Cells.Select If Selection.NumberFormat = "Selection.NumberFormat = "[$-41D]dd/mmm;@" Then Selection.Format = "General" End If This does not work (I am not surprised myself). How do you write the code? Please I really need help with this! Thank you! Selection.NumberFormat = "General" Selection.NumberFormat = "[$-41D]dd/mmm;@" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking cells with changing format | Excel Discussion (Misc queries) | |||
Format of Cells Changing on thier own | Excel Discussion (Misc queries) | |||
Format of cells Changing on thier own | Excel Worksheet Functions | |||
Changing format of cells | Excel Programming | |||
Changing Cells format using VBA | Excel Programming |