Copying information to register

Dear @Heather Brock,

Thank you for posting your question in the Microsoft Q&A forum.

Based on the provided information, I understand the issue might be related to VBA. This usually indicates one of the following VBA‑related reasons:

1. The VBA code is referencing the wrong cells or empty cells

If the cell references for amounts or invoice numbers are incorrect (e.g., wrong column, wrong sheet, wrong range), VBA will return 0 because numbers default to zero when the referenced cell is empty.

2. The VBA variables for amounts/invoice numbers are declared as the wrong data type

If variables are declared incorrectly (e.g., as Integer instead of String or Variant), Excel may convert empty or non‑numeric values to 0.

3. The lookup function (e.g., Find, VLOOKUP, Match) is failing

If VBA cannot locate the correct row or lookup value for invoice number/amount but can find customer name, the numeric fields may be returning nothing, which VBA displays as 0.

4. The values are formatted as text, not numbers

If the invoice number or amount is stored as text in the worksheet, but the VBA code expects numeric values, the code may fail to read them.

5. A missing worksheet, changed table layout, or shifted columns

If the spreadsheet was modified after the VBA was written (extra column added, sheet renamed), the code may still run—but return zeros for numeric fields because it is pulling from the wrong location.

To diagnose accurately, I need to see how the VBA code is written and what the source data looks like.

Could you please provide one of the following?

• A screenshot related to the issue
• A copy of the VBA code you are running
• A short screen recording (if possible) showing the issue

This information will help identify whether the problem is caused by:

• incorrect cell references,
• variable types,
• lookup failures, or changes in the worksheet structure.

I am looking forward to your response.

If you have extra questions about this answer, please click "Comment".       

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Zeros appearing for amounts and invoice numbers when running a VBA routine usually indicate that the source fields or variables being copied are either empty, defaulting to 0, or not being referenced correctly in the code.

Based on similar VBA behavior in Access/Excel scenarios, common causes and checks are:

1. Verify the source cells/controls
   • Confirm that the cells or controls that should contain the amount and invoice number actually contain numeric or text values before the macro runs.
   • Ensure there is no default value of 0 set in the underlying table/worksheet range or control properties that might overwrite the intended values.
2. Check the VBA field references
   • In the code that copies data to the register, confirm that the correct fields are referenced for amount and invoice number (for example, Range("Amount") vs Range("InvoiceNo")).
   • Make sure the code is not assigning 0 explicitly (for example, initializing variables as 0 and then never overwriting them).
3. Validate event timing
   • If the macro runs from a form or user interface, ensure it runs after the user has entered the values (for example, not before an update/commit event).
4. Remove conflicting defaults
   • If the amounts or invoice numbers are bound to a table or list object, remove any default value of 0 on those fields so that the macro can store the actual values instead of 0.

If the problem persists, the exact VBA procedure that copies the customer name, amount, and invoice number is needed to identify the incorrect references or logic.

References:

• Before Update VBA code not working - Microsoft Q&A