This works for the first cell that has title "Shop Report", but it will not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

hi
odd. as written the loop is not necessary. in each loop the code always
finds the first instance of "shop report" then always select E4 then always
puts the same formula in E4.
what are you trying to do?

Regards
FSt1

"stacia" wrote:

This works for the first cell that has title "Shop Report", but it will not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

Try removing
Range("E4").Select
as that just selects e4 every loop. That being said you will run into a
problem if it does not find what it is looking for and it wil crash... You
really should set the return value of the find to a range object and check if
that rang object is nothing...
--
HTH...

Jim Thomlinson


"stacia" wrote:

This works for the first cell that has title "Shop Report", but it will not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

Everytime it finds "Shop Report" in column "E" I want it to take the
information from column "A" in the first cell under "Dept" column heading

--
Stacia


"FSt1" wrote:

hi
odd. as written the loop is not necessary. in each loop the code always
finds the first instance of "shop report" then always select E4 then always
puts the same formula in E4.
what are you trying to do?

Regards
FSt1

"stacia" wrote:

This works for the first cell that has title "Shop Report", but it will not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

Do you give everyone a hard time or just those who won't pay your fees. This
forum is for those of us who have questions. Sorry not a VBA expert, just a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.

What do you say to your clients when they ask for free advice? Do you at
least ask that they be civil about it? You should do the same here.

Regards,
Fred

"stacia" wrote in message
...
Do you give everyone a hard time or just those who won't pay your fees.
This
forum is for those of us who have questions. Sorry not a VBA expert, just
a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell,
LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.

It isn't convoluted? Maybe confused. I have an Accounting degree (BBA) from
the University of Texas with graduate work at NYU and am a retired Regional
Manager for ING. My books are already audited.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
Do you give everyone a hard time or just those who won't pay your fees.
This
forum is for those of us who have questions. Sorry not a VBA expert, just
a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell,
LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.

I have never seen Don request any fees for the advice he has supplied on this
forum. He has given more free advice than 99% of the posters around here.
While his reply may have been blunt it certaily was not over the line. It is
not obviouls from your code what you are wanting to accomplish. It is obvious
from your code that you are not comforatble in VBA. A brief description of
what you want to do would be helpful...
--
HTH...

Jim Thomlinson


"stacia" wrote:

Do you give everyone a hard time or just those who won't pay your fees. This
forum is for those of us who have questions. Sorry not a VBA expert, just a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.

That's interesting seeing I have an e-mail from him telling me to attach my
file and e-mail it. I then received a return e-mail that his fees were
$75.00/hour.
--
Stacia


"Jim Thomlinson" wrote:

I have never seen Don request any fees for the advice he has supplied on this
forum. He has given more free advice than 99% of the posters around here.
While his reply may have been blunt it certaily was not over the line. It is
not obviouls from your code what you are wanting to accomplish. It is obvious
from your code that you are not comforatble in VBA. A brief description of
what you want to do would be helpful...
--
HTH...

Jim Thomlinson


"stacia" wrote:

Do you give everyone a hard time or just those who won't pay your fees. This
forum is for those of us who have questions. Sorry not a VBA expert, just a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.

I was unaware that Don was soliciting clients from this site. The more
clearly you post your requirements the better an answer that we can give you.
Often if we know what the end goal is we will be able to suggest a very
simple approach that is less error prone than your original efforts might
have been. You did the correct thing when you posted your code. What was
missing was a description of what you wanted it to accomplish.
--
HTH...

Jim Thomlinson


"stacia" wrote:

That's interesting seeing I have an e-mail from him telling me to attach my
file and e-mail it. I then received a return e-mail that his fees were
$75.00/hour.
--
Stacia


"Jim Thomlinson" wrote:

I have never seen Don request any fees for the advice he has supplied on this
forum. He has given more free advice than 99% of the posters around here.
While his reply may have been blunt it certaily was not over the line. It is
not obviouls from your code what you are wanting to accomplish. It is obvious
from your code that you are not comforatble in VBA. A brief description of
what you want to do would be helpful...
--
HTH...

Jim Thomlinson


"stacia" wrote:

Do you give everyone a hard time or just those who won't pay your fees. This
forum is for those of us who have questions. Sorry not a VBA expert, just a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.

Thank you
--
Stacia


"Jim Thomlinson" wrote:

I was unaware that Don was soliciting clients from this site. The more
clearly you post your requirements the better an answer that we can give you.
Often if we know what the end goal is we will be able to suggest a very
simple approach that is less error prone than your original efforts might
have been. You did the correct thing when you posted your code. What was
missing was a description of what you wanted it to accomplish.
--
HTH...

Jim Thomlinson


"stacia" wrote:

That's interesting seeing I have an e-mail from him telling me to attach my
file and e-mail it. I then received a return e-mail that his fees were
$75.00/hour.
--
Stacia


"Jim Thomlinson" wrote:

I have never seen Don request any fees for the advice he has supplied on this
forum. He has given more free advice than 99% of the posters around here.
While his reply may have been blunt it certaily was not over the line. It is
not obviouls from your code what you are wanting to accomplish. It is obvious
from your code that you are not comforatble in VBA. A brief description of
what you want to do would be helpful...
--
HTH...

Jim Thomlinson


"stacia" wrote:

Do you give everyone a hard time or just those who won't pay your fees. This
forum is for those of us who have questions. Sorry not a VBA expert, just a
little old accountant who would love to audit you.
--
Stacia


"Don Guillett" wrote:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"stacia" wrote in message
...
This works for the first cell that has title "Shop Report", but it will
not
continue to the next and to the end

Sub b10shop()


Dim MyRange As Range
Dim MyCell As Range
Dim EndRow As Integer

EndRow = Range("E65536").End(xlUp).Row
Set MyRange = Range("E1:E" & EndRow)
MyRange.Select
On Error Resume Next
For Each MyCell In MyRange
If MyCell.value = "Shop" Then
Cells.Find(What:="Shop Report", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-4]"
End If
Next MyCell
End Sub


--
Stacia

.