VB syntax problem with referencing another tab

[Wombat]

Hi. The following macro works fine if I run it while the tab in question is
open. If I try to run it with another tab open, I get a 400 error. I'm pretty
sure it's just a problem with the way I'm referencing the tab. Any ideas on
how I could make this work?


Sub Formatierung_Einverkauf()

Set sh1 = Sheets("Einverkauf")

z = 4
Do Until sh1.Cells(z, 1).Value = ""
If sh1.Cells(z, 1).Value <> sh1.Cells(z + 1, 1).Value Then
sh1.Rows(z).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
z = z + 1
Else
z = z + 1
End If

Loop

End Sub

Thanks a lot

 

[Bob Phillips]

This works fine for me

Sub Formatierung_Einverkauf()
Dim z As Long
z = 4
With Sheets("Einverkauf")
Do Until .Cells(z, 1).Value = ""
If .Cells(z, 1).Value <> .Cells(z + 1, 1).Value Then
With .Rows(z).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If
z = z + 1
Loop
End With
End Sub

 

[Mike H]

Hi,

There is vertually never any need to select cells to perform operations on
them and this was the issue with this code, you tried to select on an
inactive sheet. Do it this way

Sub Formatierung_Einverkauf()
Set sh1 = Sheets("Einverkauf")
Z = 4
Do Until sh1.Cells(Z, 1).Value = ""
If sh1.Cells(Z, 1).Value <> sh1.Cells(Z + 1, 1).Value Then
With sh1.Rows(Z).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Z = Z + 1
Else
Z = Z + 1
End If
Loop
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.