Formula returns #N/A
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Formula returns #N/A
Rudi:
You helped with some code by creating a spreadsheet with code that I use nightly. Well it worked perfectly until Monday, then I get an error. I have included the data sheet for 7 Apr 2021. Now the data is perfect but the date has somehow got corrupted and displays a #N/A instead of the date could you have a look at this for me?
I'll copy the code which isn't on the sheet, I think, to help you.
You will notice the command GoTo PutCallRatio, that to get around the errors created when they deleted the named address and started putting data in a new format. I do this part manually every night.
The file is to large to attach 346K. The code is too long for the character limits. I don't have any cloud to ad the file.
You helped with some code by creating a spreadsheet with code that I use nightly. Well it worked perfectly until Monday, then I get an error. I have included the data sheet for 7 Apr 2021. Now the data is perfect but the date has somehow got corrupted and displays a #N/A instead of the date could you have a look at this for me?
I'll copy the code which isn't on the sheet, I think, to help you.
You will notice the command GoTo PutCallRatio, that to get around the errors created when they deleted the named address and started putting data in a new format. I do this part manually every night.
The file is to large to attach 346K. The code is too long for the character limits. I don't have any cloud to ad the file.
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
A Gmail is account is free. It comes with space on Google Drive.
A Microsoft account is free. It comes with space on Onedrive.
The free version of Dropbox comes with 2 GB of cloud storage.
And if you don't want cloud storage, there is We Transfer
A Microsoft account is free. It comes with space on Onedrive.
The free version of Dropbox comes with 2 GB of cloud storage.
And if you don't want cloud storage, there is We Transfer
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula returns #N/A
I must have about a dozen app.box.com accounts , like we talked about here
http://www.eileenslounge.com/viewtopic. ... 15#p197115
No Ads, never had any spam Emails from them, unlike I did with some other free cloud sharing sites.
( Register free here : https://app.box.com/signup/personal )
I have never had any problem with them, ( and at some of the major forums, where I have to go incognito, I have a matching EMail account , Forum username account and appbox account for a particular area of Excel or anything else I am interested in, and it is a useful way to organise stuff. Its an unconventional, but I find quite practical way, to keep a bit organized )
( More recently on some of my older computers with older Operating systems with out of date browsers, some functions in my app box accounts stopped working, or got very slow, but changing to Firefox browser seemed to sort out all those problems )
_.___
A Gmail account like Hans said is free, easy to register,
( https://accounts.google.com/signup/v2/w ... try=SignUp ) ,
and comes with an automatic cloud storage.
A short cut to get a share link to a large file from the free storage is to send yourself an Email and try to attach the file, then it gets sent as a link to the file storage.
A German Telekom account works the same, sending a large attached file as a link which you can then share, and I have found it is the one of the most easiest to register an account with and so far I never hit a limit with nigh on 100 accounts. I use a lot of those extra accounts purely for the file cloud storage facility, as I do with some of the Gmail accounts. But Gmail seem to limit the number of accounts that they will let be verified via a particular telephone number.. They don’t give the figure officially, but it seems to be about 10-12 in my experience. German Telekom seems to have no limit.
Free mail register at German Telekom is at
https://meinkonto.telekom-dienste.de/te ... tion.xhtml
http://www.eileenslounge.com/viewtopic. ... 15#p197115
No Ads, never had any spam Emails from them, unlike I did with some other free cloud sharing sites.
( Register free here : https://app.box.com/signup/personal )
I have never had any problem with them, ( and at some of the major forums, where I have to go incognito, I have a matching EMail account , Forum username account and appbox account for a particular area of Excel or anything else I am interested in, and it is a useful way to organise stuff. Its an unconventional, but I find quite practical way, to keep a bit organized )
( More recently on some of my older computers with older Operating systems with out of date browsers, some functions in my app box accounts stopped working, or got very slow, but changing to Firefox browser seemed to sort out all those problems )
_.___
A Gmail account like Hans said is free, easy to register,
( https://accounts.google.com/signup/v2/w ... try=SignUp ) ,
and comes with an automatic cloud storage.
A short cut to get a share link to a large file from the free storage is to send yourself an Email and try to attach the file, then it gets sent as a link to the file storage.
A German Telekom account works the same, sending a large attached file as a link which you can then share, and I have found it is the one of the most easiest to register an account with and so far I never hit a limit with nigh on 100 accounts. I use a lot of those extra accounts purely for the file cloud storage facility, as I do with some of the Gmail accounts. But Gmail seem to limit the number of accounts that they will let be verified via a particular telephone number.. They don’t give the figure officially, but it seems to be about 10-12 in my experience. German Telekom seems to have no limit.
Free mail register at German Telekom is at
https://meinkonto.telekom-dienste.de/te ... tion.xhtml
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
Well I do have a Gmail account and it does have MyDrive. Now since I have never used this, it will be a two step process. I have uploaded two files
Data.xls and VIXData050824.xls (the original file). Now how do I allow you to download?
Data.xls and VIXData050824.xls (the original file). Now how do I allow you to download?
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
Thanks Doc, if the MyDrive doesn't work, I'll give app.box a try.
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
I did(do?) have a drop box account, but the last time we had an issue to discuss they wanted to charge me which is not what I care for, but I understand their need.HansV wrote: ↑08 Apr 2021, 07:12A Gmail is account is free. It comes with space on Google Drive.
A Microsoft account is free. It comes with space on Onedrive.
The free version of Dropbox comes with 2 GB of cloud storage.
And if you don't want cloud storage, there is We Transfer
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
Right-click one of the uploaded files.
Select 'Get link' from the context menu.
Click 'Copy Link'.
You can now paste the URL into a reply in this thread.
Do the same for the other uploaded file.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
https://drive.google.com/file/d/1anUNAT ... sp=sharing ---for Vix(full file)
https://drive.google.com/file/d/1eFs6wY ... sp=sharing -- for Data
https://drive.google.com/file/d/1eFs6wY ... sp=sharing -- for Data
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
Something went wrong - when I click on the links, I get a message "you need access"...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula returns #N/A
Same with me....
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
https://drive.google.com/file/d/1anUNAT ... sp=sharing Not sure if it is the same link
https://drive.google.com/file/d/1eFs6wY ... sp=sharing
I didn't include everyone, sorry, I'm a first time user.
https://drive.google.com/file/d/1eFs6wY ... sp=sharing
I didn't include everyone, sorry, I'm a first time user.
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
I have changed the subject of this thread to be more descriptive of the problem and to remove the request to a particular member. See Rule #3 in House rules for Eileen's Lounge
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
The cause of the error is that the text "Cboe Market Summary for" does not occur in column A.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
Here is the web link
https://markets.cboe.com/us/options/mar ... ics/daily/
What column does the date appear?
ETA: The rest of the the data is accurate, just the date is the problem as far as I can see.
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
Apparently they don't mention the date on that web page anymore.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
Look at the web page it is displayed with a small calendar function to the left.
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
Ah - I didn't see that because I had scripts turned off.
The little calendar is not a text element, but some kind of control. I don't know anything about such stuff, hopefully someone else can help you with that.
The little calendar is not a text element, but some kind of control. I don't know anything about such stuff, hopefully someone else can help you with that.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Formula returns #N/A
There are too many settings and unless everyone has the same we are on the Tower of Babel.
In the page refresh that Rudi programmed, there is no date. Would the "exclusion" of the date be in:
Code: Select all
Private Sub GetDataWeb()
Application.CutCopyMode = False
On Error Resume Next 'added this to eliminate securty warning alerts
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://markets.cboe.com/us/options/market_statistics/daily", Destination _
:=Range("$A$2"))
On Error GoTo 0 'added this to eliminate securty warning alerts
'.CommandType = 0
.Name = "daily"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula returns #N/A
I don't think so, but I will have to leave this to others. I'm totally out of my depth here.
Best wishes,
Hans
Hans