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

Find & Delete columns to the left stopping at column B
 

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

<Aria wrote in message ...
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 ***

Find & Delete columns to the left stopping at column B
 

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

Find & Delete columns to the left stopping at column B
 

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

<Aria wrote in message ...
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 ***

Find & Delete columns to the left stopping at column B
 

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

Find & Delete columns to the left stopping at column B
 

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

<Aria wrote in message ...
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 ***

Find & Delete columns to the left stopping at column B
 

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

Find & Delete columns to the left stopping at column B
 

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

<Aria wrote in message ...
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 ***

Find & Delete columns to the left stopping at column B
 

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

Find & Delete columns to the left stopping at column B
 

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

<Aria wrote in message ...
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 ***

Find & Delete columns to the left stopping at column B
 

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

Aria :)

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

Find & Delete columns to the left stopping at column B
 

glad you got it going

--
Don Guillett
SalesAid Software

<Aria wrote in message ...
Don,
All very good ideas. It works. Thanks so much.
Have a good weekend.

Aria :)

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