Data acquisition from the web

[bknight]

Using your link and the information on the page
Range.TextToColumns(F1,xlDelimited) results in compile error
F1-F4005(in this case) would be the destination and I'm guessing the xlDelimited would be the correct choice.

[HansV]

F1 is Excel notation, not VBA. Use Range("F1") in VBA.

You'd have to use something like this:

Code: Select all

    With Worksheets("VixData")
        .Range("A1:A" & LastRow).TextToColumns Destination:=.Range("F1"), Comma:=True
    End With

if the data are on sheet VixData and LastRow is a variable containing the last used row number.

[bknight]

Code: Select all

Dim LastRow As Long
Dim LastRow As Long
With Worksheets("VixData")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row - 1
    .Range("A1:A" & LastRow).TextToColumns Destination:=.Range("F1"), Comma:=True
End With

Works great although I could have and will use "B1", A is wide enough to see all.
There must be some character in row 4006 as LastRow equaled 4006 (but data ends at 4005), the reason I subtracted one. Seems to me that Destination: and Comma: should be replaced by commas in the formula, but

[HansV]

When you specify arguments to a method, you have to use :=
So for example Comma:=True means that the Comma argument of TextToColumns is set to True, telling Excel to use the comma as delimiter.

[Rudi]

You were right to comment out the line .CommandType = 0.
That always fails when the macro is run.

Regarding the data all in column A:
Its either a conflict on your delimiter in Windows Regional Settings.
Check to ensure that the delimiter in the CSV is the same as the delimiter set as the List Separator in the Regional settings dialog (see image below)

Or it could be that your Text Import Wizard has a different delimiter character set in Step 2
Ensure that it is Tab or Comma

rundll32_2019-11-27_06-01-13.jpg

Edit: Oh, sorry. It seems you have the CSV import issue resolved.

[bknight]

You were right to comment out the line .CommandType = 0.
That always fails when the macro is run.

Regarding the data all in column A:
Its either a conflict on your delimiter in Windows Regional Settings.
Check to ensure that the delimiter in the CSV is the same as the delimiter set as the List Separator in the Regional settings dialog (see image below)

Yes my delimiter is a comma and the data imported is comma delimited. I don't know how where to check whether the delimiter in csv is a comma

Or it could be that your Text Import Wizard has a different delimiter character set in Step 2
Ensure that it is Tab or Comma

I don't know where that setting might be located.

rundll32_2019-11-27_06-01-13.jpg

Edit: Oh, sorry. It seems you have the CSV import issue resolved.

If the import could be accomplished into cells instead of a sting in column, that would be optimal. If that can't be done then Han's suggestions have enabled the data to separated into cells.

In your code suggestion you used

Code: Select all

