Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
change "true" and "false" to "availble" and "out of stock" inthestands Excel Worksheet Functions 2 July 19th 07 07:05 PM
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F daves Excel Discussion (Misc queries) 3 April 24th 07 04:52 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
Insert "-" in text "1234567890" to have a output like this"123-456-7890" Alwyn Excel Discussion (Misc queries) 3 October 25th 05 11:36 PM


All times are GMT +1. The time now is 04:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"