Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Extracting Values and inserting Rows/Values
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Extracting Values and inserting Rows/Values
Anyone know the best way to do this with an Excel Macro.
I have about 35 Files I need to Import Parse.
Here is the raw data Before Import:

ADD-DPLN:1,,,STN,N,,,,,;
ADD-DPLN:200000&&200005,,,STN,N,,,,,;
ADD-DPLN:200007,,,HUNT,N,,,,,;
ADD-DPLN:200009&&200125,,,STN,N,,,,,;

After I Import:
Sub Dpln_Import_Macro()
ChDir "C:\"
Workbooks.OpenText Filename:="C:\Regen Dplan.txt", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=True, _
Space:=False, Other:=True, OtherChar:=":",
FieldInfo:=Array(Array(1, 9), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6,
2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2)),
TrailingMinusNumbers:=True
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A1:E4").Select
Selection.Copy
Application.CutCopyMode = False
End Sub

This is what I need; to seperate out and Insert the Missing Numbers/
Rows between the "&&" signs, (I.e. the following is output: 200000,
200001, 200002, 200003, 200004, 200005)

1 STN N
200000&&200005 STN N
200007 HUNT N
200009&200125 STN N

I need to Expand the Numbers with an "&&". For example

200000&&200005 STN N

Should end up looking like:

1 STN N
200000 HUNT N
200001 HUNT N
200002 HUNT N
200003 HUNT N
200004 HUNT N
200005 HUNT N
200007 HUNT N
etc...

Any one have any suggestions for accomplishing this?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Extracting Values and inserting Rows/Values
This should work. Your sample data in one location had a single aphersand
instead of two.


Sub dup_rows()

RowCount = 1
Do While Range("A" & RowCount) < ""
If InStr(Range("A" & RowCount), "&") 0 Then
Numbers = Range("A" & RowCount)
FirstNum = Val(Trim(Left(Numbers, InStr(Numbers, "&") - 1)))
LastNum = Val(Trim(Mid(Numbers, InStr(Numbers, "&") + 2)))
Set CopyRange = Range("B" & RowCount & ":C" & RowCount)
Range("A" & RowCount) = FirstNum
For NumCount = (FirstNum + 1) To LastNum
Rows(RowCount + 1).Insert
RowCount = RowCount + 1
Range("A" & RowCount) = NumCount
CopyRange.Copy Destination:=Range("B" & RowCount)
Next NumCount
End If
RowCount = RowCount + 1
Loop

End Sub


"VexedFist" wrote:

Anyone know the best way to do this with an Excel Macro.
I have about 35 Files I need to Import Parse.
Here is the raw data Before Import:

ADD-DPLN:1,,,STN,N,,,,,;
ADD-DPLN:200000&&200005,,,STN,N,,,,,;
ADD-DPLN:200007,,,HUNT,N,,,,,;
ADD-DPLN:200009&&200125,,,STN,N,,,,,;

After I Import:
Sub Dpln_Import_Macro()
ChDir "C:\"
Workbooks.OpenText Filename:="C:\Regen Dplan.txt", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=True, _
Space:=False, Other:=True, OtherChar:=":",
FieldInfo:=Array(Array(1, 9), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6,
2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2)),
TrailingMinusNumbers:=True
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A1:E4").Select
Selection.Copy
Application.CutCopyMode = False
End Sub

This is what I need; to seperate out and Insert the Missing Numbers/
Rows between the "&&" signs, (I.e. the following is output: 200000,
200001, 200002, 200003, 200004, 200005)

1 STN N
200000&&200005 STN N
200007 HUNT N
200009&200125 STN N

I need to Expand the Numbers with an "&&". For example

200000&&200005 STN N

Should end up looking like:

1 STN N
200000 HUNT N
200001 HUNT N
200002 HUNT N
200003 HUNT N
200004 HUNT N
200005 HUNT N
200007 HUNT N
etc...

Any one have any suggestions for accomplishing this?

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Extracting Values and inserting Rows/Values
On Apr 18, 2:20*pm, Joel wrote:
This should work. *Your sample data in one location had a single aphersand
instead of two.

Sub dup_rows()

RowCount = 1
Do While Range("A" & RowCount) < ""
* *If InStr(Range("A" & RowCount), "&") 0 Then
* * *Numbers = Range("A" & RowCount)
* * *FirstNum = Val(Trim(Left(Numbers, InStr(Numbers, "&") - 1)))
* * *LastNum = Val(Trim(Mid(Numbers, InStr(Numbers, "&") + 2)))
* * *Set CopyRange = Range("B" & RowCount & ":C" & RowCount)
* * *Range("A" & RowCount) = FirstNum
* * *For NumCount = (FirstNum + 1) To LastNum
* * * * Rows(RowCount + 1).Insert
* * * * RowCount = RowCount + 1
* * * * Range("A" & RowCount) = NumCount
* * * * CopyRange.Copy Destination:=Range("B" & RowCount)
* * *Next NumCount
* *End If
* *RowCount = RowCount + 1
Loop

End Sub



"VexedFist" wrote:
Anyone know the best way to do this with an Excel Macro.
I have about 35 Files I need to Import Parse.
Here is the raw data Before Import:

ADD-DPLN:1,,,STN,N,,,,,;
ADD-DPLN:200000&&200005,,,STN,N,,,,,;
ADD-DPLN:200007,,,HUNT,N,,,,,;
ADD-DPLN:200009&&200125,,,STN,N,,,,,;

