change the 8th column in a not contiguous array

i have this code, that selects a range which may be contiguous, or not
depending on what you select in the listbox.

Private Sub OKButton_Click()

Dim RowRange As Range
Dim Rng As Range
Dim RowCnt As Integer
Dim r As Integer

RowCnt = 0
For r = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(r) Then
RowCnt = RowCnt + 1
If RowCnt = 1 Then
Set RowRange = Range("MyRange").Rows(r + 1)
Else
Set RowRange = Union(RowRange, Range("MyRange").Rows(r + 1))
End If
End If
Next r
If Not RowRange Is Nothing Then RowRange.Select
Unload Me

End Sub


what can i add to it to make it change the 8th column to the value "A" in
the selected ranges only?

TIA

If I understand you correctly, the following should do:

Set rng = Intersect(RowRange, Columns(8))
rng.Value = "A"

Regards,
Greg

"Spencer Hutton" wrote:

> i have this code, that selects a range which may be contiguous, or not
> depending on what you select in the listbox.
>
> Private Sub OKButton_Click()
>
> Dim RowRange As Range
> Dim Rng As Range
> Dim RowCnt As Integer
> Dim r As Integer
>
> RowCnt = 0
> For r = 0 To ListBox1.ListCount - 1
> If ListBox1.Selected(r) Then
> RowCnt = RowCnt + 1
> If RowCnt = 1 Then
> Set RowRange = Range("MyRange").Rows(r + 1)
> Else
> Set RowRange = Union(RowRange, Range("MyRange").Rows(r + 1))
> End If
> End If
> Next r
> If Not RowRange Is Nothing Then RowRange.Select
> Unload Me
>
> End Sub
>
>
> what can i add to it to make it change the 8th column to the value "A" in
> the selected ranges only?
>
> TIA
>

I guess it depends upon what that exactly means. Does 8th column mean 8
columns on from start of range, the 8th column within the columns within the
range? I would guess it is the latter so

RowRange.columns(8)

is not correct, as it could return a column not in RowRange. You could try

Set RowRange = Range("rng")

For iArea = 1 To RowRange.Areas.Count
If cCols + RowRange.Areas(iArea).Columns.Count > 7 Then
iCol = 8 - cCols
Exit For
Else
cCols = cCols + RowRange.Areas(iArea).Columns.Count
End If
Next iArea

Set rng = RowRange.Areas(iArea).Columns(iCol)
rng.Value = "A"



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Spencer Hutton" <SpencerHutton@discussions.microsoft.com> wrote in message
news:D4A95437-B606-4494-A8EB-5DA85F5D64CE@microsoft.com...
> i have this code, that selects a range which may be contiguous, or not
> depending on what you select in the listbox.
>
> Private Sub OKButton_Click()
>
> Dim RowRange As Range
> Dim Rng As Range
> Dim RowCnt As Integer
> Dim r As Integer
>
> RowCnt = 0
> For r = 0 To ListBox1.ListCount - 1
> If ListBox1.Selected(r) Then
> RowCnt = RowCnt + 1
> If RowCnt = 1 Then
> Set RowRange = Range("MyRange").Rows(r + 1)
> Else
> Set RowRange = Union(RowRange, Range("MyRange").Rows(r +
1))
> End If
> End If
> Next r
> If Not RowRange Is Nothing Then RowRange.Select
> Unload Me
>
> End Sub
>
>
> what can i add to it to make it change the 8th column to the value "A" in
> the selected ranges only?
>
> TIA
>

thank you for your reply, but this is not working as i would have liked. i
don't think i really explained it that great.
i have a range which certain rows of THAt range get selected depending on
the users choices in the list box. what i am ultimately wanting to have this
code do, is change the 8th column of the range, not necessarily in the sheet,
but in this particular named range ("MyRange"), to "A". every value in
column 8 of this range, which is actually column BA in the sheet, has a
different single letter value. i want to change those letter values to "A",
but only the itms that are selected in the list box. so even if the list box
was forgotten about ang i just selected say 4 non contiguous ranges in
"MyRange", how can i change the 8h column in "MyRange" to "A" for the
selected items only. thank you very much for your attention.

"Bob Phillips" wrote:

