Find and selecting cells of same value

[antler44]

Hi

I am using Excel 2003 and I'm trying (and failing) to write a VBA script to look at a range (A2:A300) and select all cells containing the same integer value (from an input box) AND also cells containing the original value but plus 0.5.

The input box asks for a number (say 4) and I want to select all cells containg 4 and 4.5 and copy the adjacent cells in Column B.

At the moment I am using Find but that is also selecting 14, 24 etc etc and if I use LookAt: =xlWhole, I only get 4 and not the 4.5.

Any help would be greatly appreciated.

Thanks in advance.

This is the code so far:

Dim firstAddress As String, c As Range, rALL As Range
Dim src As Workbook

Set src = Workbooks.Open("n:\target\wdf2.xls")

oscar = InputBox("Which Comps?") 'which comp to process

With Worksheets("Target").Range("A1:A300")
Set c = .Find(oscar, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
Set rALL = c
firstAddress = c.Address
Do
Set rALL = Union(rALL, c)
Worksheets("Target").Range(c.Address).Activate
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
.Activate
If Not rALL Is Nothing Then rALL.Select
End With


Selection.Copy

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub antler44()
   Dim Res As Double
   
   Res = InputBox("Which Comps?")
   With Sheets("Data")
      .Range("A1").AutoFilter 1, Res, xlOr, Res + 0.5
      .AutoFilter.Range.Offset(1, 1).Resize(, 1).Copy
      .AutoFilterMode = False
   End With
End Sub

 

[antler44]

Many thanks, Fluff. I should've thought of that myself!! Works perfectly and much quicker than looping through cells.
Cheers

 

[Fluff]

You're welcome & thanks for the feedback