Checking every row

[somenoob]

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

 

[somenoob]

would like to ask, if there is no ->chk_ found as in no values are bigger than 0, when i run the macro it gave me an error message : 400.

is there a way to show a message saying there is no values bigger than 0?

 

[somenoob]

is it possible to be like, if there is no values bigger than 0, there will be no ->chk_, then there will be a text in cell F28 saying 'there is no values bigger than 0' ?

 

[villy]

Sorry for late reply been busy on weekend...
Anyway

Sub AddNewSheet()   'Function to Add New Sheet based on number of Chk_
Call check
If Sheets("List").Range("E17:E200").Text = "" Then 'Change range to suit
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("List").Range("F28").Value = "Sorry, There is no value bigger than 0"    'Change text as you like
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Else
ls = Sheets("List").Cells(Rows.Count, 5).End(xlUp).Row
cnt = WorksheetFunction.CountA(Sheets("List").Range("E17:E" & ls))
For i = Sheets.Count - 1 To 2 Step -1
Application.DisplayAlerts = False
    Sheets(i).Delete
Next
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 If
Application.DisplayAlerts = True
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
Application.ScreenUpdating = False
Application.EnableEvents = False
With WorksheetFunction.Application
i = 1
k = 17
x = 16
Do
    For j = k To (i * 37) + 16
        If Len(ws1.Range("E" & j)) > 0 Then
            xn = .Match(ws1.Range("E" & j), ws1.Range("E:E"), 0)
            ls = Sheets("List").Cells(Rows.Count, 5).End(xlUp).Row
            'MsgBox i
            'MsgBox xn
            Select Case xn
                Case Is <= (i * 37) + 16
                    For Each ws In Worksheets
                        lr = Sheets("List").Cells(Rows.Count, 3).End(xlUp).Row
                        'MsgBox ws.Name
                        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("B17")
                                    Exit For
                                Case Is <= 12
                                    ws.Range("A13").Value = ws1.Range("B23")
                                    Exit For
                                Case Is <= 18
                                    ws.Range("A13").Value = ws1.Range("B29")
                                    Exit For
                                Case Is <= 24
                                    ws.Range("A13").Value = ws1.Range("B35")
                                    Exit For
                                Case Is <= 30
                                    ws.Range("A13").Value = ws1.Range("B41")
                                    Exit For
                                Case Is <= 36
                                    ws.Range("A13").Value = ws1.Range("B47")
                                    Exit For
                            End Select
                        End If
                    Next
            End Select
        End If
        lr = lr + 1
    Next
    i = i + 1
    x = x + 37
    k = j
    'MsgBox i
    'MsgBox x
Loop Until (j = lr + 2)
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Function

 

[RAMU]

You could put this formula in E2 and drag downward

=REPT(IF($C2="Final Value", "->chk_" & (1+COUNTIF(E$1:E1,"?*")), ""), --(N($D2)>0))

Dear Mr. Mike,

Nice solution. Hats off !!!!

Regards
RAMU

 

[somenoob]

thanks it works!

 

[somenoob]

is it possible to create a month dropdownlist in coding?

in cell D16 of 'List', i would like to have a dropdown list of the months. when i click on cell D16, a drop down list will appear with the months, and i can click on them.

 

[villy]

Actually you don't need coding to make a dropdown list.. Just crate a data validation>List
But you have to have your value(months) somewhere in any sheets

 

[somenoob]

i see.. thanks for the help

 

[somenoob]

Hi everyone, can anyone help me with this, i have in my original spreadsheet 'List'. In the picture below, it shows the layout of how 'List' looks.

i need a macro that checks the pattern. If the ->chk is beside OGL_Unreconciled, for e.g. in cell D19 there is a ->chk_1 there. i will need a macro that will take in the values from C17:C19 and place it in a new spreadsheet created from another macro in cell G12:I12.

If however the ->chk_ is beside OPS_Unreconciled, i need the macro to get the data from C20:C22 and place it in the new spreadsheet G12:I12.