> I guess it depends upon what that exactly means. Does 8th column mean 8
> columns on from start of range, the 8th column within the columns within the
> range? I would guess it is the latter so
>
> RowRange.columns(8)
>
> is not correct, as it could return a column not in RowRange. You could try
>
> Set RowRange = Range("rng")
>
> For iArea = 1 To RowRange.Areas.Count
> If cCols + RowRange.Areas(iArea).Columns.Count > 7 Then
> iCol = 8 - cCols
> Exit For
> Else
> cCols = cCols + RowRange.Areas(iArea).Columns.Count
> End If
> Next iArea
>
> Set rng = RowRange.Areas(iArea).Columns(iCol)
> rng.Value = "A"
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Spencer Hutton" <SpencerHutton@discussions.microsoft.com> wrote in message
> news:D4A95437-B606-4494-A8EB-5DA85F5D64CE@microsoft.com...
> > i have this code, that selects a range which may be contiguous, or not
> > depending on what you select in the listbox.
> >
> > Private Sub OKButton_Click()
> >
> > Dim RowRange As Range
> > Dim Rng As Range
> > Dim RowCnt As Integer
> > Dim r As Integer
> >
> > RowCnt = 0
> > For r = 0 To ListBox1.ListCount - 1
> > If ListBox1.Selected(r) Then
> > RowCnt = RowCnt + 1
> > If RowCnt = 1 Then
> > Set RowRange = Range("MyRange").Rows(r + 1)
> > Else
> > Set RowRange = Union(RowRange, Range("MyRange").Rows(r +
> 1))
> > End If
> > End If
> > Next r
> > If Not RowRange Is Nothing Then RowRange.Select
> > Unload Me
> >
> > End Sub
> >
> >
> > what can i add to it to make it change the 8th column to the value "A" in
> > the selected ranges only?
> >
> > TIA
> >
>
>
>

Intersect(selection,Columns("BA")).Cells.Value = "A"

--
Regards,
Tom Ogilvy

"Spencer Hutton" <SpencerHutton@discussions.microsoft.com> wrote in message
news:46307185-01F7-420B-99F6-93C3C0BCEB9D@microsoft.com...
> thank you for your reply, but this is not working as i would have liked.
i
> don't think i really explained it that great.
> i have a range which certain rows of THAt range get selected depending on
> the users choices in the list box. what i am ultimately wanting to have
this
> code do, is change the 8th column of the range, not necessarily in the
sheet,
> but in this particular named range ("MyRange"), to "A". every value in
> column 8 of this range, which is actually column BA in the sheet, has a
> different single letter value. i want to change those letter values to
"A",
> but only the itms that are selected in the list box. so even if the list
box
> was forgotten about ang i just selected say 4 non contiguous ranges in
> "MyRange", how can i change the 8h column in "MyRange" to "A" for the
> selected items only. thank you very much for your attention.
>
> "Bob Phillips" wrote:
>
> > I guess it depends upon what that exactly means. Does 8th column mean 8
> > columns on from start of range, the 8th column within the columns within
the
> > range? I would guess it is the latter so
> >
> > RowRange.columns(8)
> >
> > is not correct, as it could return a column not in RowRange. You could
try
> >
> > Set RowRange = Range("rng")
> >
> > For iArea = 1 To RowRange.Areas.Count
> > If cCols + RowRange.Areas(iArea).Columns.Count > 7 Then
> > iCol = 8 - cCols
> > Exit For
> > Else
> > cCols = cCols + RowRange.Areas(iArea).Columns.Count
> > End If
> > Next iArea
> >
> > Set rng = RowRange.Areas(iArea).Columns(iCol)
> > rng.Value = "A"
> >
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Spencer Hutton" <SpencerHutton@discussions.microsoft.com> wrote in
message
> > news:D4A95437-B606-4494-A8EB-5DA85F5D64CE@microsoft.com...
> > > i have this code, that selects a range which may be contiguous, or not
> > > depending on what you select in the listbox.
> > >
> > > Private Sub OKButton_Click()
> > >
> > > Dim RowRange As Range
> > > Dim Rng As Range
> > > Dim RowCnt As Integer
> > > Dim r As Integer
> > >
> > > RowCnt = 0
> > > For r = 0 To ListBox1.ListCount - 1
> > > If ListBox1.Selected(r) Then
> > > RowCnt = RowCnt + 1
> > > If RowCnt = 1 Then
> > > Set RowRange = Range("MyRange").Rows(r + 1)
> > > Else
> > > Set RowRange = Union(RowRange, Range("MyRange").Rows(r
+
> > 1))
> > > End If
> > > End If
> > > Next r
> > > If Not RowRange Is Nothing Then RowRange.Select
> > > Unload Me
> > >
> > > End Sub
> > >
> > >
> > > what can i add to it to make it change the 8th column to the value "A"
in
> > > the selected ranges only?
> > >
> > > TIA
> > >
> >
> >
> >

