jeudi 24 septembre 2015

Nesting IF statements within Do, While loops

I have been having an issue with a piece of code, where I want to compare a user input string to a set of strings in a column, then run a loop, which checks to see if they are identical, if not it prompts the user for another string. If they match, it goes ahead and executes the next section of the script.

I call both variables Checktag and ServiceTag: (both are Variants).

ServiceTag = InputBox("Please enter the laptop service tag ID")

Set TagCheck =     ActiveWorkbook.Sheets("Sheet5").Range("B2:B1000").Find(ServiceTag, lookat:=xlWhole)

Do
If TagCheck Is Nothing Then

ServiceTag = InputBox(" User entered Service ID is not present in database, please re-enter the laptop service tag ID")

Set TagCheck = ActiveWorkbook.Sheets("Sheet5").Range("B2:B1000").Find(ServiceTag, lookat:=xlWhole)

Else

MsgBox (" Service Tag ID recognised ")

s1.Cells(2, 3).Value = ServiceTag

End If

Loop While TagCheck Is Nothing

Is there an easier way to do this?

It seems like the code uninitializes TagCheck -setting it to nothing- if it has not been matched the the ServiceTag Value.

I have another sub which does a similar job, but then goes ahead and writes to a specific cell after performing another cell/user comparison:

 Set rngX = ActiveWorkbook.Sheets("Sheet5").Range("B2:B1000").Find(ServiceTag, lookat:=xlWhole)

Set TagCheckA = ActiveWorkbook.Sheets("Sheet1").Range("C2:C10000").Find(ServiceTag, lookat:=xlWhole)

If Not TagCheckA Is Nothing Then

RowZ = Range(TagCheckA.Address).Row

DateReadA = Worksheets("Sheet1").Cells(RowZ, 7).Value

End If

If TagCheck = TagCheckA And DateRead = DateReadA Then

    If Not rngX Is Nothing Then
         RowX = Range(rngX.Address).Row
         s5.Cells(RowX, 3).Interior.Color = RGB(0, 255, 0)
         s5.Cells(RowX, 3).Value = " Checked IN "
         s5.Cells(RowX, 4).Value = myDate

      End If

s1.Cells(RowZ, 8).Value = myDate

MsgBox ("The Laptop has been Sucessfully Signed IN")

In this case I want a cell to be edited and filled green when the matches are satisfied. However, it always skips that section of code and jumps straight to the final message-box. I am not sure why.

Aucun commentaire:

Enregistrer un commentaire