Find & Delete columns to the left stopping at column B

Hi, please me with this task. I need to Find "Jan" then keep deleting
the columns to the left until "Jan" is situated in column B.

For example, "Jan" is in column E. Find "Jan" then delete columns to
the left and keep deleting until "Jan" is on column B.

Thanks very much,
Aria

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

try this for row 3

Sub findjanandmove()
x = Rows(3).Find("Jan").Column - 1
If x = 1 Then Exit Sub
Range(Cells(3, 2), Cells(3, x)).EntireColumn.Delete
End Sub

--
Don Guillett
SalesAid Software
[email protected]
<Aria> wrote in message news:%[email protected]...
> Hi, please me with this task. I need to Find "Jan" then keep deleting
> the columns to the left until "Jan" is situated in column B.
>
> For example, "Jan" is in column E. Find "Jan" then delete columns to
> the left and keep deleting until "Jan" is on column B.
>
> Thanks very much,
> Aria
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Don,
Very close but I didn't want to be limited to row 3 in case finding
"Jan" is located in other rows. Any way to make the column deletions
regardless of which rows "Jan" is located?

Thanks,
Aria

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

Are we to assume there will be only ONE "Jan"??

Sub findjan()
x = Cells.Find("Jan").Column - 1
If x = 1 Then Exit Sub
Range(Cells(1, 2), Cells(1, x)).EntireColumn.Delete
End Sub

--
Don Guillett
SalesAid Software
[email protected]
<Aria> wrote in message news:[email protected]...
> Hi Don,
> Very close but I didn't want to be limited to row 3 in case finding
> "Jan" is located in other rows. Any way to make the column deletions
> regardless of which rows "Jan" is located?
>
> Thanks,
> Aria
>
> *** Sent via Developersdex http://www.developersdex.com ***

Don,
Yes, I'm assuming that there is only one Jan throughout the document. I
tried putting in a message prior to the deletion but when I run it
again, the same message appears. Any way to stop the message from
appearing, if "Jan" is already situated in column B?


Dim mbox As Button
x = Cells.Find("Jan-*").Column - 1
If x <> 1 Then
mbox = MsgBox(Prompt:="Jan will shift to column B", _
Buttons:=vbOKOnly)
Range(Cells(1, 2), Cells(1, x)).EntireColumn.delete
End If

Thanks a bunch,
Aria

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

Then why not just insert the message and leave the rest alone

Sub findjan1()
x = Cells.Find("Jan*").Column - 1
If x = 1 Then Exit Sub
MsgBox "Jan will shift to column B"
Range(Cells(1, 2), Cells(1, x)).EntireColumn.Delete
End Sub
--
Don Guillett
SalesAid Software
[email protected]
<Aria> wrote in message news:[email protected]...
> Don,
> Yes, I'm assuming that there is only one Jan throughout the document. I
> tried putting in a message prior to the deletion but when I run it
> again, the same message appears. Any way to stop the message from
> appearing, if "Jan" is already situated in column B?
>
>
> Dim mbox As Button
> x = Cells.Find("Jan-*").Column - 1
> If x <> 1 Then
> mbox = MsgBox(Prompt:="Jan will shift to column B", _
> Buttons:=vbOKOnly)
> Range(Cells(1, 2), Cells(1, x)).EntireColumn.delete
> End If
>
> Thanks a bunch,
> Aria
>
> *** Sent via Developersdex http://www.developersdex.com ***

Don,
I missed something on my previous post. Once I've found "Jan", I
changed the name. So when I ran the code again, it shouldn't find "Jan"
anymore; thus no message should appear.

Any way to stop the message from appearing, if "Jan" is not found? Or,
if "Jan" is already in Column B?

Dim mbox As Button
x = Cells.Find("Jan-*").Column - 1
If x <> 1 Then
mbox = MsgBox(Prompt:="Jan will shift to column B", _
Buttons:=vbOKOnly)
Range(Cells(1, 2), Cells(1, x)).EntireColumn.delete
End If

'remaining codes here

Thanks a bunch,
Aria

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

traps the error

Sub findjan1()
On Error GoTo quitit
x = Cells.Find("Jan*").Column - 1
If x = 1 Then Exit Sub
MsgBox "Jan will shift to column B"
Range(Cells(1, 2), Cells(1, x)).EntireColumn.Delete
quitit:
End Sub

