Running macro when cell contains text

[Greg]

Hi
I wish to run a macro that will reformat a list,
then paste the values to a new set of cells on the same sheet.
I have recorded the macro with Relative Reference's and it works
perfectly for one line.
I have included the line at the start of the macro "For i = 1 to 100"
and at the end "next i" so that it runs down the 100 rows on the
sheet.
My problem is that I only want to run the macro if a cell within
column A contains text eg
A1= apple, then run the reformat macro.
A2= pear, don't run the macro.
A3= Blank cell,don't run the macro.
A4= apple, then run the reformat macro.
.........checking each row until the end of the sheet.
I have looked at many posts to find a solution. Can it be done this
way or should I be looking at a different approach to solve this.
Thanks
Greg

 

[Claud Balls]

Would something like this work? Please post what code you have, it will
make it easier to see what you are trying to do.

for i = 1 to 100
if range("A" & i) = "apple"
'format text code
'copy to new location code
end if
next i

 

[Greg]

Hi Claud
This is the macro I have recorded.
As you can see the information that I am refomatting is in a block of
information,
that is a few columns wide and four rows deep. It has a number of blank
lines between each block of information, my thought was for the macro
to look for the common cell in each block of information to trigger the
reformatting.
Thankyou
Greg

Sub reformatmainlist()
'
' reformatmainlist Macro
' Macro recorded 27/01/2005 by Greg
'

For i = 1 To 100
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -5).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -5).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -6).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 7).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -7).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-2, 8).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(3, -8).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-3, 9).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -10).Range("A1").Select
Next i
End Sub

 

[Greg]

Hi Claud
Sorry I have just gone back to look at my original post and realised
that the A1,A2,A3,A4 etc should have been B1,B2,B3,B4 etc
thanks
Greg

 

[Tom Ogilvy]

This checks the value in the selected cell and decides whether to format or
not, so select the cell you want checked before running.

Sub ABCDEF()
For i = 1 To 100
If LCase(ActiveCell.Value) = "apple" Then
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -5).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -5).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -6).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 7).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -7).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-2, 8).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(3, -8).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-3, 9).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -10).Range("A1").Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

 

[Greg]

Hi Tom
Thankyou for your info I will follow up this over the weekend.
Thanks
Greg

 

[Greg]

Hi Tom
Thankyou for your reply, I had a bit of trouble working out what was
going wrong as the macro would not reformat the list correctly. I
(after some time) worked out that some of the items I was wanting to
reformat were not in lowercase so I included a macro before yours to
first make the whole list lowercase. I found this one in another post.
Once again thankyou
Greg

Sub Lower_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Formula = LCase(Cell.Formula)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub