Executing Part Of Macro Only On Cells Meeting A Condition

[CVinje]

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

 

[Howard31]

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]

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