Visual Basic help?

[Django x2]

Hi guys,

I need to write a macro which will look for values from an array (say 100 different words) in a large dataset. Once it finds the values, it will then need to copy the row and paste it into a new sheet.

I'm a little stumped, any tips?

 

[jonifen]

If I've understood right (I'm feeling quite tired at the min ), you could start with going through the array and search through the dataset to find the current word.

Something like... (code is untested, I don't have access to Excel at the min)

Dim oApp As Excel.Application
Dim oWB As Excel.Workbook

Const ROW_COUNT As Long = 1000   '<< This is the amount of rows in the dataset

Dim iArray As Integer
Dim lRow As Long
Dim lRow2 As Long
Dim sArray(100) As String

'sArray has been populated with words here :)

'Open an instance of Word   
Set oApp = New Excel.Application
oApp.Visible = False

Set oWB = oApp.Workbooks.Add

oApp.Visible = True
lRow2 = 1

For iArray = LBound(sArray) To UBound(sArray)
    lRow = 1
    
    Do
        If sArray(iArray) = oWB.Sheets("Sheet1").Cells(lRow, 1).Value Then
            'Word found, make value of sheet2 row to that of sheet1 row
            oWB.Sheets("Sheet2").Cells(lRow2,1).Value = oWB.Sheets("Sheet1").Cells(lRow, 1).Value
            lRow2 = lRow2 + 1
            
            'Exit the loop as the word has already been found
            Exit Do
        End If
        
        lRow = lRow + 1
    Loop While lRow < ROW_COUNT
Next iArray

Erase sArray
Set oWB = Nothing
Set oApp = Nothing