Extract data from a table.

[mdmackillop]

Hi all,
I'm looking to extract data from a large table, 200 columns x 7000 rows to be inserted in a pro-forma spreadsheet. (I can't change its layout) I have some code to do this by doing two "Finds" to get the Data row number and the Ref column number and using these to return the result. Range("G10") = Sheets("Source").Cells(rw,col). Is there a better solution than this?
Regards
MD

[Ken Puls]

Hi Malcolm,

You could do it via formula by using the following in any of the yellow cells:

=INDEX(Source!$A$1:$I$14,MATCH($A$1,Source!$A$1:$A$14,FALSE),MATCH(E10,Sour ce!$A$1:$I$1,FALSE))

Since both Index/Match functions are available in VBA, you could go that route. (Either applying it to the cell, or reading placing the result in the same method you used above.) I have no idea if it would impact the speed though.

[johnske]

Hi Malcolm,

Don't know whether it'd be faster, but something along these lines maybe?Sub CrossReference()
With Sheets("Source")
ActiveSheet.Range("G10") = .Cells(WorksheetFunction.Match("Data4", .[A1:A20], 0), _
WorksheetFunction.Match("01A", .[A1:I1], 0))
End With
End SubRegards,
John :)

[mdmackillop]

Thanks,:thumb
I'll give them both a try.
Regards
Malcolm

[johnske]

If it's faster than the double find, I've also got a function you might have a use for...Sub TryThis()
'modify and put into a loop (or whatever suits your needs)
Range("G10") = CrossReference("01A", "Data4", "Source")
End Sub

Public Function CrossReference(RowHeading As String, ColumnHeading As String, _
LookAtSheet As String, Optional LookInRow As Long, _
Optional LookInCol As Long) As String

If LookInRow = Empty Then LookInRow = 1
If LookInCol = Empty Then LookInCol = 1

On Error GoTo NotFound
With Sheets(LookAtSheet)
CrossReference = .Cells(WorksheetFunction.Match(ColumnHeading, .Columns(LookInCol), 0), _
WorksheetFunction.Match(RowHeading, .Rows(LookInRow), 0))
End With
Exit Function

NotFound:
MsgBox "Nothing found..."
End Function :)