Worked great, thanks again Tom.

"Tom Ogilvy" wrote:

> Intersect(selection,Columns("BA")).Cells.Value = "A"
>
> --
> Regards,
> Tom Ogilvy
>
> "Spencer Hutton" <SpencerHutton@discussions.microsoft.com> wrote in message
> news:46307185-01F7-420B-99F6-93C3C0BCEB9D@microsoft.com...
> > thank you for your reply, but this is not working as i would have liked.
> i
> > don't think i really explained it that great.
> > i have a range which certain rows of THAt range get selected depending on
> > the users choices in the list box. what i am ultimately wanting to have
> this
> > code do, is change the 8th column of the range, not necessarily in the
> sheet,
> > but in this particular named range ("MyRange"), to "A". every value in
> > column 8 of this range, which is actually column BA in the sheet, has a
> > different single letter value. i want to change those letter values to
> "A",
> > but only the itms that are selected in the list box. so even if the list
> box
> > was forgotten about ang i just selected say 4 non contiguous ranges in
> > "MyRange", how can i change the 8h column in "MyRange" to "A" for the
> > selected items only. thank you very much for your attention.
> >
> > "Bob Phillips" wrote:
> >
> > > I guess it depends upon what that exactly means. Does 8th column mean 8
> > > columns on from start of range, the 8th column within the columns within
> the
> > > range? I would guess it is the latter so
> > >
> > > RowRange.columns(8)
> > >
> > > is not correct, as it could return a column not in RowRange. You could
> try
> > >
> > > Set RowRange = Range("rng")
> > >
> > > For iArea = 1 To RowRange.Areas.Count
> > > If cCols + RowRange.Areas(iArea).Columns.Count > 7 Then
> > > iCol = 8 - cCols
> > > Exit For
> > > Else
> > > cCols = cCols + RowRange.Areas(iArea).Columns.Count
> > > End If
> > > Next iArea
> > >
> > > Set rng = RowRange.Areas(iArea).Columns(iCol)
> > > rng.Value = "A"
> > >
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Spencer Hutton" <SpencerHutton@discussions.microsoft.com> wrote in
> message
> > > news:D4A95437-B606-4494-A8EB-5DA85F5D64CE@microsoft.com...
> > > > i have this code, that selects a range which may be contiguous, or not
> > > > depending on what you select in the listbox.
> > > >
> > > > Private Sub OKButton_Click()
> > > >
> > > > Dim RowRange As Range
> > > > Dim Rng As Range
> > > > Dim RowCnt As Integer
> > > > Dim r As Integer
> > > >
> > > > RowCnt = 0
> > > > For r = 0 To ListBox1.ListCount - 1
> > > > If ListBox1.Selected(r) Then
> > > > RowCnt = RowCnt + 1
> > > > If RowCnt = 1 Then
> > > > Set RowRange = Range("MyRange").Rows(r + 1)
> > > > Else
> > > > Set RowRange = Union(RowRange, Range("MyRange").Rows(r
> +
> > > 1))
> > > > End If
> > > > End If
> > > > Next r
> > > > If Not RowRange Is Nothing Then RowRange.Select
> > > > Unload Me
> > > >
> > > > End Sub
> > > >
> > > >
> > > > what can i add to it to make it change the 8th column to the value "A"
> in
> > > > the selected ranges only?
> > > >
> > > > TIA
> > > >
> > >
> > >
> > >
>
>
>