Checking every row

somenoob

Board Regular
Joined
Sep 19, 2011
Messages
100
is it possible to check every row with the text 'Final Value' for values not equals to 0?

in my excel,
<TABLE style="WIDTH: 630pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=838 x:str><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 4835" width=170><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 5461" width=192><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 7338" width=258><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 3299" width=116><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 2901" width=102><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 128pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=20 width=170 x:str="'EXT_SERVICE_COST">SERVICE_COST</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; WIDTH: 144pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=192 x:str="'4003 Accident Repair">4003 Repair</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; WIDTH: 194pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=258 x:str="'DS_Maximo-OGL">Maximo</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; WIDTH: 87pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=116 x:str="'0">0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=102></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 x:str="'DS_OGL-MX">OGL</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 x:str="'0">0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=21></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 x:str="'DS_Maximo-OGL_Unreconciled">Final Value </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 x:str="'0">0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 x:str="'DS_Maximo-OPS">Maximo</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right x:num>148</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 x:str="'DS_OPS-MX">OPS</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right x:num>149</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=21></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 x:str="'DS_Maximo-OPS_Unreconciled"> Final Value </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 x:num="70"> 1 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 x:str="'->chk_1">->chk_1</TD></TR></TBODY></TABLE>

i would like to have a macro that checks the 'final value' that is not equals to 0. and if there is a value that is not equals to 0, put a ->chk_1 beside the cell. If there is some more values in the 'final value' not equals to 0, place ->chk_2 and ->chk_3 and so on beside the cell.

Please help me
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Also when i check the values, i want it to check for values not equals to 0. Values such as symbols (like '-') and negative values i want to ignore. i do not want to place a ->chk beside the cell for symbols and negative values. just values higher than 0.
 
Upvote 0
To Specify clearer,

Cell: A (Service_Cost) Cell:B (4003 Repair) Cell:C (Maximo) Cell:D (0) Cell:E (chk_1)
<TABLE style="WIDTH: 630pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=838 x:str><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP-COLOR: #ece9d8; WIDTH: 128pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl70 height=20 width=170 x:str="'EXT_SERVICE_COST">SERVICE_COST</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; WIDTH: 144pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 width=192 x:str="'4003 Accident Repair">4003 Repair</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; WIDTH: 194pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 width=258 x:str="'DS_Maximo-OGL">Maximo</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; WIDTH: 87pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 width=116 x:str="'0">0</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 77pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" width=102></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 x:str="'DS_OGL-MX">OGL</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 x:str="'0">0</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=21></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl72 x:str="'DS_Maximo-OGL_Unreconciled">Final Value </TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl69 x:str="'0">0</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 x:str="'DS_Maximo-OPS">Maximo</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 align=right x:num>148</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 x:str="'DS_OPS-MX">OPS</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl67 align=right x:num>149</TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=21></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl72 x:str="'DS_Maximo-OPS_Unreconciled">Final Value </TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl68 x:num="70">1 </TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl71 x:str="'->chk_1">->chk_1</TD></TR></TBODY></TABLE>
 
Upvote 0
This seems to work, though others will likely come up with a more efficient solution (I'm a macro-newbie).

It assumes that your text (e.g., "Final Value") is in column A, values are in Column B, and you want the check total text in Column C.

Code:
Sub check()
Dim x As Long
Dim counter As Long
Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
counter = 1
For x = 1 To lastrow
    If Range("A" & x).Value = "Final Value" And IsNumeric(Range("B" & x).Value) And Range("B" & x).Value >= 0 _
        Then Range("C" & x).Value = "->chk_" & counter
    If Range("A" & x).Value = "Final Value" And IsNumeric(Range("B" & x).Value) And Range("B" & x).Value >= 0 _
        Then counter = counter + 1
Next x
End Sub
 
Upvote 0
wow it works. but there is a little problem.

when i run the macro, there is the ->chk_1, ->chk_2...in column E beside all the final value. whether is it above 0, beside the symbol (-), beside negative values and also beside a value with " '0 " . please help
 
Upvote 0
Try:
Code:
Sub check()
Dim x As Long
Dim counter As Long
Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
counter = 1
For x = 1 To lastrow
    If Range("C" & x) = "Final Value" And Val(Range("D" & x)) > 0 Then
        Range("E" & x).Value = "->chk_" & counter
        counter = counter + 1
    End If
Next x
End Sub
 
Upvote 0
You could put this formula in E2 and drag downward

=REPT(IF($C2="Final Value", "->chk_" & (1+COUNTIF(E$1:E1,"?*")), ""), --(N($D2)>0))
 
Upvote 0
Try:
Code:
Sub check()
Dim x As Long
Dim counter As Long
Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
counter = 1
For x = 1 To lastrow
    If Range("C" & x) = "Final Value" And Val(Range("D" & x)) > 0 Then
        Range("E" & x).Value = "->chk_" & counter
        counter = counter + 1
    End If
Next x
End Sub

Thanks for the codes but i am still having the problem of the chk_ beside every "final value". symbols and negatives and 0 values are not suppose to have chk_ beside them
 
Upvote 0
I checked it many times in here and I don't have any error...Output as you described.. Chk_ only shows up when >0 value od D and with final value in C.
Copy paste this code then check your input in C I noticed that your inout is "Final Value "... Notice the space after value..
Thanks
 
Upvote 0
Thanks for the codes but i am still having the problem of the chk_ beside every "final value". symbols and negatives and 0 values are not suppose to have chk_ beside them

nvm i think it has been resolved. But if i have two rows to check, 'Final Value' and 'Final Amount'. then how do i edit the codes?

I have also noticed that if i change the values from 0 to a number like 10, the chk_ comes up beside it. But If i change it back to 0 and run the macros, the chk_ does not go away.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,570
Messages
6,179,610
Members
452,931
Latest member
The Monk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top