After I Import:
Sub Dpln_Import_Macro()
* * ChDir "C:\"
* * Workbooks.OpenText Filename:="C:\Regen Dplan.txt", Origin:=437,
StartRow _
* * * * :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
* * * * ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=True, _
* * * * Space:=False, Other:=True, OtherChar:=":",
FieldInfo:=Array(Array(1, 9), _
* * * * Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6,
2), Array(7, 2), Array(8, 2), _
* * * * Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2)),
TrailingMinusNumbers:=True
* * Columns("A:E").Select
* * Columns("A:E").EntireColumn.AutoFit
* * Range("A1:E4").Select
* * Selection.Copy
* * Application.CutCopyMode = False
End Sub

This is what I need; to seperate out and Insert the Missing Numbers/
Rows between the "&&" signs, (I.e. the following is output: *200000,
200001, 200002, 200003, 200004, 200005)

1 * * * * * * * * *STN * * N
200000&&200005 * * * * * * STN * * N
200007 * * * * * * * * * * HUNT * *N
200009&200125 * * * * *STN * * N

I need to Expand the Numbers with an "&&". *For example

200000&&200005 * * * * * * STN * * N

Should end up looking like:

1 * * * * * * * * *STN * * N
200000 * * * * * * * * * * HUNT * *N
200001 * * * * * * * * * * HUNT * *N
200002 * * * * * * * * * * HUNT * *N
200003 * * * * * * * * * * HUNT * *N
200004 * * * * * * * * * * HUNT * *N
200005 * * * * * * * * * * HUNT * *N
200007 * * * * * * * * * * HUNT * *N
etc...

Any one have any suggestions for accomplishing this?- Hide quoted text -

- Show quoted text -

JOEL,

This is not copying the additonal informaiton in the other Cells
(I.e., STN N, or HUNT N).
IS there an easy fix for this?

Thanks for all the assistance on this.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Extracting Values and inserting Rows/Values
My code expects the data to be in columns A - C. The data apparently in
column A is working correctly. the following line of code is used to select
columns B and C. change this line as necessary

Set CopyRange = Range("B" & RowCount & ":C" & RowCount)

"VexedFist" wrote:

On Apr 18, 2:20 pm, Joel wrote:
This should work. Your sample data in one location had a single aphersand
instead of two.

Sub dup_rows()

RowCount = 1
Do While Range("A" & RowCount) < ""
If InStr(Range("A" & RowCount), "&") 0 Then
Numbers = Range("A" & RowCount)
FirstNum = Val(Trim(Left(Numbers, InStr(Numbers, "&") - 1)))
LastNum = Val(Trim(Mid(Numbers, InStr(Numbers, "&") + 2)))
Set CopyRange = Range("B" & RowCount & ":C" & RowCount)
Range("A" & RowCount) = FirstNum
For NumCount = (FirstNum + 1) To LastNum
Rows(RowCount + 1).Insert
RowCount = RowCount + 1
Range("A" & RowCount) = NumCount
CopyRange.Copy Destination:=Range("B" & RowCount)
Next NumCount
End If
RowCount = RowCount + 1
Loop

End Sub



"VexedFist" wrote:
Anyone know the best way to do this with an Excel Macro.
I have about 35 Files I need to Import Parse.
Here is the raw data Before Import:

ADD-DPLN:1,,,STN,N,,,,,;
ADD-DPLN:200000&&200005,,,STN,N,,,,,;
ADD-DPLN:200007,,,HUNT,N,,,,,;
ADD-DPLN:200009&&200125,,,STN,N,,,,,;

After I Import:
Sub Dpln_Import_Macro()
ChDir "C:\"
Workbooks.OpenText Filename:="C:\Regen Dplan.txt", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=True, _
Space:=False, Other:=True, OtherChar:=":",
FieldInfo:=Array(Array(1, 9), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6,
2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2)),
TrailingMinusNumbers:=True
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A1:E4").Select
Selection.Copy
Application.CutCopyMode = False
End Sub

This is what I need; to seperate out and Insert the Missing Numbers/
Rows between the "&&" signs, (I.e. the following is output: 200000,
200001, 200002, 200003, 200004, 200005)

1 STN N
200000&&200005 STN N
200007 HUNT N
200009&200125 STN N

I need to Expand the Numbers with an "&&". For example

200000&&200005 STN N

Should end up looking like:

1 STN N
200000 HUNT N
200001 HUNT N
200002 HUNT N
200003 HUNT N
200004 HUNT N
200005 HUNT N
200007 HUNT N
etc...

Any one have any suggestions for accomplishing this?- Hide quoted text -

- Show quoted text -

JOEL,

This is not copying the additonal informaiton in the other Cells
(I.e., STN N, or HUNT N).
IS there an easy fix for this?

Thanks for all the assistance on this.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Match Values in Rows with Partial Values in Columns ryguy7272 Excel Worksheet Functions 3 August 8th 07 05:14 PM
Extracting rows from a spreadsheet using values from another shee Alex 46 Excel Discussion (Misc queries) 1 January 31st 07 07:27 PM
Inserting rows without changing formula values Hook''em 2006 Excel Worksheet Functions 5 December 27th 06 09:45 PM
counting rows with same values for multiple values Jon Viehe New Users to Excel 4 September 1st 05 03:49 PM
Inserting/Deleting Rows when values are added /cleared teresa Excel Programming 1 January 23rd 05 03:03 PM


All times are GMT +1. The time now is 03:00 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"

 

Contact Us ExcelBanter forum home Privacy Statement