Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: 3
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: 35
Default Strange thing with this formula written by VBA

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)

escreveu na mensagem
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why is a formula written this way? Ross Excel Worksheet Functions 3 December 25th 08 08:45 AM
Strange thing happens... TomCat Excel Worksheet Functions 4 September 30th 07 04:17 PM
Is there a formula to change number value to the written value? Ginny@apl Excel Discussion (Misc queries) 3 February 23rd 06 03:39 AM
Strange Thing Happening PeterK Excel Worksheet Functions 0 May 3rd 05 09:10 PM
Strange thing happened with TOC RoxSn Excel Discussion (Misc queries) 1 March 11th 05 09:43 PM


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"