Strange thing with this formula written by VBA

[tempest]

Hi all.

I ran into a problem where the cell formulas deviate from what my VBA
code wrote to them.

Example:

My VBA code wrote the following string to formula property of a
selected cell:

=VLOOKUP(B11,LaborDataQuery!BW_LaborData,2,FALSE)

where "LaborDataQuery" is the name of a separate worksheet in the same
workbook and "BW_LaborData" is the name of the query table (or named
range) in "LaborDataQuery" worksheet.

However, the final formula shown in the cell became this:

=VLOOKUP(B11,APPWG.XLS!BW_LaborData,2,FALSE)

Where "APPWG.XLS" is the file name of the workbook.

I don't know why Excel changed the worksheet name in my formula to the
file name of the workbook.

Entering the correct formula directly in Excel's formula bar does not
have this problem.

If anyone knows why this anomaly occurred, please let me know.

Thanks.

Jason

 

[Orlando Magalhães Filho]

Hi,

Because the name "BW_LaborData" on LaborDataQuery sheet is defined as
workbook name. Try define as sheet name. Do this:

- Active LaborDataQuery sheet;
- Select the BW_LaborData range;
- Go Insert menu > Name submenu > Define command
- Select BW_LaborData name and Delete
- Now insert LaborDataQuery!BW_LaborData
- Press Add and OK


HTH