if between two numbers

**mattmac** · 01-20-2011, 05:21 AM

Hi Guys,
Bit stuck on something. Basically I've got a sheet with 3 columns ....

a - the name of a zone ... b and c are two numbers. there are 22 rows in this sheet (say sheet1)

I have another sheet (sheet2) with an unknown number of rows (will change). one of the columns (say F) has a number in it. If this number is between columns B and C in sheet1 then i want sheet2 column D to = sheet1 column a

quite complicated to explain sorry! anyway here's the code i've done but i get object required error at the "Set CheckCells = Sheet.CMDB.UsedRange" line

Sub Location()
Dim INCR As Integer
Dim cells As Range, CheckCells As Range

Set CheckCells = Sheet.CMDB.UsedRange

INCR = 0

A:

For Each cells In CheckCells

    INCR = INCR + 1

    If Sheet.CMDB("E(INCR)") Or Sheet.CMDB("F(INCR)") Or Sheet.CMDB("G(INCR)") Or Sheet.CMDB("H(INCR)") Or Sheet.CMDB("i(INCR)") > Sheet.SDC_PROD("D(INCR)") < Sheet.SDC_PROD("E(INCR)") Then Sheet.CMDB("D(INCR)") = Sheet.SDC_PROD("A(INCR)")

    If INCR = 22 Then GoTo A

Next

End Sub

**Armando Montes** · 01-20-2011, 06:14 AM

See if this helps:
http://www.mrexcel.com/forum/showthr...aracter+number

Public Function IsBetween(testvalue As Variant, lwr As Variant, upr As Variant, Optional equaltype As Boolean) As Boolean
If equaltype Then
    IsBetween = (testvalue >= lwr And testvalue <= upr)
Else
    IsBetween = (testvalue > lwr And testvalue < upr)
End If
End Function

**mattmac** · 01-20-2011, 08:17 AM

Ok thanks for that... i've changed my code.

Getting an overflow at INCR = INCR + 1

Sub IsBetween()

' ### INITIAL SETUP
Dim INCR As Integer
Dim cell As Range
Dim testvalue As Variant
Dim lwr As Variant
Dim upr As Variant
Dim equaltype As Boolean
Dim IsBetween As Boolean
Dim i As Integer
Dim j As Integer
Dim sheet As Worksheet
Dim UsedRange As Range

INCR = 0
' ### START LOOP FOR ALL CMDB ASSETS

Set sheet = ActiveSheet
Set cell = sheet.UsedRange
For i = 1 To cell.Rows.Count
    
    ' ### START LOOP FOR ALL ZONES
    For j = 1 To 22

         INCR = INCR + 1
         lwr = ("SDC_PROD!(D(INCR))")
         upr = ("SDC_PROD!(E(INCR))")
         IsBetween = "TRUE"
    
         If equaltype Then
                IsBetween = (testvalue >= lwr And testvalue <= upr)
         Else
                IsBetween = (testvalue > lwr And testvalue < upr)
         End If
    
         If equaltype = True Then
              sheet.["D(i + 1)"] = Worksheet.SDC_PROD("A(INCR)")
         End If
            
         ' ### END ZONE LOOP
         Next j
Next i
End Sub

**DonkeyOte** · 01-20-2011, 08:52 AM

I've not reviewed the code but change INCR to Long (from Integer).

Row numbers can exceed Integer's upper bounday (32767)

**mattmac** · 01-20-2011, 10:45 AM

Thanks, changed that and I'm no longer getting an overflow.

Code still isn't doing what I want it to though, infact it's not doing anything

**mattmac** · 01-20-2011, 12:00 PM

Updated code but it's still not working

Basically it's just copying cells 1 to 22 from the CMDB sheet and pasting them in the SDC_PROC sheet in D2 - D23. Not sure what I've done wrong

Sub IsBetween()
' ### INITIAL SETUP
Dim INCR As Long
Dim cell As Range
Dim testvalue As Variant
Dim lwr As Variant
Dim upr As Variant
Dim equaltype As Boolean
Dim IsBetween As Integer
Dim i As Long
Dim j As Long
Dim sheet As Worksheet
Dim UsedRange As Range

Application.ScreenUpdating = False

Sheets("CMDB").Select
Set sheet = ActiveSheet
Set cell = sheet.UsedRange

' ### START LOOP FOR ALL CMDB ASSETS
For i = 1 To cell.Rows.Count
    INCR = 1
    ' ### START LOOP FOR ALL ZONES
    For j = 1 To 22

         INCR = INCR + 1
         Sheets("SDC_PROD").Select
         lwr = ("D" & j)
         upr = ("E" & j)
         Sheets("CMDB").Select
         testvalue = ("E" & i)

            If testvalue >= lwr And testvalue <= upr Then
                 Sheets("SDC_PROD").Select
                 Range("A" & j).Select
                 Application.CutCopyMode = False
                 Selection.Copy
                 Sheets("CMDB").Select
                 Range("D" & INCR).Select
                 ActiveSheet.Paste
                 
            End If
    
         ' ### END ZONE LOOP
         Next j
         
' ### END CMDB ASSETS LOOP
Next i

Application.ScreenUpdating = True
End Sub