Importing Specific Rows from a text file into Excel

[rstroughair]

I have a spreadsheet into which I import certain rows from a text file
and then parse to retrieve the information I need on a daily basis.
However, each time a new data type is introduced I need to amend the
code to include it. However, I believe this can be programmed quite
easily to avoid the need to change the VBA code for future data but am
struggling a little to do it!

Basically, I have a list of Pool IDs in Column A and I import the data
I need into Column B, then parse it. What I want to do is rewrite my
code such that the code reads the values in Cell A1 down to the last
cell in column A, then imports the relevant rows from my text file into
Column B1 downwards. That way all the user needs to do is to add a new
Pool ID to column A to include the data in the download.

Values in column A will be in the format:

P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
P11

and the rows to be imported from the text file can be calculated using
the formulas:

(12 x P) - 9 and (12 x P) - 3, where P is the number of the Pool ID
(that is the number following the "P" in the column list above).

Thus in the example above, rows 3, 9, 15, 21, 27, 33, .... , 123 and
129 need to be imported from the text file.

Can anyone please give me an example on how I might go about
programming such code?

Many Thanks in advance,

Richard

 

[Tom Ogilvy]

Option Explicit
Sub ABC()
Dim rng As Range, cell As Range
Dim rng1 As Range
Dim rw As Long, l As Long
Dim sNum As String, v As Variant
Set rng = Range("A1", Cells(1, 1).End(xlDown))
rw = 1
Range("B1").Value = "Header1"
For Each cell In rng
sNum = Right(cell, Len(cell) - 1)
l = CLng(sNum)
rw = rw + 1
Cells(rw, 2).Value = 12 * l - 9
rw = rw + 1
Cells(rw, 2).Value = 13 * l - 3
Next
Set rng1 = Range("B1", Cells(1, 2).End(xlDown))
rng1.Sort Key1:=Range("B1"), Header:=True
rng1.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), Unique:=True
Set rng = Range("C2", Cells(2, 3).End(xlDown))

v = rng.Value
v = Application.Transpose(v)
rng1.Resize(, 2).ClearContents
End Sub

v will contain a 1 dimensional array of your rows. (lower bound of 1)