--
Don Guillett
SalesAid Software
[email protected]
<Aria> wrote in message news:%[email protected]...
> Don,
> I missed something on my previous post. Once I've found "Jan", I
> changed the name. So when I ran the code again, it shouldn't find "Jan"
> anymore; thus no message should appear.
>
> Any way to stop the message from appearing, if "Jan" is not found? Or,
> if "Jan" is already in Column B?
>
> Dim mbox As Button
> x = Cells.Find("Jan-*").Column - 1
> If x <> 1 Then
> mbox = MsgBox(Prompt:="Jan will shift to column B", _
> Buttons:=vbOKOnly)
> Range(Cells(1, 2), Cells(1, x)).EntireColumn.delete
> End If
>
> 'remaining codes here
>
> Thanks a bunch,
> Aria
>
> *** Sent via Developersdex http://www.developersdex.com ***

Don, I've tried the code on its own and it works. But...when I add the
rest of mine, it errors on me after quitit. It's like the first On
Error GoTo seems to conflict with On Error Resume Next. Any thoughts to
why? I'm trying to find the "Jan-2006" text field, delete the columns
to the left so "Jan-2006" is on column B. Then, I'll search for "Jan-*"
and change it to be in "mmm" format and field in the formulas for the
rest of the year.


On Error GoTo quitit
x = Cells.Find("Jan-*").Column - 1
If x = 1 Then Exit Sub
MsgBox "Jan will shift to column B"
Range(Cells(1, 2), Cells(1, x)).EntireColumn.delete
quitit:

On Error Resume Next
Cells.Find(what:="Jan-*", after:=ActiveCell, LookIn:=xlFormulas,
lookat _
:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext,
MatchCase:= _
False, searchformat:=False).Activate
Cells.Find(what:="Jan", after:=ActiveCell, LookIn:=xlValues, lookat
_
:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext,
MatchCase:= _
True, searchformat:=False).Activate
Selection.NumberFormat = "mmm"
ActiveCell.FormulaR1C1 = "1/1/2006"
ActiveCell.Select
With Selection
.HorizontalAlignment = xlCenter
End With
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 =
"=DATE(YEAR(RC[-1]),MONTH(RC[-1])+2,DAY(0))"
ActiveCell.Select
Selection.NumberFormat = "mmm"
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1:J1").Select
ActiveSheet.Paste

Thanks, Aria

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

If Jan-2006 IS text then this should do it

Sub findjanandmove()
On Error GoTo quitit
Set mo = Cells.Find("Jan*")
mo.Value = Left(mo, 3)
mc = mo.Column - 1
If mc = 1 Then Exit Sub
MsgBox "Jan will shift to column B"
Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Delete
quitit:
End Sub

--
Don Guillett
SalesAid Software
[email protected]
<Aria> wrote in message news:[email protected]...
> Don, I've tried the code on its own and it works. But...when I add the
> rest of mine, it errors on me after quitit. It's like the first On
> Error GoTo seems to conflict with On Error Resume Next. Any thoughts to
> why? I'm trying to find the "Jan-2006" text field, delete the columns
> to the left so "Jan-2006" is on column B. Then, I'll search for "Jan-*"
> and change it to be in "mmm" format and field in the formulas for the
> rest of the year.
>
>
> On Error GoTo quitit
> x = Cells.Find("Jan-*").Column - 1
> If x = 1 Then Exit Sub
> MsgBox "Jan will shift to column B"
> Range(Cells(1, 2), Cells(1, x)).EntireColumn.delete
> quitit:
>
> On Error Resume Next
> Cells.Find(what:="Jan-*", after:=ActiveCell, LookIn:=xlFormulas,
> lookat _
> :=xlPart, searchorder:=xlByRows, searchdirection:=xlNext,
> MatchCase:= _
> False, searchformat:=False).Activate
> Cells.Find(what:="Jan", after:=ActiveCell, LookIn:=xlValues, lookat
> _
> :=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext,
> MatchCase:= _
> True, searchformat:=False).Activate
> Selection.NumberFormat = "mmm"
> ActiveCell.FormulaR1C1 = "1/1/2006"
> ActiveCell.Select
> With Selection
> .HorizontalAlignment = xlCenter
> End With
> ActiveCell.Offset(0, 1).Range("A1").Select
> ActiveCell.FormulaR1C1 =
> "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+2,DAY(0))"
> ActiveCell.Select
> Selection.NumberFormat = "mmm"
> With Selection
> .HorizontalAlignment = xlCenter
> End With
> Selection.Copy
> ActiveCell.Offset(0, 1).Range("A1:J1").Select
> ActiveSheet.Paste
>
> Thanks, Aria
>
> *** Sent via Developersdex http://www.developersdex.com ***

Don,
All very good ideas. It works. Thanks so much.
Have a good weekend.

Aria

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

glad you got it going

--
Don Guillett
SalesAid Software
[email protected]
<Aria> wrote in message news:[email protected]...
> Don,
> All very good ideas. It works. Thanks so much.
> Have a good weekend.
>
> Aria
>
> *** Sent via Developersdex http://www.developersdex.com ***