Search for Range not single number

**jonn** · 04-14-2007, 03:58 PM

Hi,

I have this code below, how do i search for a range i.e. if the number is greater than 0 or '1 - 100', rather than a single number,

I've tried specifiying 'stringTolookFor = Range("1:100")' but is comes up with an error.

Sub CommsInstallFind()

Dim sh As Worksheet
Dim shData As Worksheet
Dim lastcell As Range
Dim FixedRowPos As Long
Dim BOOKS As Long
Dim RowSearch As Long
Dim ROWPOS As Long
Dim X As Integer
Dim y As Integer
Dim SheetName As String

Set sh = ThisWorkbook.ActiveSheet
Set shData = ThisWorkbook.Sheets("Comms")

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="PLEC"

Set lastcell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

For X = 1 To lastcell.Row
    If Cells(X, 1) = "Part Code" Then Exit For
Next

FixedRowPos = X + 1 ' row of set for header etc. used at end for clear

Worksheets("Summary").Activate
Set lastcell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Range(Cells(FixedRowPos, 1), Cells(lastcell.Row, lastcell.Column)).Clear



stringToLookFor = "1"
myColumn = ActiveCell.Column
myRow = 17

ROWPOS = X + 1

With shData.Columns("F:F").Rows("2:9999")
    Set mycell = .Find(stringToLookFor, LookIn:=xlValues)
    If Not mycell Is Nothing Then
        firstAddress = mycell.Address
        Do
    
            'if mycell = stringtolookfor then
                myFoundRow = mycell.Row
                shData.Range("A" & myFoundRow & ":D" & myFoundRow).Copy _
                    Destination:=sh.Cells(myRow, 1)
                'cost
                shData.Range("E" & myFoundRow).Copy _
                    Destination:=sh.Cells(myRow, 6)
                'Quantity
                 shData.Range("F" & myFoundRow).Copy _
                    Destination:=sh.Cells(myRow, 7)
                myRow = myRow + 1
                
                'Nett Price
                Cells(myRow - 1, 8) = "=sum(F" & ROWPOS & "*" & "G" & ROWPOS & ")*(1-c11)"
                Cells(myRow - 1, 8).Font.Size = 8
                Cells(myRow - 1, 8).NumberFormat = "£0.00"
                Cells(myRow - 1, 8).HorizontalAlignment = xlHAlignCenter
                
                'Nett Cost
                Cells(myRow - 1, 9) = "=sum(D" & ROWPOS & "*" & "G" & ROWPOS & ")"
                Cells(myRow - 1, 9).Font.Size = 8
                Cells(myRow - 1, 9).NumberFormat = "£0.00"
                Cells(myRow - 1, 9).HorizontalAlignment = xlHAlignCenter
              
                'Margin
                Cells(myRow - 1, 10) = "=1 - sum(I" & ROWPOS & " / H" & ROWPOS & ")"
                Cells(myRow - 1, 10).Font.Size = 8
                Cells(myRow - 1, 10).NumberFormat = "0.00%"
                Cells(myRow - 1, 10).HorizontalAlignment = xlHAlignCenter
                
                ROWPOS = X + 1
      FixedRowPos = X + 2
      
    'Total Text Box
        Cells(ROWPOS + 4, 7) = "Total Price"
            'Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
      
    'Total Cell Sum
       Cells(ROWPOS + 4, 8) = "=sum(H" & ROWPOS & ":" & "H" & FixedRowPos & ")"
            'Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
            
    'Total Cost Text Box
        Cells(ROWPOS + 5, 7) = "Total Cost"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
      
    'Total Cost Cell Sum
       Cells(ROWPOS + 5, 8) = "=sum(I" & ROWPOS & ":" & "I" & FixedRowPos & ")"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
            
    'Total Margin Text Box
        Cells(ROWPOS + 6, 7) = "Total Margin"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
      
    'Total Margin Cell Sum
        Cells(ROWPOS + 6, 8) = "=1 - sum(H" & ROWPOS + 5 & " / H" & ROWPOS + 4 & ")"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
            Cells(ROWPOS + 6, 8).NumberFormat = "0.00%"
                
            Set mycell = shData.Columns("F:F").FindNext(mycell)

        Loop While Not mycell Is Nothing _
            And mycell.Address <> firstAddress
            
             Else
         MsgBox ("Nothing Found")
    
      End If
    End Sub

Thanks

**avveerkar** · 04-16-2007, 09:45 AM

Originally Posted by jonn

Hi,

I have this code below, how do i search for a range i.e. if the number is greater than 0 or '1 - 100', rather than a single number,

