PDA

View Full Version : Solved: where to put end if



asdzxc
05-17-2012, 09:15 PM
compile error: End if without If
Dim ws As Worksheet
With ActiveSheet
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
If ws = Worksheets("Sheet2") Then
With Worksheets("Sheet2").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet2").Range("I1").Value & "&a=10&b=13&c=2010&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet2").Range("$A$1"))

With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("I1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End If
.Name = _
"MyData"
.FieldNames = True

Simon Lloyd
05-17-2012, 11:02 PM
If your IF is outside of the WITH statement then so should your END IF be, the same goes with your END WITH.

Aussiebear
05-17-2012, 11:30 PM
As per Simon's advice, code sections starting with If and With need closing End If & End With. Have a good look at your current section of code and find the points that VBA is looking for.

asdzxc
05-17-2012, 11:50 PM
As per Simon's advice, code sections starting with If and With need closing End If & End With. Have a good look at your current section of code and find the points that VBA is looking for.
compile error: syntax error
Dim ws As Worksheet
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
If ws = Worksheets("Sheet2") Then
With Worksheets("Sheet2").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet2").Range("I1").Value & "&a=10&b=13&c=2010&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet2").Range("$A$1"))
End If and end with
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("I1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
.Name = _
"MyData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 0
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False

Aussiebear
05-18-2012, 12:07 AM
That is not what I meant. Sections of code require opening and closing vba statements. For example

Private Sub()
'your code
End Sub
If C= B then
'do something
End If

In your case your code has the following lines


If ws = Worksheets("Sheet2") Then
With Worksheets("Sheet2").QueryTables.Add(Connection:= "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _ Worksheets("Sheet2").Range("I1").Value & "&a=10&b=13&c=2010&d=06&e=6&f=2013&g=m&ignore=.xlsx" _ , Destination:=Worksheets("Sheet2").Range("$A$1"))
End If And End with
With Worksheets("Sheet1").QueryTables.Add(Connection:= "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _ Worksheets("Sheet1").Range("I1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _ , Destination:=Worksheets("Sheet1").Range("$A$1"))
.Name = "MyData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 0
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False

In a logical sequence, If..., With....With. VBA would be expecting you to close them in logical sequence End With...., End With.... End If.

Go back to your code and remove the line "End If and End With" and work through the code logically. Post back your results.

asdzxc
05-18-2012, 12:27 AM
remove the line "End If and End With" removed and macro stoped at Refresh BackgroundQuery:=False
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0

With Worksheets("Sheet2").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet2").Range("I1").Value & "&a=10&b=13&c=2010&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet2").Range("$A$1"))
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("I1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
.Name = _
"MyData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 0
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False

End With
End With
End Sub

Simon Lloyd
05-18-2012, 12:32 AM
You cannot do thisIf something then
with this
.do that
End If
'blah
'blah
End withit must be like thisIf something then
with this
.do that
'blah
'blah
End with
end if or
with this
.do that
If something then
End If
'blah
'blah
End withthis Refresh BackgroundQuery:=False
is an orphan and you need to attach it to whichever statement it came from or delete it!

Bob Phillips
05-18-2012, 01:16 AM
DOn't you need a closing bracket after .WebConsecutiveDelimitersAsOne = True ?

I assume you recorded this code, so it should have got it right.

Aussiebear
05-18-2012, 02:29 PM
There is a missing . immediately before the Refresh

Teeroy
05-18-2012, 06:56 PM
The nesting of the WITH statements does not appear to be correct. You only need to nest WITHs to perform multiple actions on a single object. I would take the last END WITH to before the start of the second WITH block assuming you're planning to do more actions on the object. At the moment, with only one action performed on the first object that WITH block is superfluous and should be removed for clarity.

asdzxc
05-18-2012, 10:30 PM
You cannot do thisIf something then
with this
.do that
End If
'blah
'blah
End withit must be like thisIf something then
with this
.do that
'blah
'blah
End with
end if or
with this
.do that
If something then
End If
'blah
'blah
End withthis Refresh BackgroundQuery:=False
is an orphan and you need to attach it to whichever statement it came from or delete it! Thank you for your detailed explanation
whether H1 is empty or not, produce the same result (macro file attached):
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
If Range("h1").Value > 0 Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End With
End If
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
.Name = _
"MyData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 0
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False
End With
End Sub

Teeroy
05-19-2012, 01:44 AM
The second WITH block acts upon the same destination as the first and so changes the result of the first action. Therefore it doesn't matter whether the first runs or not you'll only get the second.

asdzxc
05-19-2012, 03:58 AM
The second WITH block acts upon the same destination as the first and so changes the result of the first action. Therefore it doesn't matter whether the first runs or not you'll only get the second.


No way to solve this problem?

Teeroy
05-19-2012, 05:31 AM
I'm lost as to what you're trying to do. Initially you were working with 2 different sheets ("Sheet1" and "Sheet2") but in the last post both were working on the same sheet ("Sheet1") and destination range, and checking for a .value > 0 (at "h1") with a string in the cell. If you're looking to see whether the cell's populated and do different actions you should use <>"" (i.e. not equal to Null string) in an if / else condition.

Basically without more detail about what you're trying to achieve we're a bit hamstrung about the amount of help we can give.

asdzxc
05-19-2012, 05:42 AM
I'm lost as to what you're trying to do. Initially you were working with 2 different sheets ("Sheet1" and "Sheet2") but in the last post both were working on the same sheet ("Sheet1") and destination range, and checking for a .value > 0 (at "h1") with a string in the cell. If you're looking to see whether the cell's populated and do different actions you should use <>"" (i.e. not equal to Null string) in an if / else condition.

Basically without more detail about what you're trying to achieve we're a bit hamstrung about the amount of help we can give.
I had tried if / else condition but there is compile error:
Else without if
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
If Range("H1").Value = "" Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
Else
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End If

Teeroy
05-19-2012, 06:11 AM
I love some of the random errors VBA gives with the wrong syntax. The problem there is a lack of END WITH inside the IF and ELSE sections of the code per the order that's been given through this post.

To show how random it can be if I get a FOR without NEXT error I'm immediately looking for a place I missed an END IF (Go Figure!). :dunno

asdzxc
05-19-2012, 07:04 AM
I love some of the random errors VBA gives with the wrong syntax. The problem there is a lack of END WITH inside the IF and ELSE sections of the code per the order that's been given through this post.

To show how random it can be if I get a FOR without NEXT error I'm immediately looking for a place I missed an END IF (Go Figure!). :dunno

compile error: End if without If On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
If Range("H1").Value = "" Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End With
Else
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End If

Aussiebear
05-19-2012, 02:28 PM
No. We are back at the initial error in methodology.

Opening and closing statements need to be paired. If you get them out of sequence then vba will throw error messages. You can nest statements within others but doing so requires the person doing the coding to carefully follow the sequence.

You have in sequence the following order IF, With, End With, Else, With, End If, but it needs to be If, With, End With, Else, With, End With, End If.

Aussiebear
05-19-2012, 02:38 PM
Look at the following example of code and see how the pairing works.
Sub MoveDatatosheets()
' Copy and paste bids into sheets
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 4 To lastrow
If .Cells(i, "N").Value = "MorgStan/DeanWit-Trader" Then
With Range(.Cells(i, "A"), .Cells(i, "Y")).Select Selection.Cut Sheets("MS").Select Range("A10000").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Sheets("Blotter").Select
End With
End If
Next i
End With
End Sub
Notice how the sequence here is With, For, If, With, End With, End If, Next i, End With

asdzxc
05-19-2012, 06:56 PM
Thank you for your example.
compile error: Next without for

Dim s As String
If Range("H1").Value = "" Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
s = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End With
End If
Next s
If Range("H1").Value > 0 Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
s = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2010&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End With
End If

Teeroy
05-19-2012, 08:09 PM
You only use NEXT to complete a FOR loop. Since there's no loop in your code delete the line "Next s". You've got the nexting of the IFs and WITHs worked out.

asdzxc
05-19-2012, 09:06 PM
You only use NEXT to complete a FOR loop. Since there's no loop in your code delete the line "Next s". You've got the nexting of the IFs and WITHs worked out.


In J1, put ^dji
macro stop at .FieldNames = True
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
If Range("H1").Value = "" Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End With
End If

If Range("H1").Value > 0 Then
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2010&d=06&e=6&f=2013&g=m&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
End With
End If
.Name = _
"MyData"
.FieldNames = True

Teeroy
05-19-2012, 09:32 PM
The properties belong to an object and there's no object specified. If they should apply to the QueryTable then they need to be within the WITH/END WITH block.

asdzxc
05-19-2012, 09:42 PM
The properties belong to an object and there's no object specified. If they should apply to the QueryTable then they need to be within the WITH/END WITH block.


How to apply "If" to the QueryTable

asdzxc
05-19-2012, 11:20 PM
Range("H1").Value = "", give weekly data.
H1>0 ,also give weekly data Dim StrURL As String
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear: On Error GoTo 0
StrURL = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx"
If Range("H1").Value = "" Then
StrURL = StrURL & "&a=10&b=13&c=2011"
StrURL = StrURL & "&g=w&ignore=.xlsx"
Else
StrURL = StrURL & "&a=10&b=13&c=2010"
StrURL = StrURL & "&g=m&ignore=.xlsx"
End If

Teeroy
05-19-2012, 11:34 PM
You can do this simply by changing the IF statement to include an OR if you want the same outcome.

If Range("H1").Value = "" or Range("H1").Value > 0 Then

asdzxc
05-19-2012, 11:45 PM
Range("H1").Value = "", give weekly data.
I want:Range("H1").Value>0, give monthly data.
It seems that macro do not go to
StrURL = StrURL & "&a=10&b=13&c=2010"
StrURL = StrURL & "&g=m&ignore=.xlsx"

Teeroy
05-20-2012, 12:33 AM
It should go there whenever there is any value in H1.

Try:

If Range("H1").Value = "" Then
StrURL = StrURL & "&a=10&b=13&c=2011" _
& "&g=w&ignore=.xlsx"
End If
If val(Range("H1").Value) >0 Then
StrURL = StrURL & "&a=10&b=13&c=2010" _
& "&g=m&ignore=.xlsx"
End If
The first If statements will run if there is a blank cell at H1 and the second will run if H1 is a number greater than 0. Neither will run if there is a string in H1 and all bets are off whether or not the second one will run if there is a date in H1.

asdzxc
05-20-2012, 02:19 AM
It should go there whenever there is any value in H1.

Try:

If Range("H1").Value = "" Then
StrURL = StrURL & "&a=10&b=13&c=2011" _
& "&g=w&ignore=.xlsx"
End If
If val(Range("H1").Value) >0 Then
StrURL = StrURL & "&a=10&b=13&c=2010" _
& "&g=m&ignore=.xlsx"
End If The first If statements will run if there is a blank cell at H1 and the second will run if H1 is a number greater than 0. Neither will run if there is a string in H1 and all bets are off whether or not the second one will run if there is a date in H1.


both give same result

Teeroy
05-20-2012, 03:57 AM
Have you tried building the URL strings and writing them to a worksheet then copying them to a browser to ensure they're going to give you the output you want?

You've made a lot of changes and posting pieces of code can get confusing so if the problem persists could you post an updated spreadsheet.

asdzxc
05-20-2012, 04:33 AM
Have you tried building the URL strings and writing them to a worksheet then copying them to a browser to ensure they're going to give you the output you want?

You've made a lot of changes and posting pieces of code can get confusing so if the problem persists could you post an updated spreadsheet.


spreadsheet with macro attached

Teeroy
05-20-2012, 06:10 AM
OK I did what I'd asked if you'd done and found the problem is not in the VBA it's in the URLS; both URLs you built pull the same CSV data.

After playing around with the URL strings I found the errors and have corrected below. FYI your strings will hard code the dates into the querytable and therefore I wouldn't have built them the same way.


Sub Macro1()
'
' Macro1 Macro
'
Dim StrURL As String
On Error Resume Next
ThisWorkbook.Connections(1).Delete 'This line doesn't work with excel 2003
Err.Clear: On Error GoTo 0
StrURL = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & _
Worksheets("Sheet1").Range("J1").Value
If Range("H1").Value = "" Then
StrURL = StrURL & "&a=10&b=13&c=2011&d=06&e=6&f=2013" _
& "&g=w&ignore=.xlsx"
Else
StrURL = StrURL & "&a=10&b=13&c=2010&d=06&e=6&f=2013" _
& "&g=m&ignore=.xlsx"
End If

With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub

asdzxc
05-20-2012, 06:27 AM
tested. ok
Thank you for spending so much time with me

snb
05-20-2012, 07:14 AM
Instead of removing/adding a querytable you could consider to adapt it's connection property:


Sub snb()
With Sheets("sheet1").QueryTables(1)
.Connection = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=" & 2011 + (Sheets("sheet1").Range("H1") <> "") & "&d=06&e=6&f=2013&g=w&ignore=.xlsx"
.Refresh False
End With
End Sub

asdzxc
05-20-2012, 07:27 AM
Instead of removing/adding a querytable you could consider to adapt it's connection property:


Sub snb()
With Sheets("sheet1").QueryTables(1)
.Connection = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Sheet1").Range("J1").Value & "&a=10&b=13&c=" & 2011 + (Sheets("sheet1").Range("H1") <> "") & "&d=06&e=6&f=2013&g=w&ignore=.xlsx"
.Refresh False
End With
End Sub


Is it possible to merge this code with previos one

asdzxc
05-20-2012, 07:32 AM
below code is smarter ? (http://www.snb-vba.eu) If so, plse check any error
StrURL = "URL;http://ichart.finance.yahoo.com/table.csv?s=" & [Sheet1!J1].Value & IIf(Len([H1]), "w", "m") & "&ignore=.xlsx"
If Range("H1").Value = w Then
StrURL = StrURL & "&a=10&b=13&c=2011&d=06&e=6&f=2013" _ & ""
End If
If Range("H1").Value = m Then StrURL = StrURL & "&a=10&b=13&c=2009&d=06&e=6&f=2013" _ & ""
End If

asdzxc
05-20-2012, 07:54 PM
treat as cancel "Is it possible to merge this code with previos one"