Range in VBA

J

Jeff

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,
 
O

Otto Moehrbach

Jeff
Do you mean that you have "199" in a number of cells in Column A and you
want to copy/paste them? Where do you want to paste them? Otto
 
M

Mark

Hi,
yeh, it is a dubious question. do you mean "#199" 199
times from a1 to the cell in Column A that is 199?

Try This:

sub 199mark()
dim i as integer
i=0
range("A1").select

set a=selection
do until a.offset(i,0).value=199

if a.offset(i,0).value<>199 then
a.offset(i,0).value=199
end if
i=i+1
loop
end sub



send me the info to excelmarksway2yahoo.com.au

see my stuff at
http://www.geocities.com/excelmarksway


- mark
 
D

Dave Peterson

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
 
J

Jeff

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
 
D

Dave Peterson

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
 
Top