Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change the 8th column in a not contiguous array
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change the 8th column in a not contiguous array
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change the 8th column in a not contiguous array
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
change the 8th column in a not contiguous array
Intersect(selection,Columns("BA")).Cells.Value = "A"
-- Regards, Tom Ogilvy "Spencer Hutton" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
change the 8th column in a not contiguous array
Worked great, thanks again Tom.
"Tom Ogilvy" wrote: Intersect(selection,Columns("BA")).Cells.Value = "A" -- Regards, Tom Ogilvy "Spencer Hutton" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & non-contiguous table array | Excel Worksheet Functions | |||
Finding the longest contiguous 1-d array of 0's | Excel Discussion (Misc queries) | |||
summing non-contiguous array cells | Excel Worksheet Functions | |||
Multiplying Contiguous Values in an Array | Excel Worksheet Functions | |||
Creating an array from non-contiguous ranges | Excel Programming |