Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 24th 20, 08:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 56
Default Help with named-cell referencing

I have two open workbooks:

SPX-BS.xlsm and

Book1.xlsm

There is a cell named "premium" in SPX-BS which I need to access from a macro to post the value to Book1. If I enter manually

'SPX-BS.xlsm'!premium into a cell on Book1 it works fine. However if, within a macro I type

Range("A7") = 'SPX-BS.xlsm'!premium

I get an "Compile Error Syntax Error" message.

Can someone please advise what I'm doing wrong? Many thanks in advance.

  #2   Report Post  
Old November 24th 20, 09:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default Help with named-cell referencing

Hi,

Am Tue, 24 Nov 2020 11:54:10 -0800 (PST) schrieb programmernovice:

I have two open workbooks:

SPX-BS.xlsm and

Book1.xlsm

There is a cell named "premium" in SPX-BS which I need to access from a macro to post the value to Book1. If I enter manually

'SPX-BS.xlsm'!premium into a cell on Book1 it works fine. However if, within a macro I type

Range("A7") = 'SPX-BS.xlsm'!premium

I get an "Compile Error Syntax Error" message.


try:
Range("A7") = Workbooks("SPX-BS.xlsm").Sheets("Sheet1").Range("premium")

Modify the sheet name as expected.


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old November 24th 20, 09:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 56
Default Help with named-cell referencing

On Tuesday, November 24, 2020 at 2:20:34 PM UTC-6, Claus Busch wrote:
Hi,
Am Tue, 24 Nov 2020 11:54:10 -0800 (PST) schrieb programmernovice:

I have two open workbooks:

SPX-BS.xlsm and

Book1.xlsm

There is a cell named "premium" in SPX-BS which I need to access from a macro to post the value to Book1. If I enter manually

'SPX-BS.xlsm'!premium into a cell on Book1 it works fine. However if, within a macro I type

Range("A7") = 'SPX-BS.xlsm'!premium

I get an "Compile Error Syntax Error" message.

try:
Range("A7") = Workbooks("SPX-BS.xlsm").Sheets("Sheet1").Range("premium")

Modify the sheet name as expected.


Regards
Claus B.


Many thanks for taking time to help me out, Claus




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
Referencing a named range based upon Range name entry in cell Barb Reinhardt Excel Worksheet Functions 14 June 20th 07 07:19 PM
Referencing Named Ranges using values in a cell eggman Excel Discussion (Misc queries) 2 September 23rd 06 06:30 PM
Referencing cell in Named Range in Function VS Formula Nyq[_2_] Excel Programming 2 June 13th 06 08:56 PM
referencing a sheet named in a cell then using data from that sheet gbeard Excel Worksheet Functions 4 April 15th 05 08:42 AM
VBA Referencing a Named Cell Range in another Workbook Frank & Pam Hayes[_2_] Excel Programming 5 June 29th 04 10:01 PM


All times are GMT +1. The time now is 09:25 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017