Looking for a way to make this formula into a vba to only be ran on button click or called from another micro.
this formula is in D146
=IF(H146="",IF(SUMPRODUCT(--ISNUMBER(SEARCH(cartcolor,$Q146)))=1,IF(COUNTIF($AH146:$AH$5000,AH146)-1>=1,(COUNTIF($AH146:$AH$5000,AH146)-1)+1,0&"-"&IF($K146="-","EPN",K146)),1),H146)
Crazy looking formula. i was able to find a vba here in MrExcel and now i cant find the post. in a since what i would like to do:
look in cell for being blank (most of the time it has a value), if the cell is not blank then check another cell to see if it contains 1(one) of the items in a named list(this is variable), if it dose (this is where it gets crazy) it counts the number of occurrence and if the number of occurrence is 1 then it makes the cell look like this: 0-USA, if the cell exceeds 1 then it gets the # of occurrence start count from the bottom to current cell (ie if this is the 3 occurrence it will put a number 2 in the cell because the first occurrence is 0-USA the second is 1 and the third is 2 and so on.. but this is all bypassed if anything is in the cell already.
data set.
D146 (is the end active cell
h146 (for this example) hold ""
q146 hold GR (which is in the "(cartcolor)" list so that part)returns a 1
in AH146:5000 hold 3 of the same values.
this make the d146 value a "2"
the vba i found here to handle the occurrence is: (i cant find the og post)
Sub Number_Items()
Dim d As Object
Dim a As Variant
Dim i As Long
a = Range("AH5", Range("AH" & Rows.Count).End(xlUp)).Value
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a)
d(a(i, 1)) = d(a(i, 1)) + 1
a(i, 1) = d(a(i, 1))
Next i
Range("D5").Resize(UBound(a)).Value = a
End Sub
one issue with this is it dose not do the count from bottom up. so it kinda dose what i need but not really.
this formula is in D146
=IF(H146="",IF(SUMPRODUCT(--ISNUMBER(SEARCH(cartcolor,$Q146)))=1,IF(COUNTIF($AH146:$AH$5000,AH146)-1>=1,(COUNTIF($AH146:$AH$5000,AH146)-1)+1,0&"-"&IF($K146="-","EPN",K146)),1),H146)
Crazy looking formula. i was able to find a vba here in MrExcel and now i cant find the post. in a since what i would like to do:
look in cell for being blank (most of the time it has a value), if the cell is not blank then check another cell to see if it contains 1(one) of the items in a named list(this is variable), if it dose (this is where it gets crazy) it counts the number of occurrence and if the number of occurrence is 1 then it makes the cell look like this: 0-USA, if the cell exceeds 1 then it gets the # of occurrence start count from the bottom to current cell (ie if this is the 3 occurrence it will put a number 2 in the cell because the first occurrence is 0-USA the second is 1 and the third is 2 and so on.. but this is all bypassed if anything is in the cell already.
data set.
D146 (is the end active cell
h146 (for this example) hold ""
q146 hold GR (which is in the "(cartcolor)" list so that part)returns a 1
in AH146:5000 hold 3 of the same values.
this make the d146 value a "2"
the vba i found here to handle the occurrence is: (i cant find the og post)
Sub Number_Items()
Dim d As Object
Dim a As Variant
Dim i As Long
a = Range("AH5", Range("AH" & Rows.Count).End(xlUp)).Value
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a)
d(a(i, 1)) = d(a(i, 1)) + 1
a(i, 1) = d(a(i, 1))
Next i
Range("D5").Resize(UBound(a)).Value = a
End Sub
one issue with this is it dose not do the count from bottom up. so it kinda dose what i need but not really.
