Strange thing with this formula written by VBA

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

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

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)

<tempest@ucla.edu> escreveu na mensagem
news:9dc1gvobm3fte33niaum5bsvbf9h9h27mm@4ax.com...
> 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
>