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]
[/tr][/td][tr][/tr]
[TD="width: 253"]Curtain_簾
[/tr][tr][td]
[/TABLE]
[/TD]A2
[/td][td][/td][td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[tr]
[/tr][/td][tr][/tr]
[TD="width: 253"]Air Compressor_空氣壓縮機
[td][/td][/tr][tr][td]
[/TABLE]
[/TD]A3
[/td][td][/td][td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[tr]
[/tr][/td][tr][/tr]
[TD="width: 253"]BandSaw_帶鋸
[td][/td][/tr][tr][td]
[/TABLE]
[/TD]A4
[/td][td][/td][td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[tr]
[/tr][/td][tr][/tr]
[TD="width: 253"]Base_腳
[td][/td][/tr][tr][td]
[/TABLE]
[/TD]A5
[/td][td][/td][td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[tr]
[/tr][/td][tr][/tr]
[TD="width: 253"]Cabinet_內閣
[td][/td][/tr][tr][td]
[/TABLE]
[/TD]A6
[/td][td][/td][td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[tr]
[tr]
[TD="width: 253"] [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[/tr][/tr][/td][tr][tr][/tr]
[TD="width: 253"]Curtain_簾
[/tr]
[/TABLE]
[/TD]
[td][/td][/tr][tr][td]
[/TABLE]
[/TD]A7
[/td][td][/td][td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[tr]
[/tr][/td][tr][/tr]
[TD="width: 253"]Door_大門
[td][/td][/tr]
[/TABLE]
[/TD]
[/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.
[tr][td]
I can't make this formula work and there is no error . [TABLE="border: 1, cellpadding: 1, width: 500"]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:
Code
Display MoreSub 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 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):
Code
Display MorePrivate 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 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
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
Hi skywriter,
i have moved the code you wrote in the module and make it work by altering the “target” with “activecell”. The issue is : why sometimes it fires and sometimes dont?thanks
-
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 SubSub MyTimeCounter()
Sub startCounter()
Application.OnTime Now + TimeValue("00:00:0001"), _
"Reduce_Count_By_1"
End SubVB:
Code:
Sub Reduce_Count_By_1()
Dim x, LastRow
LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
For x = 1 To LastRowIf 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 IfIf 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 SubVB:
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 Subthanks,
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.
Code
Display MoreDim r As Range If Target.Count > 1 Then Exit Sub If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Range("D1:D10").ClearContents For Each r In Range("B1:B10") If r.Value = Target.Value Then Application.EnableEvents = False r.Offset(, 2).Value = r.Value Application.EnableEvents = True End If Next r End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Value <> "" Then Target.Offset(0, 1).Interior.ColorIndex = 0 Target.Offset(0, 1).NumberFormat = "m:ss" Target.Offset(0, 1).Value = "00:03:00.00" End If End Sub
thanks again.
reggieneo -
Re: Populating cell if matches found from input box value
Hi skywriter,
can i use this code into the regular module? Reason is i have worksheet change in this worksheet already.thanks
-
Re: Populating cell if matches found from input box value
Thank you very much skywriter!
-
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).ValueIf (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)
ElseActiveSheet.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;798477You 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?
Code
Display MoreSub find_Week_Batch_GreaterDay() Dim find_Week_Batch_GreaterDay As Integer Dim ClearVal As String ClearVal = Sheets(ActiveSheet.Index - 1).Range("F6").Value If Clearval Like "*WEEK*" Or _ Clearvall Like "*BATCH*" Then If Sheets(ActiveSheet.Index - 1).Range("O6") > 1.1 Then ActiveSheet.Range("ToClear").Value = Sheets(ActiveSheet.Index - 1).Range("ToClear") Else ActiveSheet.Range("ToClear").ClearContents MsgBox "Please Put New Work Order"
Kind Reagrd,
Reggieneo