Hello,
I have been at this all morning and cannot get it down. I am trying to search column "d" for "wire" or "roll" and insert a formula in column B of each instances row. The formula is counting all instances from another tab. For some reason, my larger macro (bottom code) makes the formula static and i need it to remain a formula, updating when the other tab changes.
I used the macro recorder to get the below code (upper code). The code below does work for me, but it does not loop through all instances, and it does not necessarily start from the beginning of the sheet. Ideally, id prefer to get the bottom code working, but if someone can work with either code, id be more than happy to implement whichever method works.
my workaround macro--
Sub tally_wire()
'
Cells.Find(What:="wire", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(columnOffset:=-2).Address).Select
Selection.FormulaArray = _
"=SUMIF('bom sheet'!C[2],Assembly!RC[3],'bom sheet'!C)"
End Sub
my original code-- this fetches the value, but it is static and i need it to dynamically update as changes are made.
' wire and roll
Dim Cnt As Long
lrow = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
StrArray = Array("1/8", "1/4", "1/2", "3/4")
For Cnt = 2 To lrow
Dim StrArray2 As Variant
StrArray2 = Array("wire", "roll")
For Each Arr2 In StrArray2
If Cells(Cnt, 4) Like "*" & Arr2 & "*" Then
'does not make qty dynamic.
Cells(Cnt, 2) = "=SUMIF('bom Sheet'!C[2],Assembly!RC[3],'bom Sheet'!C)"
End If
Next Arr2
Next Cnt
I have been at this all morning and cannot get it down. I am trying to search column "d" for "wire" or "roll" and insert a formula in column B of each instances row. The formula is counting all instances from another tab. For some reason, my larger macro (bottom code) makes the formula static and i need it to remain a formula, updating when the other tab changes.
I used the macro recorder to get the below code (upper code). The code below does work for me, but it does not loop through all instances, and it does not necessarily start from the beginning of the sheet. Ideally, id prefer to get the bottom code working, but if someone can work with either code, id be more than happy to implement whichever method works.
my workaround macro--
Sub tally_wire()
'
Cells.Find(What:="wire", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(columnOffset:=-2).Address).Select
Selection.FormulaArray = _
"=SUMIF('bom sheet'!C[2],Assembly!RC[3],'bom sheet'!C)"
End Sub
my original code-- this fetches the value, but it is static and i need it to dynamically update as changes are made.
' wire and roll
Dim Cnt As Long
lrow = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
StrArray = Array("1/8", "1/4", "1/2", "3/4")
For Cnt = 2 To lrow
Dim StrArray2 As Variant
StrArray2 = Array("wire", "roll")
For Each Arr2 In StrArray2
If Cells(Cnt, 4) Like "*" & Arr2 & "*" Then
'does not make qty dynamic.
Cells(Cnt, 2) = "=SUMIF('bom Sheet'!C[2],Assembly!RC[3],'bom Sheet'!C)"
End If
Next Arr2
Next Cnt
