Conflict

  • Thread starter Thread starter smandula
  • Start date Start date
S

smandula

I have a conflict in VBA
-------------------------------------
Sub sequence11()
With Sheets("Sheet2")
x5 = Range("J1").Value
x6 = Range("J2").Value
Set x = .Range("B" & x6 & ":G" & x5)
End With

For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
..Color = RGB(255, 204, 153) '222, 222, 222 Gray
..Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub
'---------------------------------------------------
Sub bonussequence()
With Sheets("Sheet2")
Set x = .Range(.Range("K2"), .Range("N31"))
End With
For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub
'----------------------------------------------------

The last macro will not finish

Both are used on the same sheet,
Both have different objects,or GUI

When tried separately on different worksheets, they work
Any suggestions, I would like to have them work indepent,
on same worksheet.

With Thanks
 
Hi,

Am Thu, 1 Jan 2015 19:04:47 -0800 (PST) schrieb smandula:
The last macro will not finish
Click to expand...

try:

Sub bonussequence()
With Sheets("Sheet2")
Set x = .Range("K2:N31")
For Each c In x
If c = c.Offset(0, 1) - 1 Then
c.Resize(, 2).Interior.ColorIndex = 40
End If
Next
End With
End Sub


Regards
Claus B.
 
I have a conflict in VBA
-------------------------------------
Sub sequence11()
With Sheets("Sheet2")
x5 = Range("J1").Value
x6 = Range("J2").Value
Set x = .Range("B" & x6 & ":G" & x5)
End With

For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
.Color = RGB(255, 204, 153) '222, 222, 222 Gray
.Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub
'---------------------------------------------------
Sub bonussequence()
With Sheets("Sheet2")
Set x = .Range(.Range("K2"), .Range("N31"))
End With
For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub
'----------------------------------------------------

The last macro will not finish

Both are used on the same sheet,
Both have different objects,or GUI

When tried separately on different worksheets, they work
Any suggestions, I would like to have them work indepent,
on same worksheet.

With Thanks
Click to expand...

Thanks Claus,
Your solution works by itself
However,when used on Sheet 2, the macro stops midway in rqange.

The error report is

run time error '13'
Type mismatch

I don't know what the problem is?

With Thanks
 
For me, the original code fails on any sheet other than Sheet2, because of the Select step. It only works ActiveSheet, otherwise giving
Run-time error '1004'
Select method of Range class failed.

Claus's method of c.property = xxx should work regardless of which sheet is active.

What is c.address, c.value, and c.offset(0,1).value when the error occurs?

Carl.
 
Hi,

Am Fri, 2 Jan 2015 18:26:41 -0800 (PST) schrieb smandula:
Type mismatch
Click to expand...

how did you declare the variables?
Dim x As Range, c As Range


Regards
Claus B.
 
Hi again,

Am Sat, 3 Jan 2015 17:14:12 +0100 schrieb Claus Busch:
how did you declare the variables?
Dim x As Range, c As Range
Click to expand...

if in c.offset(,1) is text then you get the mismatch error.
Try:
Sub bonussequence()
Dim x As Range, c As Range

With Sheets("Sheet2")
Set x = .Range("K2:N31")
For Each c In x
If IsNumeric(c.Offset(, 1)) Then
If c = c.Offset(0, 1) - 1 Then
c.Resize(, 2).Interior.ColorIndex = 40
End If
End If
Next
End With
End Sub


Regards
Claus B.
 
Went for a walk, thought of the problem. The comparison for the right-mostcolumn is to a cell outside the range, which contains a value which fails the compare. Claus's IsNumeric check is one way to handle it, another is to restrict the checks to not include the right-most column.
 
For me, the original code fails on any sheet other than Sheet2,
because of the Select step. It only works ActiveSheet, otherwise
giving
Run-time error '1004'
Select method of Range class failed.
Click to expand...

It only works for "Sheet2" because it's hard-coded to only work on
"Sheet2"!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I have a conflict in VBA
-------------------------------------
Sub sequence11()
With Sheets("Sheet2")
x5 = Range("J1").Value
x6 = Range("J2").Value
Set x = .Range("B" & x6 & ":G" & x5)
End With

For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
.Color = RGB(255, 204, 153) '222, 222, 222 Gray
.Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub
'---------------------------------------------------
Sub bonussequence()
With Sheets("Sheet2")
Set x = .Range(.Range("K2"), .Range("N31"))
End With
For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub
'----------------------------------------------------

The last macro will not finish

Both are used on the same sheet,
Both have different objects,or GUI

When tried separately on different worksheets, they work
Any suggestions, I would like to have them work indepent,
on same worksheet.

With Thanks
Click to expand...

Many, Many Thanks Claus

The solution works perfectly.
Thanks for reply, and especially
your solution.
I couldn't do it.

Bye for Now
 
Back
Top