Is there gonna be a 201, 202, ...??

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range
Dim myStr As Variant
Dim iCtr As Long

myStr = Array("199", "200")

Set wks = ActiveSheet

With wks
.Range("a1").Value = 199 'just in case it isn't in A1
For iCtr = LBound(myStr) To UBound(myStr)
Set TopCell = Nothing
Set BotCell = Nothing

With .Range("a:a")
Set TopCell = .Cells.Find(What:=myStr(iCtr), _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set BotCell = .Cells.Find(What:=myStr(iCtr), _
After:=.Cells(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With

If TopCell Is Nothing Then
MsgBox myStr(iCtr) & " wasn't found"
Else
.Range(TopCell, BotCell).Value = myStr(iCtr)
End If
Next iCtr

End With
End Sub




Jeff wrote:

Thank you for help. This is what I wanted. Now what would be a new VBA macro
if I wanted to copy / paste the all rows that contain "200". These rows come
after the rows that contain "199" ?
Thanks,

Like this:
199
199
199
200
200
200

"Dave Peterson" wrote:

Run this against a copy of your workbook--it does what you describe, but I'm not
sure that's what you want:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim myStr As String

myStr = "199"

Set wks = ActiveSheet
With wks
With .Range("a:a")
Set FoundCell = .Cells.Find(What:="199", _
After:=.Cells(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox myStr & " wasn't found"
Else
.Range("a1", FoundCell).Value = 199
End If

End With
End Sub



Jeff wrote:

Hello,

I need a VBA Macro to copy / paste the # "199" in column "A". The range
would have to be from "A1" to the last row in column "A" that contains "199"
Thanks,

--

Dave Peterson


--

Dave Peterson