Excel - Return row number to VBA from search

[ceecld]

I have a text file which is imported into Excel using a macro which formats the column widths etc. At the moment the user manual types in the number of samples so that when plots are created from the table the macro knows how far down the table to take data from. e.g. if the user says there are 1000 samples then a graph is plot with the data in cells A2:C1001.

Im now thinking that it might be better if excel can determine the end of the data imported from the text file. The text file itself is split into two parts the upper part is columns of waveform data, then the lower part is a statistical overview of the waveforms.

What i would like to do is from a VBA macro search down column B of worksheet "mrunout.out" looking for the value 360 then return the row at which this value is detected.

I have been playing about with the functions 'Vlookup' & 'Cell' but i cant seem to get it to work.

Any help on this matter would be much appreciated

 

[Loomah]

Hi
Here are two possible ways to get what you're after here

Sub lime()
Dim lRow As Long
Dim myRow As Long
lRow = [b65536].End(xlUp).Row

'FIRST METHOD USING FIND
myRow = Range("B1:B" & lRow).Find(360, , , xlWhole, xlByRows, xlNext).Row
MsgBox myRow

'SECOND METHOD USING MATCH FUNCTION
myRow = WorksheetFunction.Match(360, Range("B1:B" & lRow), 0)
MsgBox myRow
End Sub

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[ceecld]

Thanks! i had just managed to get it to work with the Vlookup function but thats causing more problems than its worth.

Thanks again