Formula to VBA Code

**archvanarl** · 08-04-2022, 09:43 PM

Hi, is it possible to make these formulas run into the background through VBA?

In "PPMP I (3)" sheet columns E and F, there are index+match formulas to generate unit and unit costs from the "APP PART A 2022".
It works. However, there were times that I must replace the formula and manually enter the UNIT and UNIT COST that are not to be found in "APP PART A 2022".
With this, the formula is erased. What I think is if it is possible to make this formula work in the background without inputting the formula in columns E and F.
If those columns were filled with values/data manually, Index+Match through VBA will not work but if it is left emply, the VBA will work as Index+Match.

I hope I made it clear. :D

Hoping for someone could help me on this.

**JohnTopley** · 08-05-2022, 01:28 AM

What is the issue with overwriting a formula in a given row?

With VBA, you can trigger the INDEX/MATCH equivalent on entry of data in column C if no match is found, VBA does nothing i.e E and F are left blank. This OK ?

**archvanarl** · 08-05-2022, 01:36 AM

The issue is some users of this form are skeptical and they change minds to what item they entered in column C. Yes. whenever data is entered in column C, the formula should be triggered and look for the UNIT and UNIT COST from the other sheet. Sometimes, some users have no idea how to recover the formula when replaced with manually entered data that cannot be found from other sheet.

**JohnTopley** · 08-05-2022, 02:16 AM

Please Login or Register  to view this content.

The following in a standard module

Please Login or Register  to view this content.

Highlighted in attached are via VBA

**archvanarl** · 08-05-2022, 08:49 AM

Hi, it works! I manage to ignore the error when data on the cells are deleted by using "On Error Resume Next"

Please Login or Register  to view this content.

However, whenever I paste (CTRL+V) from the other sheet and select multiple items to paste in column C, UNIT PRICE and UNIT are not generating. Could you help me further on this? Also, when the cells on column C is empty again, should the cells on column E and F be empty also.

**archvanarl** · 08-05-2022, 09:59 PM

Bump for help.

**JohnTopley** · 08-06-2022, 05:37 AM

I was curious as how you entered data "manually" without lots of potential typos: now I know!

Will re-look at code.

**archvanarl** · 08-06-2022, 06:01 AM

Yep, data are mostly copied and pasted using crtl + v. It is working when I copy and paste a single cell in but it could be time consuming to copy and paste one by one. Is there a workaround on this to work this with multiple cells to be pasted at the same in column C?

**JohnTopley** · 08-06-2022, 07:20 AM

Try

Replace code in MODULE

Please Login or Register  to view this content.

**archvanarl** · 08-06-2022, 08:57 AM

Perfect! I will use this to the original file. For now, solved!

Formula to VBA Code

LinkBack

Thread Tools

Rate This Thread

Display

Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Re: Formula to VBA Code

Thread Information

Users Browsing this Thread

Similar Threads

writing vba code to fill in column of sheet, formula works vba code will not

Writing a Formula using R1C1 code in VBA code

sir how to vlookup formula two line break in excel. sir my code is, this code is working a

Advice and help with a copy past code or filldown formula code

Help: formula or VB code to implement voting system code

[SOLVED] Array Formula vs vBa code - How To Modify My Messy Code To One Of Those

Sumproduct formula:Empl Code Payroll Code Amount

Bookmarks

Bookmarks

Posting Permissions