Posts by reggieneo

    Hi All,
    Kindly assist to create or teach a suitable formula for this situation : the answer should be "Curtain_簾" (the value in C6).


    I need a formula to look for a closest string match in column C from strings in A1.
    A1 value is constantly changing or dynamic.


    I have tried index match but this is too complicated for me to crack. [TABLE="border: 1, cellpadding: 1, width: 800"]

    [tr]


    [td]

    A1

    [/td]


    [td]

    CorpComm Office Curtain Project 二樓金沙中國 辦公室的公共關係部需要造窗簾

    [/td]


    [td]

    Accessories_飾品

    [/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]Curtain_簾

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    A2

    [/td]


    [td][/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]Air Compressor_空氣壓縮機

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    A3

    [/td]


    [td][/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]BandSaw_帶鋸

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    A4

    [/td]


    [td][/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]Base_腳

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    A5

    [/td]


    [td][/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]Cabinet_內閣

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    A6

    [/td]


    [td][/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"] [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]Curtain_簾

    [/tr][/tr][/td][tr][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    A7

    [/td]


    [td][/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 253"]Door_大門

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [/TABLE]
    much thanks,
    reggieneo

    Hi All,




    there are 4 and 8 (the value in cells) but my sumfis can't extract hem


    please see formula:


    =SUMIFS(C6:C10,A6:A10,"="&A6,D6:D10," = "&E6 Suppose answer is 12


    I also tried removing the ampersand but still 0. I have double checked all the values, are in general. Tried formatting as numbers too.
    I can't make this formula work and there is no error . [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [/tr]


    [tr]


    [td]

    39751

    [/td]


    [td]

    1245

    [/td]


    [td]

    4

    [/td]


    [td]

    5/18/18

    [/td]


    [td]

    5/18/18

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    39751

    [/td]


    [td]

    1245

    [/td]


    [td]

    8

    [/td]


    [td]

    5/18/18

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]
    kindly assist

    HI All,


    Please help:


    look for all 2 matching values in sheet, these 2 criteria must be met with the same structure which is 2 rows (one top and below of each other). Then sum the row values within the range of where the match will occur of these two combined criteria values (b to f or the 4th column after the criteria):


    CRITERIA: 12345 (top row)


    apple (bottom row)


    SUM RANGE: F1 TO F19


    ANSWER: 210




    sum range: all rows in the 4th Colum after the criteria column (in this case is F column)




    Note: only sum b1 and b2 values that has b3 blank. if b3 has value then should not be in the sum.


    please see table as attached. the return value must be 210. F20 TO F23 NOT INCLUDED SINCE THEY HAVE VALUE BELOW THE "apple".






    Thank you all.
    reggieneo

    Re: Populating cell if matches found from input box value


    Hello,
    I have added the code tags.
    I have changed the target cell in the code you wrote for me.


    this below is the codes in 2 separate modules that make the other worksheet change activate:


    _________________________________________________________________________-


    this is the input box that places the E6 to A1:



    ------------------------------------------------------------------------------------------------------


    the 2 worksheet change including the one you wrote (i have modified):






    basically, i need to make the column A "the target column", be automatically filled from E6, which the value comes from inputbox, that eventually run the timer.
    my arrangement fires sometimes, sometimes dont.





    thanks,
    reggieneo

    Re: Populating cell if matches found from input box value


    Hello,
    I have added the code tags.
    I have changed the target cell in the code you wrote for me.


    this below is the codes in 2 separate modules that make the other worksheet change activate:


    _________________________________________________________________________-


    this is the input box that places the E6 to A1:


    VB:
    Code:
    Sub ClickToStartWorkOrderInputBox()
    Dim Click As Long
    Dim TM As String
    TM = InputBox("Please input your TM number or Scan your TM ID")
    Range("E6").Value = TM
    UserForm1.Show
    End Sub


    Sub MyTimeCounter()
    Sub startCounter()
    Application.OnTime Now + TimeValue("00:00:0001"), _
    "Reduce_Count_By_1"
    End Sub


    VB:
    Code:
    Sub Reduce_Count_By_1()
    Dim x, LastRow
    LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    For x = 1 To LastRow



    If ActiveSheet.Cells(x, "A").Value <> "" And _
    ActiveSheet.Cells(x, "B").Value > 0 Then
    ActiveSheet.Cells(x, "B").NumberFormat = "m:ss"
    ActiveSheet.Cells(x, "B").Value = ActiveSheet.Cells(x, "B").Value - (1 / 86400)
    LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    End If



    If ActiveSheet.Cells(x, "A").Value <> "" And _
    ActiveSheet.Cells(x, "B").Value < 0 Or ActiveSheet.Cells(x, "C").Value = "Done" Or ActiveSheet.Cells(x, "C").Value = "Done" Then
    ActiveSheet.Cells(x, "B").NumberFormat = "m:ss"
    ActiveSheet.Cells(x, "B").Value = "0:00"



    End If



    LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    Next
    startCounter
    End Sub



    ------------------------------------------------------------------------------------------------------


    the 2 worksheet change including the one you wrote (i have modified):


    VB:
    Code:
    Private Sub Worksheet_Change1(ByVal Target As Range)
    On Error Resume Next
    If Target.Column = 1 Then
    Target.Offset(0, 1).Value = 0 Then
    Target.Offset(0, 1).Value = ""
    End If
    If Target.Value <> "" Then
    Target.Offset(0, 1).NumberFormat = "m:ss"
    Target.Offset(0, 1).Value = "00:08:00.00"
    End If
    End If
    End Sub


    VB:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("E6")) Is Nothing Then Exit Sub
    'Range("D1:D10").ClearContents
    For Each r In Range("H3:H15")
    If r.Value = Target.Value Then
    Application.EnableEvents = False
    r.Offset(, -7).Value = r.Value
    Application.EnableEvents = True
    End If
    Next r
    End Sub






    thanks,
    reggieneo

    Re: Populating cell if matches found from input box value


    Thanks for replying Skywriter.


    I can't make these two worksheet change work together.



    thanks again.
    reggieneo

    Hi All,
    I have an inputbox that giving value in cell A1.
    Column B1:B10 has ID numbers.
    I am trying to populate D1:D10 with same row of matching values in B1:B10.
    Basically, If cell A1 (from input box) has ID12345 and ID12345 present location is in B4 then D4 must have the ID12345 too. It cannot have formula in D column hence i am trying to do it in vba.



    kindly asssit.
    reggieneo

    Re: Loop clearcontents


    Hi Kjbox.
    Apologies for responding late.
    I figured it out thru navigating online help and made some adjustment to SMC's suggestion. thanks much.


    However this takes more than a minute to complete running and I am wondering if I could make it faster. Please see below.
    Sub Vba_for_clear_contents()
    Dim ClearValue As String
    Dim LongLoop As Long
    Dim refValue As Variant
    Dim ClearValueEL As Variant
    Dim ClearValueN As Long
    Dim ClearValueO As Double
    Application ScreenUpdating = False

    For LongLoop = 6 To 81
    refValue = Sheets(ActiveSheet.Index - 1).Range("F" & LongLoop).Value
    ClearValueEL = Sheets(ActiveSheet.Index - 1).Range("E" & LongLoop & ":L" & LongLoop).Value

    Clear_valueN = Sheets(ActiveSheet.Index - 1).Range("N" & LongLoop).Value

    Clear_valueO = Sheets(ActiveSheet.Index - 1).Range("O" & LongLoop).Value


    If (refValue Like "*week*") Or (refValue Like "*Batch*") Or (ClearValueO > 1.1) Then


    ActiveSheet.Range("E" & LongLoop & ":L" & LongLoop).Value = Sheets(ActiveSheet.Index - 1).Range("E" & LongLoop & ":L" & LongLoop).Value
    ActiveSheet.Range("N" & LongLoop).Value = Sheets(ActiveSheet.Index - 1).Range("N" & LongLoop)
    Else


    ActiveSheet.Range("E" & LongLoop & ":L" & LongLoop).ClearContents
    ActiveSheet.Range("N" & LongLoop).ClearContents
    End If
    Next LongLoop
    Application ScreenUpdating = True
    End Sub

    Re: Loop clearcontents


    Quote from KjBox;798477

    You could be right, as I saw it the OP needed to clear the contents of 81 rows if F6 was equal to the ClearVal variable and O6 was greater than 1.1.


    It is not specified if those conditions applied to columns F & O of all 81 rows or if it is just a one off condition for F6 & O6.


    Hi SMC and KjBox,
    I think I made a mistake when I used "OR" and "AND" with my previous "clearcontents".




    my intents were:
    If F6 string of text has either Week or Batch (case insensitive) and o6 is greater than1.1 then those should stay in the sheet in the same location (range).
    clear contents to all rows of F6 to L6 and N6 (skipping M6) then test the following rows down until 81st rows and clear the contents as well if those mentioned conditions are met.
    finally msgbox should put new Work Order to those cells which are just content cleared.

    appreciate your reply help on this.


    Much thanks,
    reggieneo

    Hi All,
    I am having trouble looping clearContents method.


    I am aiming to clear horizontal ranges of cells ( E6:L6&N6) to which I name "ToClear" if conditions are met. I have managed to clear contents but I have until row 81 to clear. Could you please assist on this?





    Kind Reagrd,
    Reggieneo