Hi All,
can somebody please help?
vba solution is also OK, whichever can help.
much thanks
Hi All,
can somebody please help?
vba solution is also OK, whichever can help.
much thanks
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"]
A1
[/td]CorpComm Office Curtain Project 二樓金沙中國 辦公室的公共關係部需要造窗簾
[/td]Accessories_飾品
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]Curtain_簾
[/TABLE]
[/TD]
A2
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]Air Compressor_空氣壓縮機
[/TABLE]
[/TD]
A3
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]BandSaw_帶鋸
[/TABLE]
[/TD]
A4
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]Base_腳
[/TABLE]
[/TD]
A5
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]Cabinet_內閣
[/TABLE]
[/TD]
A6
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"] [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]Curtain_簾
[/TABLE]
[/TD]
[/TABLE]
[/TD]
A7
[/td][TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 253"]Door_大門
[/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.
I can't make this formula work and there is no error . [TABLE="border: 1, cellpadding: 1, width: 500"]
A
[/td]B
[/td]C
[/td]D
[/td]E
[/td]F
[/td]39751
1245
[/td]4
[/td]5/18/18
[/td]5/18/18
[/td]39751
1245
[/td]8
[/td]5/18/18
[/td]
[/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:
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 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
Display More
------------------------------------------------------------------------------------------------------
the 2 worksheet change including the one you wrote (i have modified):
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
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
Display More
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 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.
Dim 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
Display More
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).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;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?
Sub 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"
Display More
Kind Reagrd,
Reggieneo