HI Everyone I am struggling with a false true on a VBA - in short the system need to check one button needs to update a onepager sheet which indicate a Y or N for a market. to get to the yes or no a samples results need to be checked against the market's spec. I have created a user form to submit the results since there are more than 500 different actives which could be present however usually one less than 10 are detected. So i have a database sheet in which the actives are vertical and a one pager in which it is indicated which marketplace's database to use. I want to use the Userform's dropdown (Active1) to check which active are present and then have vba check that the result which is in the textbox (NewMRL1) against the relevant database which is indicated in a specific cells on the onepager (RelSh). I used the If in vba but the results come in as a false positive / false true. for example the country limit will be 5 while the value in NewMRL1 is 3. If i type the code for if 3 greater than 5 then msgbox ("above limit") then it gives the correct answer but the minute i start referring to the relevant values of the different sheets it gives a false positive. If i debug and hover over the values then it does give me the correct values to which are referred to. I have also changed the > and < to check if i did not have it the wrong way round but even that did not help he always gives a true back. I have also changed the ranges checkups so please excuse the horible coding which is totally not neat! Here is my code please help!
your Assistance is much appreciated!
Private Sub Check_ResultsAgainstCry()
'Identify MRL country sheet Dim MRLCry As Worksheet Set MRLCry = Worksheets("MRL - Countries")
'Identify worksheet in which references should retrieved from Dim RelSh As Worksheet strWSName = LRSICom.Value Set RelSh = Worksheets(strWSName)
Set MRLCryActRange = MRLCry.Range("A:A")
Dim RelRow1 As Long 'RelRow1 = MRLCryActRange.Find(Active1.Value, LookIn:=xlValues, lookat:=xlWhole).Row
'Set Cry database search range Dim CryDB_SearhRange As Range Set CryDB_SearhRange = MRLCry.Range("A4:XF4")
'Find relevant Country's column based on Relevant sheets cell information CryDB1 = CryDB_SearhRange.Find(RelSh.Cells(4, "AA").Value, LookIn:=xlValues, lookat:=xlWhole).Column
'Check the results in textbox against the relevant database column If MRLCry.Cells(RelRow1, CryDB1).Value < NewMRL1.Value Then MsgBox ("Result 1 above limit of Country" & MRLCry.Range("A4:XF4").Value)
End Sub
I expect it to do nothing when the results is below the limit but should give the message box if the results is above the limit.... currently it give the message box when the result is above below equal to or even blank...
Aucun commentaire:
Enregistrer un commentaire