#1   Report Post  
Jeff
 
Posts: n/a
Default Range in VBA

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

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting source data range with Charts D Charts and Charting in Excel 2 January 1st 06 03:51 AM
Auto x range start value, xy charts [email protected] Charts and Charting in Excel 5 January 25th 05 12:49 AM
How do you change the size of a range of cells in a column/row SOkoll Charts and Charting in Excel 1 December 8th 04 07:43 AM
Achieving Range with a trendline cshdabomb Charts and Charting in Excel 1 December 2nd 04 01:58 PM
Extending a Chart Data Series from an Array - Can it be done? Jon Peltier Charts and Charting in Excel 4 November 30th 04 04:30 AM


All times are GMT +1. The time now is 01:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"