Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
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 -- Regards, Tom Ogilvy "Greg" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
Hi Tom
Thankyou for your info I will follow up this over the weekend. Thanks Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro when cell contains text
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Running Out of Cell | Excel Discussion (Misc queries) | |||
Running a macro from currently selected cell? | Excel Discussion (Misc queries) | |||
Running Macro when a cell value changes | Excel Discussion (Misc queries) | |||
Running a macro on cell value change | Excel Discussion (Misc queries) | |||
Running a macro when a cell value changes | Excel Programming |