Declaring Variable question

[Noz2k]

Quick question I think.

Basically I want to have a variable which will be recognised as either < or >= however I'm unsure what type to reference this As?

 

[Norie]

Probaly the only variable type for those would be string but they would just be text - the wouldn't have the same meaning as the operatots.

 

[Noz2k]

Yeah, that won't work then.

Basically I need the variable like that, because dependant upon the user input (between 2 options) I need to only return data either < or >= '15:00' in the time column for example

 

[CharlesChuckieCharles]

Post the code

 

[Norie]

Couldn't you just base it on the chosen option.

Select Case OptionPicked
      Case "Option1"
             ' code to return data less/earlier than '15:00'
      Cate "Option2"
             ' code to return if more/later than or equal to '15:00'
End Select

You might not even need something like that, it really depends on what code you are using to return the data based on the criteria selected.

 

[Noz2k]

Yes, possibly.

Although I've not used Case yet in VBA. I was thinking of using Ifs but it may get quite complicated as there are other criteria/constraints involved and loops.

I will post the code up once I've got the rest of it working fine and it's easier to see what's happening.

 

[Norie]

You don't need to use Select Case, it was just an example.

If there are a lot of criteria using that method might be better than Ifs.

It all really depends on what you are trying to do.

Of course the code will help with that, but an explanation in words wouldn't do any harm either.

 

[Noz2k]

Well as it turns out, the way I wanted to do it wouldn't have worked anyway, because I think it would cross over midnight, so I would have to use date and time rather than just 1.

I will explain in depth tomorrow once I have the code up to the point I need help with and I'll post the code up then.

Thanks for the willingness to help

 

[Noz2k]

Ok, so I have the first part of my code working. Here's a bit of it

With ws1.Range("M3:M" & lngLastRow) 
Set rng = .Find("No", LookIn:=xlValues) 
If Not rng Is Nothing Then
rng1 = rng.Address
Do
 
ws2.Range("B1").Value = Format(Date, "dd/mm/yyyy")
ws2.Range("D1").Value = cbo1.Value & " Handover"
ws2.Range("B55").Value = Format(Date, "dd/mm/yyyy")
ws2.Range("D55").Value = cbo1.Value & " Handover"
If rng.Offset(0, -6) = "Option1" Then
If rng.Offset(, 6) = STE Then
ws2.Range("A60").Offset(findoffset, 0).Value = rng.Offset(, -10) & " " & rng.Offset(, -9)
ws2.Range("D60").Offset(findoffset, 0).Value = rng.Offset(, -11) ws2.Range("G60").Offset(findoffset, 0).Value = rng.Offset(, -5)
findoffset = findoffset + 1
End If
End If
Set rng .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <>rng1
End If

That all works fine, there are other bits in there for option2, option3, etc, but essentially it remains the same.

The problem is when I want to find where the value = "yes" because I then need to add a stipulation to only return the Yes' when the time is less than 15:00 or more than 15:00 if yesterday.

Here is what I have tried unsuccessfully to add immediately after the code above (so still within the With statement)

 Set rngresolved = .Find("Yes", LookIn:=xlValues)
    If Not rngresolved Is Nothing Then
        rngresolved1 = rngresolved.Address
 
            Do
                If cboShift.Value = "AM" Then
                    If (Format(CDate(rngresolved.Offset(, -8)), "dd/mm/yyyy") = Format(Date, "dd/mm/yyyy") _
                    And Format(rngresolved.Offset(, -7), "hh:mm") < Format("15:00", "hh:mm")) _
                    Or (Format(CDate(rngresolved.Offset(, -8)), "dd/mm/yyyy") < Format(Date, "dd/mm/yyyy") - 1 _
                    And Format(rngresolved.Offset(, -7), "hh:mm") >= Format("15:00", "hh:mm")) Then
                        If rngresolved.Offset(,6) = "Option1" Then
                         'etc

 

[Noz2k]

Not to worry, have got that part working now. Was just -1 from the wrong place and creating a type mismatch.