excel multiple look up

[Anonymous]

Hi everyone.. I have an excel problem.....

I can imagine it is quite simple to solve but I'm struggling a little.

I want to run a rule that opens a spreadsheet and looks up column O for a value. if it finds it, then it deletes the value, then it moves onto the next row that has the same value in column O and deletes that value. it keeps going through the data range to the end.

I can open the spreadsheet, define the range, find the first row with the string and delete the contents, but that only happens if there is only one matching cell. if there are two or more cells with the string then nothing happens...

Can I put the code below in a loop and if so can someone help please?

'Search for the file name in column A
For oRow = RowStart To RowEnd

    If (GoExcel.CellValue("O" & oRow)) = "XXX" Then
    GoExcel.CellValue("O" & oRow) = ""
    GoExcel.Save
    GoExcel.Close
    End If
    Next

Thanks for any help

[Anonymous]

I figured it out... I moved the next statement...

[MechMachineMan · Accepted solution]

For our next users... I'm guessing your finished code would look something like this:

'Search for the file name in column A

GoExcel.Open("Book1.xlsx", "Sheet1")

RowStart = 2   'Corresponds to start row
RowEnd = 100   'Corresponds to the some row just beyond the end of the dataset

For oRow = RowStart To RowEnd
    If (GoExcel.CellValue("O" & oRow)) = "XXX" Then
        GoExcel.CellValue("O" & oRow) = ""
    End if
Next

GoExcel.Save
GoExcel.Close

[Anonymous]

hi justin

100% correct.. I was exiting the loop after the first row was found... moving the 'Next' to come before the save/close allowed the program to save after the loop had completed...not before lol...

figured it out 1minute after posting... thanks for your response though....