VBA Auto Entry of Data with a Problematic Countif Function

powellku

New Member
Joined
Aug 13, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Hi Excel Gurus,

The Objective

My objective is to use VBA to automatically input information into this database from a VBA entry form. Upon entry of the data, a unique Index is generated as well as a “Tracking Add-on” is created to track the adjustments to the indexed item.

The Problem
When new data is entered the "Countif" formula changes itself to accommodate to the new data which messes up “Tracking Add-on”. Instead of the “Tracking Add-on” adding a sequential number (i.e. 1,2,3) it adds the latest version of the number (i.e. 3) to all entry with the same Index root. Additionally, the font reverts back to the original table style and not my altered style: Arial in black. The “Cell & Countif” function is the formula that is causing the error, the ID and Index are work fine.

Attempted Solutions
I’ve rebuild the sheet twice. I’ve tinkered with all the formulas. I’ve reviewed my formulas and VBA code against other excel sheets that I’ve created to see if I’ve made any significant changes, but I haven’t. Thus, I haven’t been able to solve this issue! Any help would be appreciated!

Possible Solutions
Is there another formula that I could use instead of the “Cell & Countif”? Or, a formula locking setting that I could change?

Table Details
ID
Index
Index w/ Add-on
159
B03-P01-R03-A03-19
B03-P01-R03-A03-191
=ROW(A161)-2
=CONCATENATE([Branch ID],$A$1,[Program ID],$A$1,[Recom. ID],$A$1,[Action Item ID],$A$1,[@[Year ID]])
=B161&COUNTIF($B$3:B161,B161)

Sincerely,

KP

Preview attachment Database Issue.PNG




Database Issue.PNG
14 KB
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,463
Members
416,029
Latest member
CSM1

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
Top