Need help changing Range

[Todd Huttenstine]

Below is a working code... Instead of the range being
IV1:IV100 I would like for the range to be on worksheets
(4) J2:J101.

Currently it is looking on the active sheet which is
sheet1. I am not sure what to do with line " Set Rng =
Range("IV1")"

Private Sub CommandButton58_Click()
'Applies the highlight settings to sheet Team Data
'Will highlight data as far as Column IV and as far down
as Row 100

Range("IV1:IV100").Value = ""
Dim Rng As Range
Dim Ndx As Long
Set Rng = Range("IV1")
With Me.ListBox1
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
Rng.Value = .List(Ndx)
Set Rng = Rng(2, 1)
End If
Next Ndx
End With
Unload Me
End Sub

thanx in advance.

Todd

 

[Bob Phillips]

Todd,

Does this do it?

Worksheets(4).Range("J2:J101").Value = ""
Dim Rng As Range
Dim Ndx As Long
Set Rng = Worksheets(4).Range("J2")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

How about:

worksheets(4).range("j2:j101").value = ""
'or
'worksheets(4).range("j2:j101").clearcontents

And then
Set rng = worksheets(4).range("J2")

IV1 was supposed to change to J2 of worksheet 4?

 

[Todd Huttenstine]

Hey guys,

I got it. Heres what I used. Thank you both for your
replys.

Worksheets(4).Range("J2:J101").Value = ""
Dim Rng As Range
Dim Ndx As Long
Set Rng = Worksheets(4).Range("J2")
With Me.ListBox1
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
Rng.Value = .List(Ndx)
Set Rng = Rng(2, 1)
End If
Next Ndx
End With
Unload Me
End Sub