I've tried specifiying 'stringTolookFor = Range("1:100")' but is comes up with an error.

Sub CommsInstallFind()

Dim sh As Worksheet
Dim shData As Worksheet
Dim lastcell As Range
Dim FixedRowPos As Long
Dim BOOKS As Long
Dim RowSearch As Long
Dim ROWPOS As Long
Dim X As Integer
Dim y As Integer
Dim SheetName As String

Set sh = ThisWorkbook.ActiveSheet
Set shData = ThisWorkbook.Sheets("Comms")

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="PLEC"

Set lastcell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

For X = 1 To lastcell.Row
    If Cells(X, 1) = "Part Code" Then Exit For
Next

FixedRowPos = X + 1 ' row of set for header etc. used at end for clear

Worksheets("Summary").Activate
Set lastcell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Range(Cells(FixedRowPos, 1), Cells(lastcell.Row, lastcell.Column)).Clear



stringToLookFor = "1"
myColumn = ActiveCell.Column
myRow = 17

ROWPOS = X + 1

With shData.Columns("F:F").Rows("2:9999")
    Set mycell = .Find(stringToLookFor, LookIn:=xlValues)
    If Not mycell Is Nothing Then
        firstAddress = mycell.Address
        Do
    
            'if mycell = stringtolookfor then
                myFoundRow = mycell.Row
                shData.Range("A" & myFoundRow & ":D" & myFoundRow).Copy _
                    Destination:=sh.Cells(myRow, 1)
                'cost
                shData.Range("E" & myFoundRow).Copy _
                    Destination:=sh.Cells(myRow, 6)
                'Quantity
                 shData.Range("F" & myFoundRow).Copy _
                    Destination:=sh.Cells(myRow, 7)
                myRow = myRow + 1
                
                'Nett Price
                Cells(myRow - 1, 8) = "=sum(F" & ROWPOS & "*" & "G" & ROWPOS & ")*(1-c11)"
                Cells(myRow - 1, 8).Font.Size = 8
                Cells(myRow - 1, 8).NumberFormat = "£0.00"
                Cells(myRow - 1, 8).HorizontalAlignment = xlHAlignCenter
                
                'Nett Cost
                Cells(myRow - 1, 9) = "=sum(D" & ROWPOS & "*" & "G" & ROWPOS & ")"
                Cells(myRow - 1, 9).Font.Size = 8
                Cells(myRow - 1, 9).NumberFormat = "£0.00"
                Cells(myRow - 1, 9).HorizontalAlignment = xlHAlignCenter
              
                'Margin
                Cells(myRow - 1, 10) = "=1 - sum(I" & ROWPOS & " / H" & ROWPOS & ")"
                Cells(myRow - 1, 10).Font.Size = 8
                Cells(myRow - 1, 10).NumberFormat = "0.00%"
                Cells(myRow - 1, 10).HorizontalAlignment = xlHAlignCenter
                
                ROWPOS = X + 1
      FixedRowPos = X + 2
      
    'Total Text Box
        Cells(ROWPOS + 4, 7) = "Total Price"
            'Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
      
    'Total Cell Sum
       Cells(ROWPOS + 4, 8) = "=sum(H" & ROWPOS & ":" & "H" & FixedRowPos & ")"
            'Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
            
    'Total Cost Text Box
        Cells(ROWPOS + 5, 7) = "Total Cost"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
      
    'Total Cost Cell Sum
       Cells(ROWPOS + 5, 8) = "=sum(I" & ROWPOS & ":" & "I" & FixedRowPos & ")"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
            
    'Total Margin Text Box
        Cells(ROWPOS + 6, 7) = "Total Margin"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
      
    'Total Margin Cell Sum
        Cells(ROWPOS + 6, 8) = "=1 - sum(H" & ROWPOS + 5 & " / H" & ROWPOS + 4 & ")"
            Range("A" & ROWPOS & ":J" & ROWPOS).Select ' sell
            Cells(ROWPOS + 6, 8).NumberFormat = "0.00%"
                
            Set mycell = shData.Columns("F:F").FindNext(mycell)

        Loop While Not mycell Is Nothing _
            And mycell.Address <> firstAddress
            
             Else
         MsgBox ("Nothing Found")
    
      End If
    End Sub

Thanks

I don't think you can use Find method to find a range Best way out is to use For.. Next Loop.

eg.

for each c in myRange
if c.value > 1 and c.value<100 then
set myCell= c
exit for
next

A V Veerkar

Search for Range not single number

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Search for Range not single number

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions