Prev: Excel 2010
Next: Exclude one folder in a filesearch
From: Stuart on 22 Apr 2010 06:10
Dear Group

This is probably somewhere here on this site but I cannot seem to
track it down so I hope someone can help me.

I am looking for a macro that can find the word "Start" in column B
and then move one row and select all the rows until the word "End" is
found in Column B".

Then copy from sheet named "Datafrom" to sheet named "Datato".

I really hope someone can help with this.

Thanks very much in advance.
From: ozgrid.com on 22 Apr 2010 06:29
Try;

Sub DoIt()
Dim rRange As Range

On Error Resume Next
Set rRange = Range(Range("A:A").Find("Start", _
Cells(1, 1), xlFormulas, _
xlPart, xlByRows, xlNext, False), _
Range("A:A").Find("End", _
Cells(1, 1), xlFormulas, _
xlPart, xlByRows, xlNext, False))
On Error GoTo 0

If Not rRange Is Nothing Then
Application.Goto rRange.EntireRow
Else
MsgBox "'Start' or 'End' not found"
End If
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"Stuart" <swilson2006(a)gmail.com> wrote in message
news:9f515189-7304-4d30-ad1a-596e65cdfbdf(a)q15g2000yqj.googlegroups.com...
> Dear Group
>
> This is probably somewhere here on this site but I cannot seem to
> track it down so I hope someone can help me.
>
> I am looking for a macro that can find the word "Start" in column B
> and then move one row and select all the rows until the word "End" is
> found in Column B".
>
> Then copy from sheet named "Datafrom" to sheet named "Datato".
>
> I really hope someone can help with this.
>
> Thanks very much in advance.

From: Mike H on 22 Apr 2010 07:14
Stuart,

try this

Sub CopyData()
Set srcSht = Sheets("Datafrom")
Set dstSht = Sheets("Datato")
Dim lastrow As Long
Dim StRow As Range
Dim EndRow As Range
Set StRow = srcSht.Range("B:B").Find("Start", LookIn:=xlValues,
lookat:=xlWhole)
Set EndRow = srcSht.Range("B:B").Find("End", LookIn:=xlValues,
lookat:=xlWhole)
If StRow Is Nothing Or EndRow Is Nothing Then
MsgBox "Range Not Found"
Exit Sub
End If
lastrow = dstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
srcSht.Rows(StRow.Row & ":" & EndRow.Row).Copy
Destination:=dstSht.Range("A" & lastrow)
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Stuart" wrote:

> Dear Group
>
> This is probably somewhere here on this site but I cannot seem to
> track it down so I hope someone can help me.
>
> I am looking for a macro that can find the word "Start" in column B
> and then move one row and select all the rows until the word "End" is
> found in Column B".
>
> Then copy from sheet named "Datafrom" to sheet named "Datato".
>
> I really hope someone can help with this.
>
> Thanks very much in advance.
> .
>
From: Stuart on 22 Apr 2010 07:13
Dave

Thanks for this.

I made a slight change to the code as follows so that it would look up
column B.

> Sub DoIt()
>  Dim rRange As Range
>
>  On Error Resume Next
>  Set rRange = Range(Range("B:B").Find("Start", _
>         Cells(1, 2), xlFormulas, _
>             xlPart, xlByRows, xlNext, False), _
>             Range("B:B").Find("End", _
>         Cells(1, 2), xlFormulas, _
>             xlPart, xlByRows, xlNext, False))
> On Error GoTo 0
>
>  If Not rRange Is Nothing Then
>   Application.Goto rRange.EntireRow
>  Else
>    MsgBox "'Start' or 'End' not found"
>  End If
> End Sub

Is there a way to add a forumla automatically in column I for all the
rows selected above. The formula would be ="DATA"&" - "&B8&" - "&C8&"
- "&E8&"EUR".

Also your macro above does not copy all the above selected rows and
paste them into another worksheet called "Datato".

Can this be added?

Kind Regards,

Stuart

On Apr 22, 11:29 am, "ozgrid.com" <d...(a)ozgrid.com> wrote:
> Try;
>
> Sub DoIt()
>  Dim rRange As Range
>
>  On Error Resume Next
>  Set rRange = Range(Range("A:A").Find("Start", _
>         Cells(1, 1), xlFormulas, _
>             xlPart, xlByRows, xlNext, False), _
>             Range("A:A").Find("End", _
>         Cells(1, 1), xlFormulas, _
>             xlPart, xlByRows, xlNext, False))
> On Error GoTo 0
>
>  If Not rRange Is Nothing Then
>   Application.Goto rRange.EntireRow
>  Else
>    MsgBox "'Start' or 'End' not found"
>  End If
> End Sub
>
> --
> Regards
> Dave Hawleywww.ozgrid.com"Stuart" <swilson2...(a)gmail.com> wrote in message
>
> news:9f515189-7304-4d30-ad1a-596e65cdfbdf(a)q15g2000yqj.googlegroups.com...
>
>
>
> > Dear Group
>
> > This is probably somewhere here on this site but I cannot seem to
> > track it down so I hope someone can help me.
>
> > I am looking for a macro that can find the word "Start" in column B
> > and then move one row and select all the rows until the word "End" is
> > found in Column B".
>
> > Then copy from sheet named "Datafrom" to sheet named "Datato".
>
> > I really hope someone can help with this.
>
> > Thanks very much in advance.

From: Stuart on 23 Apr 2010 16:07
Mike

Thanks for your reply.

Do you happen to know how I can add the following formula into the
last column of data?

="DATA"&" - "&B8&" - "&C8&"

I want to add the above formula into the last column where there are
rows containing text in the first cell of that row.

I appreciate that this is a rather complicated request but I was
hoping that someone on the forum would be able to assist.

Kind Regards,

Stuart


On Apr 22, 12:14 pm, Mike H <Mi...(a)discussions.microsoft.com> wrote:
> Stuart,
>
> try this
>
> Sub CopyData()
> Set srcSht = Sheets("Datafrom")
> Set dstSht = Sheets("Datato")
> Dim lastrow As Long
> Dim StRow As Range
> Dim EndRow As Range
> Set StRow = srcSht.Range("B:B").Find("Start", LookIn:=xlValues,
> lookat:=xlWhole)
> Set EndRow = srcSht.Range("B:B").Find("End", LookIn:=xlValues,
> lookat:=xlWhole)
> If StRow Is Nothing Or EndRow Is Nothing Then
>     MsgBox "Range Not Found"
>         Exit Sub
> End If
>     lastrow = dstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
>     srcSht.Rows(StRow.Row & ":" & EndRow.Row).Copy
> Destination:=dstSht.Range("A" & lastrow)
> End Sub
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
>
> "Stuart" wrote:
> > Dear Group
>
> > This is probably somewhere here on this site but I cannot seem to
> > track it down so I hope someone can help me.
>
> > I am looking for a macro that can find the word "Start" in column B
> > and then move one row and select all the rows until the word "End" is
> > found in Column B".
>
> > Then copy from sheet named "Datafrom" to sheet named "Datato".
>
> > I really hope someone can help with this.
>
> > Thanks very much in advance.
> > .

 |  Next  |  Last
Pages: 1 2
Prev: Excel 2010
Next: Exclude one folder in a filesearch