Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
Sum first five vaules of changing range | Excel Discussion (Misc queries) | |||
Deleting or changing a range name | Excel Discussion (Misc queries) | |||
calculating in a changing range | Excel Discussion (Misc queries) | |||
changing a range of fields | Excel Discussion (Misc queries) |