[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Formatting help

keri

12/14/2006 3:11:00 PM

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?
5 Answers

Charles Chickering

12/14/2006 5:19:00 PM

0

Keri, try something along these lines:
Sub FindP()
Dim rSearch As Range
Dim rFirst As Range
'Change "B:B" to the column you wish to search
Set rSearch = Range("B:B").Find(What:="P",LookAt:=xlWhole)
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
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"keri" wrote:

> 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?
>
>

keri

12/14/2006 6:05:00 PM

0

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.

keri

12/14/2006 6:31:00 PM

0

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!

keri

12/14/2006 6:36:00 PM

0

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?

keri

12/15/2006 2:16:00 PM

0

This is working just perfectly. Thankyou for your invaluable help.
Charles Chickering wrote:
> Keri, if this does not work please email the workbook to chick65stang@yahoo.com
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "Charles Chickering" wrote:
>
> > Perhaps we need to specify the Search direction in the first find statement:
> > Set rSearch = Sheets("cardata" & k).Range("B:B"). _
> > Find(What:="P", LookIn:=xlFormulas, _
> > LookAt:=xlPart, SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, MatchCase:=True)
> > --
> > Charles Chickering
> >
> > "A good example is twice the value of good advice."
> >
> >
> > "keri" wrote:
> >
> > > 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!
> > >
> > >