Define a data range of imported data

[wmbb]

I've a textfile containing the the following data:
The content is varying but the section are always the same...
**********************************************************

(Measure data)
Name=Customer
LIMS nr=0.000000
Acc. Voltage (kV)=0.000000
Sample Current (uA)=0.000000
Crystal=PET
Element=Mo
Start (A)=1.000000
Stop (A)=0.000000

(settings)
mean over#=50
Sample Rate=100.000000
physical channels=Dev1/ai1
Input range=1.000000
Visible records=600

(Data)
0.00E+00 1.05E+00
5.00E-01 1.05E+00
1.00E+00 1.05E+00
1.50E+00 1.05E+00
2.00E+00 1.05E+00
2.50E+00 1.05E+00
...
...
[\color]
*********************************************************

After importing in excel I want to define (name) the range with datapoints printed below "(data)".
How do I define the datarange if the position of the label [data] varies for each textfile.

The algoritme will be something like
Find [data]
Find last datapoint-couple
define range ("(data)"+ 1 row) to (last datapoint) as "datapoints"

 

[Chance1234]

Does the column change ? if not you could search on the column

something like

Function FindLastInputRow()
Dim intr As Integer

intr = 10000

Do Until intr = 2

    If Sheet1.Cells(intr, 3) <> "(data)" Then
        FindLastInputRow = intr
        Exit Function
    End If
    
        intr = intr - 1
Loop
End Function

Sub Test 
Dim intLast as int
intLast = FindLastInputRow

With ActiveWorkbook
        .Names.Add Name:="ta_area", RefersToR1C1:= _
        "=Input!R3C3:R" & intLast & "C3"
end with 

end sub

Chance,

F, G + 2MSTG

 

[SkipVought]

Hi,

"After importing in excel..."

If you Import using Data > Import External Data...[/b] you already have a range name that defines the imported range.

with YourSheetObject.QueryTables("YourQTRangeName").resultrange
  lFirstRow = .Row
  lLastRow = lFirstRow + .Rows.Count - 1
end with

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[wmbb]

I don't think the option of SkipVought is usable because I don't want the whole imported data to be named but just de two columns under "(data)".

The option of Chance looks useful but I don't think this is going to work.
Can you please explain what the function does ?

What I really need is the cell-reference of the cell containing "(data)" (for example A15) and the last row with datapoint (for example B200).

Subsequently I want to name the range, in this example (A15+1) A16:B200 to "measure_data"

 

[Chance1234]

wmmb,

The function starts at cell 2 in column 3 on Sheet1 and moves down the column until it finds a cell with the value "(data)".

when it does it stops and returns the row value



Chance,

F, G + 1MSTG

 

[Gavona]

Will this do the job?

Sub Macro1()
Cells.Find(What:="(Data)", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).CurrentRegion.Name = "MyData"
End Sub

Gavin

 

[SkipVought]

1) are you using Data > Import... to IMPORT? If you do, it does not matter that, "I don't want the whole imported data to be named but just de two columns under '(data)'." It's ALREADY named.

2) if not then substitute ANY cell in your contiguous data, for instance A2...

with YourSheetObject.[A2].currentregion
  lFirstRow = .Row
  lLastRow = lFirstRow + .Rows.Count - 1
end with

Either way, you have the FIRST and LAST row of the range. For there your could, for instance...

for lRow = lFirstRow to lLastRow
   msgbox YourSheetObject.cells(lrow, "A").value
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[wmbb]

This is what I produced from your suggestions:

Sub test()
Dim rb As Integer
Dim re As Integer
rb = 0
re = 0
For rwIndex = 1 To 100
With Worksheets("Sheet1").Cells(rwIndex, 1)
If .Value = "[Data]" Then rb = (rwIndex + 1)
End With
Next rwIndex
re = rb
Do Until Sheet1.Cells(re, 1).Value = ""
re = re + 1
Loop
re = re - 1
End Sub


But what is de code to name the range (rb,1)re,2) to "datapoints

 

[SkipVought]

1) Turn on your macro recorder and RECORD naming a range -- pick a range; ANY range.

2) try to apply what you know about your range to the code to customize.

If you have problems, post back with your recorded/midified code.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Chance1234]

wmb, look at the procedur test i posted along with the function

Chance,

F, G + 1MSTG

 

[wmbb]

Finally it works !!

Thanks for all your help.
The code I use is:


Sub test()
Dim rb As Integer
Dim re As Integer

rb = 0
re = 0

For rwIndex = 1 To 100
With Worksheets("Sheet1").Cells(rwIndex, 1)
If .Value = "[Data]" Then rb = (rwIndex + 1)
End With
Next rwIndex

re = rb

Do Until Sheet1.Cells(re, 1).Value = ""
re = re + 1
Loop

re = re - 1

ActiveWorkbook.Names.Add Name:="datapoints", RefersToR1C1:= _
"=Sheet1!R" & rb & "C1:R" & re & "C2"

End Sub

 

[SkipVought]

Good for you! That's the way you do it!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}