Procedure not working, and I do not know why

[Sibilia]

Hi all,

I have a worksheet call X (list of scenario) shown on the screen, and I have a VBA code which should work on another sheet without showing it on the screen.

my code is:

Sub DeleteScenario()

Dim j As Integer
Dim k As Integer
Dim count As Integer
Dim SavedScenario1
Dim SavedScenario2

SavedScenario1 = Cells(4, "AB").Value

' sheet "list scenario" activated
With Worksheets("Scenario")
j = 3
Do
j = j + 1

SavedScenario2 = .Cells(j, "A")
Loop Until SavedScenario2 = SavedScenario1
.Rows(j).ClearContents
.Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy

This is where the procedure bug, and I do not understand why

.Range(j, "A").PasteSpecial Paste:=xlPasteValues
.Range("A20:AU50").ClearContents
End With
End Sub

Many thanks for your help

Sibilia

[BAC]

..Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy

should be

..Range(Cells(j + 1, "A"):Cells(20, "AU")).Copy (Replace , with

Please rate this post below

Thanx
BAC




"Sibilia" wrote:

>
> Hi all,
>
> I have a worksheet call X (list of scenario) shown on the screen, and I
> have a VBA code which should work on another sheet without showing it on
> the screen.
>
> my code is:
>
> Sub DeleteScenario()
>
> Dim j As Integer
> Dim k As Integer
> Dim count As Integer
> Dim SavedScenario1
> Dim SavedScenario2
>
> SavedScenario1 = Cells(4, "AB").Value
>
> ' sheet "list scenario" activated
> With Worksheets("Scenario")
> j = 3
> Do
> j = j + 1
>
> SavedScenario2 = .Cells(j, "A")
> Loop Until SavedScenario2 = SavedScenario1
> .Rows(j).ClearContents
> .Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy
>
> * This is where the procedure bug, and I do not understand why*
>
> .Range(j, "A").PasteSpecial Paste:=xlPasteValues
> .Range("A20:AU50").ClearContents
> End With
> End Sub
>
> Many thanks for your help
>
> Sibilia
>
>
> --
> Sibilia
> ------------------------------------------------------------------------
> Sibilia's Profile: http://www.excelforum.com/member.php...o&userid=21363
> View this thread: http://www.excelforum.com/showthread...hreadid=380166
>
>

[Sibilia]

Hi, the problem cannot be the " ," rather than ":" as I am using cells object. it is the way of writting it, it is why I do not understand why it does not work for this specific procedure where it works in an other.
I think that may be due to the fact that I am doing copy-paste in a sheet which is not show on the screen;

Any other possible solution ?

Many thanx

Sibilia

..Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy

should be

..Range(Cells(j + 1, "A"):Cells(20, "AU")).Copy (Replace , with

Please rate this post below

Thanx
BAC




"Sibilia" wrote:

>
> Hi all,
>
> I have a worksheet call X (list of scenario) shown on the screen, and I
> have a VBA code which should work on another sheet without showing it on
> the screen.
>
> my code is:
>
> Sub DeleteScenario()
>
> Dim j As Integer
> Dim k As Integer
> Dim count As Integer
> Dim SavedScenario1
> Dim SavedScenario2
>
> SavedScenario1 = Cells(4, "AB").Value
>
> ' sheet "list scenario" activated
> With Worksheets("Scenario")
> j = 3
> Do
> j = j + 1
>
> SavedScenario2 = .Cells(j, "A")
> Loop Until SavedScenario2 = SavedScenario1
> .Rows(j).ClearContents
> .Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy
>
> * This is where the procedure bug, and I do not understand why*
>
> .Range(j, "A").PasteSpecial Paste:=xlPasteValues
> .Range("A20:AU50").ClearContents
> End With
> End Sub
>
> Many thanks for your help
>
> Sibilia
>
>
> --
> Sibilia
> ------------------------------------------------------------------------
> Sibilia's Profile: http://www.excelforum.com/member.php...o&userid=21363
> View this thread: http://www.excelforum.com/showthread...hreadid=380166
>
>

[Norman Jones]

Hi Sibilia,

Try the following amended version of your code (my arrowed comments follow
the code):

Sub DeleteScenario()

Dim j As Integer
Dim k As Integer
Dim count As Integer
Dim SavedScenario1 As String '<<<
#1
Dim SavedScenario2 As String '<<<
#2

' sheet "list scenario" activated
'<<< #3
With Worksheets("Scenario")
SavedScenario1 = .Cells(4, "AB").Value '<<< #4

j = 3
Do
j = j + 1

SavedScenario2 = .Cells(j, "A")
Loop Until SavedScenario2 = SavedScenario1
.Rows(j).ClearContent
'<<< #5
.Range(.Cells(j + 1, "A"), .Cells(20, "AU")).Copy '<<< #6

'* This is where the procedure bug, and I do not understand why*

.Cells(j, "A").PasteSpecial Paste:=xlPasteValues '<<<
#7
.Range("A20:AU50").ClearContents '<<<
#8
End With
End Sub


#1 It is always advisable to explicitly dim all variables. If the
Saved Scenario variable is not a string, amend.
#2 Ditto
#3 This comment is wrong (or, at least, misleading); the With ... End
With
construct does not *activate* any sheet.
#4 I have moved this line into the With...End With clause and prepended
it with a ,(dot) to ensure that it refers to the Scenario sheet. As
originally
written, it would refer to the active sheet which, you indicate, is
not
the Scenario sheet.
#5 Rows has been prepended with a . dot - see #4
#6 Range and each instance of Cells have been prepended with . (dot) -
see #4
#7 Cells has been prepended with a . (dot) - see #4
#8 Range has been prepended with a . (dot) - see #4

---
Regards,
Norman


"Sibilia" <Sibilia.1qs8mf_1119031515.5041@excelforum-nospam.com> wrote in
message news:Sibilia.1qs8mf_1119031515.5041@excelforum-nospam.com...
>
> Hi all,
>
> I have a worksheet call X (list of scenario) shown on the screen, and I
> have a VBA code which should work on another sheet without showing it on
> the screen.
>
> my code is:
>
> Sub DeleteScenario()
>
> Dim j As Integer
> Dim k As Integer
> Dim count As Integer
> Dim SavedScenario1
> Dim SavedScenario2
>
> SavedScenario1 = Cells(4, "AB").Value
>
> ' sheet "list scenario" activated
> With Worksheets("Scenario")
> j = 3
> Do
> j = j + 1
>
> SavedScenario2 = .Cells(j, "A")
> Loop Until SavedScenario2 = SavedScenario1
> Rows(j).ClearContents
> Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy
>
> * This is where the procedure bug, and I do not understand why*
>
> Range(j, "A").PasteSpecial Paste:=xlPasteValues
> Range("A20:AU50").ClearContents
> End With
> End Sub
>
> Many thanks for your help
>
> Sibilia
>
>
> --
> Sibilia
> ------------------------------------------------------------------------
> Sibilia's Profile:
> http://www.excelforum.com/member.php...o&userid=21363
> View this thread: http://www.excelforum.com/showthread...hreadid=380166
>

[Sibilia]

Hi Norman

THANKS a lot - it works and the message was very clear, with comments etc.....
Very much appreciated!

Hi Sibilia,

Try the following amended version of your code (my arrowed comments follow
the code):

Sub DeleteScenario()

Dim j As Integer
Dim k As Integer
Dim count As Integer
Dim SavedScenario1 As String '<<<
#1
Dim SavedScenario2 As String '<<<
#2

' sheet "list scenario" activated
'<<< #3
With Worksheets("Scenario")
SavedScenario1 = .Cells(4, "AB").Value '<<< #4

j = 3
Do
j = j + 1

SavedScenario2 = .Cells(j, "A")
Loop Until SavedScenario2 = SavedScenario1
.Rows(j).ClearContent
'<<< #5
.Range(.Cells(j + 1, "A"), .Cells(20, "AU")).Copy '<<< #6

'* This is where the procedure bug, and I do not understand why*

.Cells(j, "A").PasteSpecial Paste:=xlPasteValues '<<<
#7
.Range("A20:AU50").ClearContents '<<<
#8
End With
End Sub


#1 It is always advisable to explicitly dim all variables. If the
Saved Scenario variable is not a string, amend.
#2 Ditto
#3 This comment is wrong (or, at least, misleading); the With ... End
With
construct does not *activate* any sheet.
#4 I have moved this line into the With...End With clause and prepended
it with a ,(dot) to ensure that it refers to the Scenario sheet. As
originally
written, it would refer to the active sheet which, you indicate, is
not
the Scenario sheet.
#5 Rows has been prepended with a . dot - see #4
#6 Range and each instance of Cells have been prepended with . (dot) -
see #4
#7 Cells has been prepended with a . (dot) - see #4
#8 Range has been prepended with a . (dot) - see #4

---
Regards,
Norman


"Sibilia" <Sibilia.1qs8mf_1119031515.5041@excelforum-nospam.com> wrote in
message news:Sibilia.1qs8mf_1119031515.5041@excelforum-nospam.com...
>
> Hi all,
>
> I have a worksheet call X (list of scenario) shown on the screen, and I
> have a VBA code which should work on another sheet without showing it on
> the screen.
>
> my code is:
>
> Sub DeleteScenario()
>
> Dim j As Integer
> Dim k As Integer
> Dim count As Integer
> Dim SavedScenario1
> Dim SavedScenario2
>
> SavedScenario1 = Cells(4, "AB").Value
>
> ' sheet "list scenario" activated
> With Worksheets("Scenario")
> j = 3
> Do
> j = j + 1
>
> SavedScenario2 = .Cells(j, "A")
> Loop Until SavedScenario2 = SavedScenario1
> Rows(j).ClearContents
> Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy
>
> * This is where the procedure bug, and I do not understand why*
>
> Range(j, "A").PasteSpecial Paste:=xlPasteValues
> Range("A20:AU50").ClearContents
> End With
> End Sub
>
> Many thanks for your help
>
> Sibilia
>
>
> --
> Sibilia
> ------------------------------------------------------------------------
> Sibilia's Profile:
> http://www.excelforum.com/member.php...o&userid=21363
> View this thread: http://www.excelforum.com/showthread...hreadid=380166
>