Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Executing Part Of Macro Only On Cells Meeting A Condition
I'm working on a spreadsheet that requires taking a total from a
specific column, adding that to another column, and placing the result in that column (see example below). I have the macro to execute this; however, I'm trying to modify it to take the action only on certain rows in the column specified that meet criteria in another column. This criteria would be designated either in the macro, or by a cell on the sheet. Here is an example of the code I have to total the cells that are numbers in column R, starting at row 8 for all the rows that have data in column T, and add the value in column R to the corresponding row's value in column T, then input the result in column T. For r = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(r, "R")) = 5 Then Cells(r, "t") = Cells(r, "t") + Cells(r, "R") End If Next r Here is the next macro to perform an action on cells in a column, based of criteria in cell "D1". In this case, it clears the contents in the cells nine rows left of the "W" column if the condition in cell "D1" is met. Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then c.Offset(, -9).ClearContents End If Next When I try to combine the two as below, the macro does not respect the condition of the cell in "D1", and it adds the value in column "R" to the corresponding row's value in column "T" as many times as the condition in cell "D1" is met in column "W". Sub Year_Skip() Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then For r = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(r, "R")) = 5 Then Cells(r, "t") = Cells(r, "t") + Cells(r, "R") End If Next End If Next End Sub Assume I have data in the columns / rows as below: Cell D1: the text D1 Col.___|__R__|...|__T__|...|__W__| Row 8_|__18__...|_12__|...|__D1_| Row 9_|__18__...|_12__|...|__D1_| Row 10|__18__...|_12__|...|__D1_| Row 11|__18__...|_12__|...|__D1_| Row 12|__18__...|_12__|...|_SST_| Row 13|__18__...|_12__|...|_SST_| Row 14|__18__...|_12__|...|__M1_| (*assume irrelevant data stored in columns not listed or skiped) The macro should find any cells in column R that have a number value (the number of rows can change; no set range), determine if the value in the corresponding row & column of W meets the text input in cell D1; and if so, add the total from column R to column T, placing the result in column T at the appropriate row (overwriting the previous value in column T) while leaving any other rows not matchng the condition in cell D1 and column W alone - no change in any of the cells in that row. Also, this macro will be only part of (in) a much larger macro - if that makes a difference. If more explanation is needed, please let me know. Thank you for your time! CVinje |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Executing Part Of Macro Only On Cells Meeting A Condition
Hi there,
Modify your sub as follows: Sub Year_Skip() Dim MyRange As Range, MyText As String, LastRow, c MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then If VarType(Cells(c.Row, "R")) = 5 Then Cells(c.Row, "t") = Cells(c.Row, "t") + Cells(c.Row, "R") End If End If Next c End Sub Hope this solves it! "CVinje" wrote in message ... I'm working on a spreadsheet that requires taking a total from a specific column, adding that to another column, and placing the result in that column (see example below). I have the macro to execute this; however, I'm trying to modify it to take the action only on certain rows in the column specified that meet criteria in another column. This criteria would be designated either in the macro, or by a cell on the sheet. Here is an example of the code I have to total the cells that are numbers in column R, starting at row 8 for all the rows that have data in column T, and add the value in column R to the corresponding row's value in column T, then input the result in column T. For r = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(r, "R")) = 5 Then Cells(r, "t") = Cells(r, "t") + Cells(r, "R") End If Next r Here is the next macro to perform an action on cells in a column, based of criteria in cell "D1". In this case, it clears the contents in the cells nine rows left of the "W" column if the condition in cell "D1" is met. Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then c.Offset(, -9).ClearContents End If Next When I try to combine the two as below, the macro does not respect the condition of the cell in "D1", and it adds the value in column "R" to the corresponding row's value in column "T" as many times as the condition in cell "D1" is met in column "W". Sub Year_Skip() Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then For r = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(r, "R")) = 5 Then Cells(r, "t") = Cells(r, "t") + Cells(r, "R") End If Next End If Next End Sub Assume I have data in the columns / rows as below: Cell D1: the text D1 Col.___|__R__|...|__T__|...|__W__| Row 8_|__18__...|_12__|...|__D1_| Row 9_|__18__...|_12__|...|__D1_| Row 10|__18__...|_12__|...|__D1_| Row 11|__18__...|_12__|...|__D1_| Row 12|__18__...|_12__|...|_SST_| Row 13|__18__...|_12__|...|_SST_| Row 14|__18__...|_12__|...|__M1_| (*assume irrelevant data stored in columns not listed or skiped) The macro should find any cells in column R that have a number value (the number of rows can change; no set range), determine if the value in the corresponding row & column of W meets the text input in cell D1; and if so, add the total from column R to column T, placing the result in column T at the appropriate row (overwriting the previous value in column T) while leaving any other rows not matchng the condition in cell D1 and column W alone - no change in any of the cells in that row. Also, this macro will be only part of (in) a much larger macro - if that makes a difference. If more explanation is needed, please let me know. Thank you for your time! CVinje |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Executing Part Of Macro Only On Cells Meeting A Condition
Awesome! This seems to work during rough draft usage. Thanks so much, I was
really having a hard time trying to get it to work. I really appreciate your time! CVinje "Howard31" wrote: Hi there, Modify your sub as follows: Sub Year_Skip() Dim MyRange As Range, MyText As String, LastRow, c MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then If VarType(Cells(c.Row, "R")) = 5 Then Cells(c.Row, "t") = Cells(c.Row, "t") + Cells(c.Row, "R") End If End If Next c End Sub Hope this solves it! "CVinje" wrote in message ... I'm working on a spreadsheet that requires taking a total from a specific column, adding that to another column, and placing the result in that column (see example below). I have the macro to execute this; however, I'm trying to modify it to take the action only on certain rows in the column specified that meet criteria in another column. This criteria would be designated either in the macro, or by a cell on the sheet. Here is an example of the code I have to total the cells that are numbers in column R, starting at row 8 for all the rows that have data in column T, and add the value in column R to the corresponding row's value in column T, then input the result in column T. For r = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(r, "R")) = 5 Then Cells(r, "t") = Cells(r, "t") + Cells(r, "R") End If Next r Here is the next macro to perform an action on cells in a column, based of criteria in cell "D1". In this case, it clears the contents in the cells nine rows left of the "W" column if the condition in cell "D1" is met. Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then c.Offset(, -9).ClearContents End If Next When I try to combine the two as below, the macro does not respect the condition of the cell in "D1", and it adds the value in column "R" to the corresponding row's value in column "T" as many times as the condition in cell "D1" is met in column "W". Sub Year_Skip() Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then For r = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(r, "R")) = 5 Then Cells(r, "t") = Cells(r, "t") + Cells(r, "R") End If Next End If Next End Sub Assume I have data in the columns / rows as below: Cell D1: the text D1 Col.___|__R__|...|__T__|...|__W__| Row 8_|__18__...|_12__|...|__D1_| Row 9_|__18__...|_12__|...|__D1_| Row 10|__18__...|_12__|...|__D1_| Row 11|__18__...|_12__|...|__D1_| Row 12|__18__...|_12__|...|_SST_| Row 13|__18__...|_12__|...|_SST_| Row 14|__18__...|_12__|...|__M1_| (*assume irrelevant data stored in columns not listed or skiped) The macro should find any cells in column R that have a number value (the number of rows can change; no set range), determine if the value in the corresponding row & column of W meets the text input in cell D1; and if so, add the total from column R to column T, placing the result in column T at the appropriate row (overwriting the previous value in column T) while leaving any other rows not matchng the condition in cell D1 and column W alone - no change in any of the cells in that row. Also, this macro will be only part of (in) a much larger macro - if that makes a difference. If more explanation is needed, please let me know. Thank you for your time! CVinje |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple cells meeting a condition | Excel Worksheet Functions | |||
Count filtered cells meeting a condition | Excel Discussion (Misc queries) | |||
Determine number of rows meeting a condition | Excel Discussion (Misc queries) | |||
Hide Rows meeting condition Amendment | Excel Programming | |||
Count of unique items meeting condition | Excel Worksheet Functions |