Checking every row

S

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
 
Sort by date Sort by votes
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
    Else
        Range("E" & x).Value = ""    
End If
Next x
End Sub


</PRE>
I added some bit of code assuming that you previously have Chk_ value before macro runs...
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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" Or Range("C" & x) = "Final Amount") And Val(Range("D" & x)) > 0 Then
        Range("E" & x).Value = "->chk_" & counter 
        counter = counter + 1
    Else
        Range("E" & x).Value = ""    
End If
Next x
End Sub
 
Upvote 0
Nice it works perfectly. If i want to create a new spreadsheet on the total number of chk_ there is, how do i do that?

like for example, i have run the macro you gave me, i have a total of 6 chk_.
then i run the macro to create new spreadsheet, it should automatically create 6 new spreadsheet with the name Chk_1 to Chk_6.

the codes to create the new spreadsheet i have is..

Sub newsheet()
'creates new spreadsheet
For i = 1 To Sheets.Count - 1
nem = "Chk_" & i
On Error Resume Next
If Sheets(nem) Is Nothing Then
Sheets.Add after:=Sheets(i)
Sheets(i + 1).Name = nem
 
Upvote 0
I've noticed that I did the code for creating new spreadsheet with chk_. I never thought it was you then...

Anyway Try this:
Code: 
Sub AddNewSheet()
cnt = WorksheetFunction.CountA(Sheets("List").Range("E:E"))       'change the name of the sheet to suit yours 
For num = 1 To cnt   'If you have header for Col E then change to cnt-1
    nem = "Chk_" & num
    On Error Resume Next
    If Sheets(nem) Is Nothing Then
        Sheets.Add after:=Sheets(num)
        Sheets(num + 1).Name = nem
    End If
Next
End Sub
 
Last edited:
Upvote 0
haha. yes it was me. and thanks alot for the codes.
it worked.

1-1.jpg

2.jpg


in the image, i want to copy the text service_cost to cell A2 of Chk_1 spreadsheet and 4003 Accident Repair to cell A13.

And in chk_2 i want to copy Material_cost and 4004 corrective mtce to new spreadsheet Chk_2.

how do i do that?

As the chk_ appears differently under different scenario as in chk_2 may be under Service_cost and not under material_cost, i don't know if it is possible to automatically copy the values for me to the new spreadsheet. i do not wish to manually copy the text to the new spreadsheet. is it possible?
 
Last edited:
Upvote 0
Is this a pattern where it is the same for all..I mean do you always have 21 rows in one attribute like service_cost, 21 rows for material_cost? Is it always the same format?
 
Upvote 0
there is always 36 rows in service_cost, there are 5 headers: service_cost, material_cost, labour_cost, ext_labour_cost and other_cost. all of those have 36 rows.

4003 accident repair, 4004 corrective mtce.... and many more all have 6 rows each.
 
Upvote 0
OK try: This is a whole program calling each function to work... Just run Sub AddNewSheet
Code: 
Sub AddNewSheet()   'Function to Add New Sheet based on number of Chk_
Call check
cnt = WorksheetFunction.CountA(Sheets("List").Range("E1:E184"))
For num = 1 To cnt
    nem = "Chk_" & num
    On Error Resume Next
    If Sheets(nem) Is Nothing Then
        Sheets.Add after:=Sheets(num)
        Sheets(num + 1).Name = nem
    End If
Next
Call Rng
End Sub
Private Function check() 'Function to Check for "Final Value" or "Final Amount"
Dim x As Long
Dim counter As Long
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Sheets("List")
lastrow = ws.UsedRange.Rows.Count
counter = 1
For x = 1 To lastrow
    If (ws.Range("C" & x) = "Final Value" Or ws.Range("C" & x) = "Final Amount") And Val(ws.Range("D" & x)) > 0 Then
        ws.Range("E" & x).Value = "->Chk_" & counter
        counter = counter + 1
    Else
        ws.Range("E" & x).Value = ""
End If
Next x
End Function
Private Function Rng()  'Function to Copy and Paste specific cells into corresponding sheets name
Dim ws, ws1 As Worksheet
Set ws1 = Sheets("List")
ws1.Select
With WorksheetFunction.Application
i = 1
k = 1
x = 0
Do
    For j = k To i * 37
        If Len(ws1.Range("E" & j)) > 0 Then
            xn = .Match(ws1.Range("E" & j), ws1.Range("E:E"), 0)
            Select Case xn
                Case Is <= i * 37
                    For Each ws In Worksheets
 
                        If Right(ws1.Range("E" & j), Len(ws1.Range("E" & j)) - 2) = ws.Name Then
                            ws.Range("A2").Value = ws1.Range("A" & x + 1)
                            Select Case xn - x
                                Case Is <= 6
                                    ws.Range("A13").Value = ws1.Range("B1")
                                    Exit For
                                Case Is <= 12
                                    ws.Range("A13").Value = ws1.Range("B7")
                                    Exit For
                                Case Is <= 18
                                    ws.Range("A13").Value = ws1.Range("B13")
                                    Exit For
                                Case Is <= 24
                                    ws.Range("A13").Value = ws1.Range("B19")
                                    Exit For
                                Case Is <= 30
                                    ws.Range("A13").Value = ws1.Range("B25")
                                    Exit For
                                Case Is <= 36
                                    ws.Range("A13").Value = ws1.Range("B31")
                                    Exit For
                            End Select
                        End If
                    Next
                Case Else
                    Exit For
            End Select
            lr = lr + 1
        End If
    Next
    i = i + 1
    x = x + 37
    k = j
Loop Until (j = lr + 2)
End With
End Function
Your data should be in sheet "List" oherwise change the name of sheet "List" in the program to suit yours
Thanks
 
Upvote 0
cnt = WorksheetFunction.CountA(Sheets("List").Range("E1:E184"))

this checks until the range of row 184?? my work exceeds row 184.

it ends at row 200. As it starts at row 17.

so i change it to:

cnt = WorksheetFunction.CountA(Sheets("List").Range("E17:E200")) ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
R
  • Question Question
Macro not working after adding two new items
Replies
5
Views
459
regan_2000
R
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,575
Messages
6,179,637
Members
452,934
Latest member
Jdsonne31

Share this page

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