change the 8th column in a not contiguous array

[Spencer Hutton]

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

 

[Greg Wilson]

If I understand you correctly, the following should do:

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

Regards,
Greg

 

[Bob Phillips]

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]

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.

 

[Tom Ogilvy]

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

 

[Spencer Hutton]

Worked great, thanks again Tom.

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