Insert 19 cells "Shift to the right" if cell contains "-"
 

I have been looking for a sub that can find a cell in column A that ha
a "-" in it the actuall cell will contain "P-9999999" the only thin
consistant is the "-". When it finds the cell I need it to shift 1
cells to the Right.
Thanks for the help or any ideas,
Bo

--
robertjtucke
-----------------------------------------------------------------------
robertjtucker's Profile: http://www.excelforum.com/member.php...fo&userid=2180
View this thread: http://www.excelforum.com/showthread.php?threadid=38933

Insert 19 cells "Shift to the right" if cell contains "-"
 

I have this and it works, it inserts cells until it gets out of the
range but takes way to long. One at a time. Any one have any ideas on
how to get it insert 19 cells at a time or outside the range.


Sub Macro64()

With Worksheets("Imported").Range("imp2")
Set c = .Find("-", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Insert Shift:=xlToRight

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


--
robertjtucker
------------------------------------------------------------------------
robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805
View this thread: http://www.excelforum.com/showthread...hreadid=389337

Insert 19 cells "Shift to the right" if cell contains "-"
 

Hi,

You can use this. Remember, the key here is that once the range is found,
Excel will not find the next range unless you swap the text in the first
range for something else. Then, at the end, I swap the text back.

....Bill

Sub hhh()
Dim LoopEnd As Boolean
Dim SomeRange As Range
Const Txt = "SomeUnlikelyText"
Do While Not LoopEnd
Set SomeRange = Cells.Find(What:="-")
If SomeRange Is Nothing Then
LoopEnd = True
Else
SomeRange.Value = Application.Substitute(SomeRange.Value, "-", Txt)
SomeRange.Cut SomeRange.Offset(0, 19)
End If
Loop

Cells.Replace What:=Txt, Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub


"robertjtucker"
wrote in message
news:robertjtucker.1sko2p_1122037851.4973@excelfor um-nospam.com...

I have been looking for a sub that can find a cell in column A that has
a "-" in it the actuall cell will contain "P-9999999" the only thing
consistant is the "-". When it finds the cell I need it to shift 19
cells to the Right.
Thanks for the help or any ideas,
Bob


--
robertjtucker
------------------------------------------------------------------------
robertjtucker's Profile:
http://www.excelforum.com/member.php...o&userid=21805
View this thread: http://www.excelforum.com/showthread...hreadid=389337

Insert 19 cells "Shift to the right" if cell contains "-"
 

Thanks for that, but I need all the cells next to the cells containinig
the "-" to be moved also thats why I wanted to insert so I could shift
everything from A:S to start in T:AL
Thanks again,
Bob


--
robertjtucker
------------------------------------------------------------------------
robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805
View this thread: http://www.excelforum.com/showthread...hreadid=389337

Insert 19 cells "Shift to the right" if cell contains "-"
 

Minor (very) change:

Sub hhh()
Dim LoopEnd As Boolean
Dim SomeRange As Range
Const Txt = "SomeUnlikelyText"
Do While Not LoopEnd
Set SomeRange = Cells.Find(What:="-")
If SomeRange Is Nothing Then
LoopEnd = True
Else
SomeRange.Value = Application.Substitute(SomeRange.Value, "-", Txt)
ActiveSheet.Range(SomeRange, SomeRange.Offset(0, 18)).Cut
SomeRange.Offset(0, 19)
End If
Loop

Cells.Replace What:=Txt, Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

"robertjtucker"
wrote in message
news:robertjtucker.1skz6r_1122052018.8388@excelfor um-nospam.com...

Thanks for that, but I need all the cells next to the cells containinig
the "-" to be moved also thats why I wanted to insert so I could shift
everything from A:S to start in T:AL
Thanks again,
Bob


--
robertjtucker
------------------------------------------------------------------------
robertjtucker's Profile:
http://www.excelforum.com/member.php...o&userid=21805
View this thread: http://www.excelforum.com/showthread...hreadid=389337

Insert 19 cells "Shift to the right" if cell contains "-"
 

Thanks Bill it worked real well.
Appreciate the help,
Bo

--
robertjtucke
-----------------------------------------------------------------------
robertjtucker's Profile: http://www.excelforum.com/member.php...fo&userid=2180
View this thread: http://www.excelforum.com/showthread.php?threadid=38933