Crazy Formula to VBA.

jvoss

Board Regular
Joined
Jun 13, 2015
Messages
76
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try change: For i = 1 To UBound(a)

To: For i = UBound(a) To 1 Step -1
 
Upvote 0
Try change: For i = 1 To UBound(a)

To: For i = UBound(a) To 1 Step -1
thank you @Phuoc that fixed the occurrence part, any suggestions on the rest of the formula..
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,374
Messages
6,171,710
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top