strDate = Format(Worksheets("Data").Range("H2").Value, "mm/dd/yy")
        On Error Resume Next
        Set rgF = .Columns(1).Cells.Find(What:=CDate(strDate), After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        On Error GoTo 0

I'm ASSUMING that this is a Range method, although I didn't try to code the transfer the data from "VixData" to "Vix" last night. But one question however, since the value of the date cell is Julian, is it possible to search for that value instead of the date string(although this did work)?

[bknight]

Oh boy This resulted in Type mismatch

Code: Select all

With Worksheets("VixData")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row - 1
    .Range("A1:A" & LastRow).TextToColumns Destination:=.Range("B1"), Comma:=True
 End With
 Set wsSourceV = Sheets("VixData")
 Set wsDestinationV = Sheets("Vix")
 strDate = Format(Worksheets("VixData").Range("B" & LastRow).Value, "mm/dd/yyyy")
   With wsDestinationV
   Set rVix = .Columns(1).Cells.Find(What:=CDate(strDate), After:=Range("B1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

rWith Worksheets("VixData")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row - 1
.Range("A1:A" & LastRow).TextToColumns Destination:=.Range("B1"), Comma:=True
End With
Set wsSourceV = Sheets("VixData")
Set wsDestinationV = Sheets("Vix")
strDate = Format(Worksheets("VixData").Range("B" & LastRow).Value, "mm/dd/yyyy")
With wsDestinationV
Set rVix = .Columns(1).Cells.Find(What:=CDate(strDate), After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)[/code]
rVix had been dimensioned as a Range earlier
Anyway the error was run time 13 Type mismatch. This is almost a duplicate of Rudi's code with the exception of setting StrDate to a four digit year.
What did I do incorrectly?

[HansV]

You search in column A on the Vix sheet, but specify that you want to search after cell B1 on the active sheet. That is not possible.
Also, I don't think you should use CDate here.

Code: Select all

   Set rVix = .Columns(1).Cells.Find(What:=strDate, After:=.Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

[bknight]

You search in column A on the Vix sheet, but specify that you want to search after cell B1 on the active sheet. That is not possible.
Also, I don't think you should use CDate here.

Code: Select all

   Set rVix = .Columns(1).Cells.Find(What:=strDate, After:=.Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

That is where the soucre cell is and the destination. Why is that not possible? Also the cdate is used in the code part from Rudi. Um could you suggest an alternate?

[HansV]

The "After" argument must be one of the cells in the range you want to find in. It tells Excel to start searching from that cell. It cannot search outside the find range.

I'd just omit the CDate - as in the code that I posted.

[bknight]

Hans other than not using cdate, I don't see any difference as you are searching in colmn 1. I'm not at the pc currently so I am unable to test.

[HansV]

I also added a . before Range(…), because without the . the Range refers to the active worksheet, not necessarily to the Vix sheet. Adding the . makes the Range refer to Vix because it is inside the With wsDestinationV … End With block.

[bknight]

Disregard, it might have been during the edit, everything still downloads. I dleted the comment.
Hans your suggestion works(?) However the adding of values is giving an error Object defined error

Code: Select all

With wsDestinationV
   Set rVix = .Columns(1).Cells.Find(What:=strDate, After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        On Error GoTo 0
     If rVix Is Nothing Then
       MsgBox "Date cannot be found on Vix sheet! Record will be added to the end of the table!", vbExclamation
            LastRow = .Cells(1).CurrentRegion.Rows.Count + 1
            .Cells("A" & lRow).Value = wsSourceV.Range("B" & LastRow).Value
            .Cells("B" & lRow).Value = wsSourceV.Range("C" & LastRow).Value
            .Cells("C" & lRow).Value = wsSourceV.Range("D" & LastRow).Value
            .Cells("D" & lRow).Value = wsSourceV.Range("E" & LastRow).Value
            .Cells("E" & lRow).Value = wsSourceV.Range("F" & LastRow).Value
            'If (.Cells(lRow - 1, "B")) - (.Cells(lRow - 1, "C")) < -500 And _
            '   (.Cells(lRow, "B")) - (.Cells(lRow, "C")) > -500 Then _
            '   .Range(.Cells(lRow, "B"), .Cells(lRow, "C")).Interior.Color = vbGreen
            '.Cells(lRow, "F").Value = "=(R[-1]C*R3C9)+(RC[-1]*R2C9)"
            '.Cells(lRow, "G").Value = "=(R[-1]C*R7C9)+(RC[-2]*R6C9)"
            '.Cells(lRow, "H").Value = "=RC[-2]/RC[-1]"
            '.Cells(lRow, "K").Value = "=AVERAGE(R[-8]C[-6]:R[-1]C[-6])"
            '.Cells(lRow, "L").Value = "=AVERAGE(R[-20]C[-7]:R[-1]C[-7])"
            'Application.Goto .Cells(lRow, 1), True
         ElseIf Not rVix Is Nothing Then
            lRow = rVix.Row
            .Cells("B" & lRow).Value = wsSourceV.Range("C" & LastRow).Value
            .Cells("C" & lRow).Value = wsSourceV.Range("D" & LastRow).Value
            .Cells("D" & lRow).Value = wsSourceV.Range("E" & LastRow).Value
            .Cells("E" & lRow).Value = wsSourceV.Range("F" & lstrow).Value
            'If (.Cells(lRow - 1, "B")) - (.Cells(lRow - 1, "C")) < -500 And _
               '(.DCells(lRow, "B")) - (.Cells(lRow, "C")) > -500 Then _
               '.Range(.Cells(lRow, "B"), .Cells(lRow, "C")).Interior.Color = vbGreen
            '.Cells(lRow, "F").Value = "=(R[-1]C*R3C9)+(RC[-1]*R2C9)"
            '.Cells(lRow, "G").Value = "=(R[-1]C*R7C9)+(RC[-2]*R6C9)"
            '.Cells(lRow, "H").Value = "=RC[-2]/RC[-1]"
            '.Cells(lRow, "K").Value = "=AVERAGE(R[-8]C[-6]:R[-1]C[-6])"
            '.Cells(lRow, "L").Value = "=AVERAGE(R[-20]C[-7]:R[-1]C[-7])"
        End If
        End With

I have added .Range before the .Cells without any change in error.
This is the line where the watch is giving the error as rVix is not null and has the value "11/27/2019".

[HansV]

Sorry, no idea.

[bknight]

It works, must have been associated with editing the macro. Please relook at the previous post.

[HansV]

Which line causes the error?

[bknight]

.Cells("B" & lRow).Value = wsSourceV.Range("C" & LastRow).Value ,sorry

[HansV]

So the first line

.Cells("A" & lRow).Value = wsSourceV.Range("B" & LastRow).Value

works, but not the one below it? If so, does wsSourceV.Range("C" & LastRow) contain an error?

[bknight]

The first part of the line doesn't work in the watch, gives the object defined error
.Cells("B" & lRow).Value
The second part(wsSourceV.Range("C" & LastRow)) works and has the correct value.
I attempted putting in the .Range in front of the .Cells, but that also results in the same error in the watch.

[HansV]

Try replacing .Cells with .Range:

Code: Select all

            .Range("A" & lRow).Value = wsSourceV.Range("B" & LastRow).Value

Cells expects row number and column number as arguments, for example .Cells(lRow, 1)
Range expects a cell address as argument, for example .Range("A" & lRow)