Solved: simple Instr doesn't work properly

[boller4prez]

Sub findstuff()
Set s = Sheets("Sheet1")
Set r = Sheets("Sheet4")
Dim i
Dim j
i = 2
j = 2

Do Until (s.Range("P" & i) = "") Or (r.Range("C" & j) = "")
If InStr(UCase(r.Range("C" & j)), UCase(s.Range("P" & i))) And InStr(UCase(r.Range("C" & j)), UCase("installed ok")) = 0 Then
'MsgBox "no match"
j = j + 1
Else
'MsgBox "at least one match"
i = i + 1
End If
Loop
End Sub

I'm running this against the cells in c of sheet 4 to find out what has been installed on the machines versus what hasn't. I know once
I get this to work I can program the rest of the macro myself.
I have been trying at this for hours anyone have any suggestions?

[Bob Phillips]

It is a bit hard to assist, the procedure does nothing.

You check and then exit, doing nothing. What is supposed to happen?

You should change it, at the least, to



Sub findstuff()
Dim s As Worksheet, r As Worksheet
Dim i
Dim j

Set s = Sheets("Sheet1")
Set r = Sheets("Sheet4")
i = 2
j = 2
Do Until (s.Range("P" & i).Value = "") Or (r.Range("C" & j).Value = "")
If InStr(UCase(r.Range("C" & j).Value), UCase(s.Range("P" & i).Value)) = 0 And _
InStr(UCase(r.Range("C" & j).Value), UCase("installed ok")) = 0 Then

'MsgBox "no match"
j = j + 1
Else

'MsgBox "at least one match"
i = i + 1
End If
Loop
End Sub

[boller4prez]

Sub findstuff()
Set s = Sheets("Sheet1")
Set r = Sheets("Sheet4")
Dim i
Dim j
i = 2
j = 2

Do Until (s.Range("P" & i) = "") Or (r.Range("C" & j) = "")
If InStr(UCase(r.Range("C" & j)), UCase(s.Range("P" & i))) And InStr(UCase(r.Range("C" & j)), UCase("installed ok")) = 0 Then
'MsgBox "no match"
i = i + 1
Else
'MsgBox "at least one match"
s.Range("Q" & i) = "Installed Ok"
s.Range("R" & i) = (r.Range("A" & j) & " " & r.Range("B" & j))
j = j + 1
i = i + 1
End If
Loop
End Sub


I'm sorry. I am a shell scripter by nature. I am programming with VBA for the first time. I thought I fixed my script but it still doesn't give the proper results(copies locations). I assume it is something wrong with my counters because the script for all intent purposes should work.

I added some additional code so that it should give you a clear picture of what I'm looking for. I want to record on the spreadsheet whether or not the update was installed and at what time. I need the macro to work to verify this.

[Bob Phillips]

Well it does it! And is that right or wrong? Some clues might help.

[boller4prez]

Thanks for trying to help me solve my coding problem.

I am trying to compare the values of each cell of sheet 1(required updates) against the (installed updates) imported from log file in sheet 4.

The incremental loop is supposed to go through all the values in sheet one comparing each against the first cell in sheet4. If it finds a match and the message installed ok is contained in sheet 4 it copies the date installed and the message installed ok in sheet1(then moves on tot he next value in sheet4). Otherwise the message "Not installed is recorded"(then moves on to the next value in sheet1).

The loop I created copied the values from the cell position. I checked and the values do not match. A proper comparison was not done.

I feel as if I probably missing something small in the coding process.

[boller4prez]

I'm still trying to get this to work. I still am unable to get the loop to properly compare values. If someone more knowlegdable then me could help that would be great.

[Sagy]

I think that your problem might be in
If InStr(UCase(r.Range("C" & j)), UCase(s.Range("P" & i))) And InStr(UCase(r.Range("C" & j)), UCase("installed ok")) = 0 Then

The first half of the If is problematic since you just take the value returned by InStr which is a Long, not a Boolean, you probably what to change it to
InStr(UCase(r.Range("C" & j)), UCase(s.Range("P" & i))) = 0
This will move you to the next value in sheet one if the update doesn't match the one in sheet4.

You might also need to break the if into two conditions:
1) Check if the updates match
2) If the update match check if it was successful

Last you might want to consider removing the UCase and use the "compare" option of the InStr function. So you end up with something like
Sub findstuff()
Set s = Sheets("Sheet1")
Set r = Sheets("Sheet4")
Dim i
Dim j
i = 2
j = 2

Do Until (s.Range("P" & i) = "") Or (r.Range("C" & j) = "")
If InStr(1, r.Range("C" & j), s.Range("P" & i), vbTextCompare) = 0 Then
'MsgBox "no match"
i = i + 1
Else
'MsgBox "at least one match"
If InStr(1, r.Range("C" & j), "installed ok", vbTextCompare) = 0 Then
' Install failed
s.Range("Q" & i) = "Installed Failed"
Else
' Install worked
s.Range("Q" & i) = "Installed Ok"
s.Range("R" & i) = (r.Range("A" & j) & " " & r.Range("B" & j))
End If
j = j + 1
i = i + 1
End If
Loop
End Sub

[boller4prez]

Thanks guys for your effort in resolving my problem. I solved the problem. I flowcharted my program and found a logic error.

Sub findmatch()
Dim s As Worksheet, r As Worksheet
Dim i
Dim j

Set s = Sheets("Sheet1")
Set r = Sheets("Sheet4")
i = 2
j = 2
req_pkg = UCase(s.Cells(i, "P"))
ins_pkg = UCase(r.Cells(j, "C"))

Do Until req_pkg = ""
If InStr(1, ins_pkg, req_pkg, vbTextCompare) > 0 And InStr(1, ins_pkg, "INSTALLED OK", vbTextCompare) > 0 Then
s.Cells(i, "Q").Value = "Installed Ok"
s.Cells(i, "R").Value = r.Cells(j, "A").Value & " " & r.Cells(j, "B")
j = 2
i = i + 1
End If
If ins_pkg = "" Then
s.Cells(i, "Q").Value = "Not Installed"
j = 2
i = i + 1
End If
j = j + 1
req_pkg = UCase(s.Cells(i, "P"))
ins_pkg = UCase(r.Cells(j, "C"))
Loop
End Sub