Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Copy Table Column & Row Names When Codition Is Met
May Jun Jul
Apple No Yes No Pear Yes No Yes Orange Yes Yes No 1) I would like to use a macro to go through the above table (using I think a range, a loop and a if) and for every "Yes" I want to copy out the "Fruit Name" & " the "Month Name" into seperate columns in another worksheet (example below) Apple Jun Pear May Pear Jul Orange May Orange Jun 2) My actual data table is larger and may expanded in both directions. If I define a name for the range in Excel, can I use this named range in a macro? Many thanks in advance for any help possible Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Copy Table Column & Row Names When Codition Is Met
You can use a named range from worksheet
Replace FindRange below with Range("WorksheetName"). You need the double quotes around the name. The code below automatically finds size of table if table. change StartCell if the table starts in another location. Sub getfruit() StartCell = "A1" With Sheets("Sheet1") LastRow = .Range(StartCell).Offset(1, 0).End(xlDown).Row LastColumn = .Range(StartCell).Offset(0, 1).End(xlToRight).Column Set FindRange = .Range(.Range(StartCell).Offset(1, 1), _ .Cells(LastRow, LastColumn)) End With RowCount = 1 For Each cell In FindRange If cell = "Yes" Then Fruit = Cells(cell.Row, "A") FMonth = Cells(1, cell.Column) With Sheets("Sheet2") .Range("A" & RowCount) = Fruit .Range("B" & RowCount) = FMonth RowCount = RowCount + 1 End With End If Next cell End Sub "steven.holloway" wrote: May Jun Jul Apple No Yes No Pear Yes No Yes Orange Yes Yes No 1) I would like to use a macro to go through the above table (using I think a range, a loop and a if) and for every "Yes" I want to copy out the "Fruit Name" & " the "Month Name" into seperate columns in another worksheet (example below) Apple Jun Pear May Pear Jul Orange May Orange Jun 2) My actual data table is larger and may expanded in both directions. If I define a name for the range in Excel, can I use this named range in a macro? Many thanks in advance for any help possible Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Copy Table Column & Row Names When Codition Is Met
Thanks Joel, this was exactly what I requested, I can now adjust to my needs.
Thanks again and have a great Xmas/New Year. Steve "Joel" wrote: You can use a named range from worksheet Replace FindRange below with Range("WorksheetName"). You need the double quotes around the name. The code below automatically finds size of table if table. change StartCell if the table starts in another location. Sub getfruit() StartCell = "A1" With Sheets("Sheet1") LastRow = .Range(StartCell).Offset(1, 0).End(xlDown).Row LastColumn = .Range(StartCell).Offset(0, 1).End(xlToRight).Column Set FindRange = .Range(.Range(StartCell).Offset(1, 1), _ .Cells(LastRow, LastColumn)) End With RowCount = 1 For Each cell In FindRange If cell = "Yes" Then Fruit = Cells(cell.Row, "A") FMonth = Cells(1, cell.Column) With Sheets("Sheet2") .Range("A" & RowCount) = Fruit .Range("B" & RowCount) = FMonth RowCount = RowCount + 1 End With End If Next cell End Sub "steven.holloway" wrote: May Jun Jul Apple No Yes No Pear Yes No Yes Orange Yes Yes No 1) I would like to use a macro to go through the above table (using I think a range, a loop and a if) and for every "Yes" I want to copy out the "Fruit Name" & " the "Month Name" into seperate columns in another worksheet (example below) Apple Jun Pear May Pear Jul Orange May Orange Jun 2) My actual data table is larger and may expanded in both directions. If I define a name for the range in Excel, can I use this named range in a macro? Many thanks in advance for any help possible Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a value from a table using Column and row names as input? | Excel Discussion (Misc queries) | |||
copy column and header names from existing spreadsheet to new spre | New Users to Excel | |||
lookup with more then one codition | Excel Worksheet Functions | |||
Macro, Copy Selected Cells Down a Column | Excel Discussion (Misc queries) | |||
Choosing a formula to used based on a codition fulfilled | Excel Discussion (Misc queries) |