Problem with VB more than 10

Solved/Closed
Will - Feb 19, 2010 at 07:11 AM
 Will - Feb 19, 2010 at 09:37 AM
Hello,
I having a problem when setting up a program to find values that are more than or less than those of in a cell (s(x). I return an output (putting a 1 in coloumn) when the number s(x) i am trying to find below is set between 1 and 9.9 but when i set it at 10 it doesn’t work, (incidentally it does work at multiples of 10 of the number in the row ie if the number in the row i am looking at is 8 it will return an out put if the max number is set at 80). i am guessing this is because i am not setting it up too look for a number??????- anybody have any ideas???????

my code

Private Sub CommandButton1_Click()
Dim col(1 To 6) As String, a As Integer, r As Integer
Dim x As Integer, s(1 To 6) As String, inputy(1 To 6) As String

col(1) = "a"
col(2) = "b"
col(3) = "c"
col(5) = "d"
' columns i am looking in


s(1) = Worksheets("sheet2").Range("F2")
s(2) = Worksheets("sheet2").Range("G2")
s(3) = Worksheets("sheet2").Range("H2")
s(4) = Worksheets("sheet2").Range("I2")
s(5) = Worksheets("sheet2").Range("J2")
s(6) = Worksheets("sheet2").Range("K2")
' max and min values

inputy(1) = "F3"
inputy(2) = "G3"
inputy(3) = "H3"
inputy(4) = "I3"
inputy(5) = "J3"
inputy(6) = "K3"
' the output is set to one

Set ws = Worksheets("sheet2")

For r = 10 To 10

If ws.Cells(r, col(1)) = s(1) Then
Worksheets("sheet2").Range(inputy(1)) = 1
End If
If ws.Cells(r, col(2)) = s(2) Then
Worksheets("sheet2").Range(inputy(2)) = 1
End If


For x = 3 To 5 Step 2

If s(x) >= ws.Cells(r, col(x)) Then
Worksheets("sheet2").Range(inputy(x)) = 1
End If

If s(x + 1) <= ws.Cells(r, col(x)) Then
Worksheets("sheet2").Range(inputy(x + 1)) = 1
End If

Next x
If ws.Range("L5") = Worksheets("sheet2").Range("L6") Then
MsgBox ("it works !!! copy " & r)
End If
ws.Range("A3:z3").Clear
Next r

End Sub
Related:

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 19, 2010 at 08:07 AM
Could you provide the data that you are using to test and expected result. May be put the sheet at some share site with you test data and expected results like https://authentification.site

I am not clear on your loops like For r = 10 To 10
0
yeah sorry i was just looping it along one row to try and figure it out.
i am going to make it into a much larger data base search program with alot more search criteria so thats why i was lopping it round some and not others.


https://authentification.site/files/21018150/Search_prog.xls
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 19, 2010 at 09:01 AM
You have declared S() to be of type string. So it is doing comparing a string with a number and hence your issue

you can do some thing like that

If (IsNumeric(s(x)) And IsNumeric(Cells(r, col(x)))) Then

If CDbl(s(x)) >= CDbl(Cells(r, col(x))) Then
Worksheets("sheet2").Range(inputy(x)) = 1
End If

ElseIf s(x) >= ws.Cells(r, col(x)) Then

Worksheets("sheet2").Range(inputy(x)) = 1
End If


Also by the way "tues" and "Tues" also failed on test (case issue of letter T)
0
Cheers thats great, works like a dream - just now got to expand it !!!

In the actual database i have a validation list so theres no change of typos.

Thanks for your help.
0