NEXT

S

stacia

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
 
F

FSt1

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
 
J

Jim Thomlinson

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...
 
S

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 said:
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 said:
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
 
S

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 said:
Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
stacia said:
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

.
 
F

Fred Smith

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 said:
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 said:
Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
stacia said:
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

.
 
D

Don Guillett

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
(e-mail address removed)
stacia said:
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 said:
Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
stacia said:
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

.
 
J

Jim Thomlinson

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 said:
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 said:
Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
stacia said:
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

.
 
S

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 said:
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 said:
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 said:
Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 
J

Jim Thomlinson

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 said:
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 said:
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 said:
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


:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 
G

george

Thank you
--
Stacia


Jim Thomlinson said:
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 said:
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 said:
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


:

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


:

Convoluted. What do you want to do.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top