Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

Kjeldc:
Thanks for your fast answer!
I'm sorry I wasn't clear before. The End sub is already there, quite a few
lines down!

Thanks for your help. The problem is that even with the "end sub" at the end
of the code I keep getting that messge!

Do you have any ideas?
Thanks,
Alex


"Kjeldc" wrote:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
end sub

"Alex Martins" skrev:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
end sub

"Alex Martins" skrev:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

The code runs at the change event for the sheet when the cell F2 is changed.
If F2 is a formula then change the code to something like


Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" or target.address = "$A$2" or _
target.address = "$B$3" or then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

Hey Jim...it's not working. Actually, that cell changes value because it is a
formula( sumif(range;"do";sum_range)).

Evidently, I am failing to make this code take a change in this formula as a
change in event.

If you come up with something I'll be much obliged. And thanks for all the
help given already!

Thanks,
Alex

"Jim Thomlinson" wrote:

The code runs at the change event for the sheet when the cell F2 is changed.
If F2 is a formula then change the code to something like


Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" or target.address = "$A$2" or _
target.address = "$B$3" or then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

The change in the value of a formula is not specifically a change event. If
it were then changing one cell referenced by 20 formuals would instagate 20
change events. The change code needs to reference the cells which are the
precidents of the formula. For example if F2 has =A1 + B1 in it then the
change formula needs to be

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$A$1" target.address = "$B$1" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

If the formula is something more like =Sum(A1: B1000) the I can give you the
code to monitor changes in that range of cells.
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...it's not working. Actually, that cell changes value because it is a
formula( sumif(range;"do";sum_range)).

Evidently, I am failing to make this code take a change in this formula as a
change in event.

If you come up with something I'll be much obliged. And thanks for all the
help given already!

Thanks,
Alex

"Jim Thomlinson" wrote:

The code runs at the change event for the sheet when the cell F2 is changed.
If F2 is a formula then change the code to something like


Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" or target.address = "$A$2" or _
target.address = "$B$3" or then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String

and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"

Thanks in advance for your help

Alex

Hey Jim. I changed the formula to this: sum(h12:h1000); can we monitor
changes in this?

Many thanks man. You da man.

Alex

"Jim Thomlinson" wrote:

The change in the value of a formula is not specifically a change event. If
it were then changing one cell referenced by 20 formuals would instagate 20
change events. The change code needs to reference the cells which are the
precidents of the formula. For example if F2 has =A1 + B1 in it then the
change formula needs to be

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$A$1" target.address = "$B$1" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

If the formula is something more like =Sum(A1: B1000) the I can give you the
code to monitor changes in that range of cells.
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...it's not working. Actually, that cell changes value because it is a
formula( sumif(range;"do";sum_range)).

Evidently, I am failing to make this code take a change in this formula as a
change in event.

If you come up with something I'll be much obliged. And thanks for all the
help given already!

Thanks,
Alex

"Jim Thomlinson" wrote:

The code runs at the change event for the sheet when the cell F2 is changed.
If F2 is a formula then change the code to something like


Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" or target.address = "$A$2" or _
target.address = "$B$3" or then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.

I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com

The only problem is that my macro code will now start-out like this:

Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)

Private Sub Worksheet_Change(ByVal Target As Range)
if not intersect(target, range("H12:H1000")) is nothing
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. I changed the formula to this: sum(h12:h1000); can we monitor
changes in this?

Many thanks man. You da man.

Alex

"Jim Thomlinson" wrote:

The change in the value of a formula is not specifically a change event. If
it were then changing one cell referenced by 20 formuals would instagate 20
change events. The change code needs to reference the cells which are the
precidents of the formula. For example if F2 has =A1 + B1 in it then the
change formula needs to be

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$A$1" target.address = "$B$1" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

If the formula is something more like =Sum(A1: B1000) the I can give you the
code to monitor changes in that range of cells.
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...it's not working. Actually, that cell changes value because it is a
formula( sumif(range;"do";sum_range)).

Evidently, I am failing to make this code take a change in this formula as a
change in event.

If you come up with something I'll be much obliged. And thanks for all the
help given already!

Thanks,
Alex

"Jim Thomlinson" wrote:

The code runs at the change event for the sheet when the cell F2 is changed.
If F2 is a formula then change the code to something like


Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" or target.address = "$A$2" or _
target.address = "$B$3" or then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then

else

endif
End Sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hi, I have been working on a Macro from some time now, and I find myself in

thank you very much for all your help.

Alex

"Jim Thomlinson" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if not intersect(target, range("H12:H1000")) is nothing
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. I changed the formula to this: sum(h12:h1000); can we monitor
changes in this?

Many thanks man. You da man.

Alex

"Jim Thomlinson" wrote:

The change in the value of a formula is not specifically a change event. If
it were then changing one cell referenced by 20 formuals would instagate 20
change events. The change code needs to reference the cells which are the
precidents of the formula. For example if F2 has =A1 + B1 in it then the
change formula needs to be

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$A$1" target.address = "$B$1" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

If the formula is something more like =Sum(A1: B1000) the I can give you the
code to monitor changes in that range of cells.
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...it's not working. Actually, that cell changes value because it is a
formula( sumif(range;"do";sum_range)).

Evidently, I am failing to make this code take a change in this formula as a
change in event.

If you come up with something I'll be much obliged. And thanks for all the
help given already!

Thanks,
Alex

"Jim Thomlinson" wrote:

The code runs at the change event for the sheet when the cell F2 is changed.
If F2 is a formula then change the code to something like


Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" or target.address = "$A$2" or _
target.address = "$B$3" or then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Yes!!!! It finally works! Thanks for your help man!

There is one problem though...it only runs when I manually put "0" or a
number different to it....

I used to have a formula...why didn't this work?

Thanks,
Alex


"Jim Thomlinson" wrote:

Try something like this (This code is placed in the NEW FILINGS sheet by
right clicking on the Tab in Excel and selecting View Code - Paste ht code
int the code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$F$2" then
if target.value = 0 then
Call DoThis
else
Call DoThat
endif
end if
end sub

Sub DoThis()
msgbox "F2 = 0"
'Place your "=0" code here
end sub

sub DoThat
msgbox "F2 < 0"
'Place your "<0" Code here
ennd sub
--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has
a value = 0. If not, I want the part with sheets "T" and "T-1" to run...

Do yu have any suggestions? I really appreciate yur help and the time
devoted to helpnig me out with this.

thanks,
Alex


"Jim Thomlinson" wrote:

This is event code so it will not appear in the play list. Any procedure that
requires an input (in this case Target as Range) will not appear in the play
list as it can't run without an input value supplied. To fire the code change
any value on the sheet in which this code resides and the code should run. To
confirm it is running add a message box something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox Target.address & " was changed."
'now your code...
end sub

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!

Thanks for your help!

"Jim Thomlinson" wrote:

Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line

If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then

compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.

If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then

would probably make more sense...

--
HTH...

Jim Thomlinson


"Alex Martins" wrote:

Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!

Thanks for all your help.

Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub


"Jim Thomlinson" wrote:

Every sub needs an end sub. You code should end up looking something like this

Sub new_filings()
'Do something in here
end sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)