Need help changing Range
 

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

Need help changing Range
 

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)

"Todd Huttenstine" wrote in message
...
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

Need help changing Range
 

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 wrote:

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

--

Dave Peterson

Need help changing Range
 

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


-----Original Message-----
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
.