Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting help
Hi again,
My code is to format a PDF file converted to Excel, therefore needs to act upon diferent inconsistencies. I now have a problem that in some conversions my columns are as below No Time 1 13.30 2P 14.00 3 13.20 4 24.30 5P 09.30 With the P appearing in the same column as the number in a random way. However sometimes this imports like this; No Time 1 13.30 2 P 14.00 3 13.20 4 24.30 5 P 09.30 With the P in a seperate column. (The column header from column A is merged into the header of the P column). Currently I have to scroll through a lot of data to remove these P columns and insert the P next to the number. (I have code that moves all of this data and if causes huge errors and problems if this P is in a seperate column). Is there a way I can search for this happening in a sheet and if it has happened to add the P to the number in the column to the left, then finally delete all of there P columns? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting help
Charles,
Thanks for the answer. This is so close but I guess the problem I'm having with the code is from something I did not explain to you so i'll try and clarify what is on my sheet in the first place. Tha data is on one sheet in up to 26 "tables" (I use tables as the best way to describe it but it is actually just a range of cells surrounded by a border). There is normally some rows of text in between each of these groups of data, and the groups are arranged down the sheet. Each group has it's own column headers as described above. When I tested your code the first 2 sets of data did not have the extra column with the P in it so it did nothing with these 2 groups. The third group did have the extra column. The code did exactly what it was supposed to in this group (eg moved the P into the first column and then deleted the "P" column). However it also deleted all of the cells below this group in this column. So where the fourth group of data columns started as No Time KPH they ended up as No KPH Where the 5th group of columns started as No P Time KPH They ended up as No Time KPH (but obviously the code hadn't moved the P into the first column in this case) I think the code needs to recognise each group of data seperately, then test if there is a P in column 2, then if there is to move the P to column 1 and delete the cells in column 2 in that group only! I hope all of that makes some sense to you. Perhaps the ranges could be selected with activerange? Your code is much more sophisticated than I expected - i was going to have the code select the first "table" and ask the user if the second column was a P column, then act depending on the answer! Thanks again for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting help
I have discovered two more things.
I can run this code AFTER I have put each of these "tables" into another sheet, which solves the problem I was wittering on about above! However the code is only finding the first P and moving it into the first column. I need it to search all the cells below this column and move the P across into the first column if it exists. Secondly I have tried to get this code to look through my sheets as below but it doesn't like it (suprise suprise seeing as I wrote it!). (Answer is already defined as a global variable) Dim k As Integer Dim rSearch As Range Dim rFirst As Range For k = 1 To answer Set rSearch = Sheets("cardata" & k).Range("B:B").Find(what:="P", LookAt:=xlPart) If rSearch Is Nothing Then Exit Sub Set rFirst = rSearch Do rSearch.Offset(, -1) = rSearch.Offset(, -1) & "P" rSearch.FindNext After:=rSearch Loop Until rSearch.Address = rFirst.Address rSearch.EntireColumn.Delete Next k End Sub Thanks experts! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting help
The problem is the do until statement - the loop is not happening at
all therefore it is not finding the next P. Can I change the do until statement to a range, or to the end of column? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting help
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
Decimal Formatting in Windows English vs European Formatting | Excel Programming |