Hi all,
Been tinkering with this for a while. I don't do much coding though I'm learning.
I have a range of cells that looks like this:
This is just one scenario. In reality the cells of equal value might be two cells or 10 cells high.
I've got a test in another cell outside the main data table that compares values in K12 & K13 to determine if they are equal - and if they are it places the tied score into "U25". If K12 & K13 are equal, all competitors with the same value in K12 must enter a sudden death elimination event to determine who is in the top 6.
That will result in an additional column of values for each in column L (in this case L10:L15) after that event.
I need to select from Fx:Ly (in the above example F10:L15) after the sudden death elimination so I can do a sort.
I've found a sub routine on another site that I've nutted out and got it to work but it was based on testing for text (FALSE). I need to test for the number in a cell. If I use this routine and put FALSE in "U25" it selects the range I want. I need it to test for the value in U25 instead.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; min-height: 13.0px}span.s1 {color: #011993}span.s2 {color: #000000}</style>Sub test()
Dim lastrow As Long
Dim c As Range, rng As Range
'change Sheet1 to suit
With ThisWorkbook.Worksheets("TR125 Final")
lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
For Each c In .Range("K1:K" & lastrow)
If UCase(c.Text) = "FALSE" Then
If rng Is Nothing Then
Set rng = .Range("F" & c.Row).Resize(, 7)
Else
Set rng = Union(rng, .Range("F" & c.Row).Resize(, 7))
End If
End If
Next c
End With
If Not rng Is Nothing Then rng.Select
End Sub
What do I change the line If UCase... etc in red above to, so that it tests for a value in a cell? eg. U25 where I'm currently putting the value.
Thanks,
Dave
Been tinkering with this for a while. I don't do much coding though I'm learning.
I have a range of cells that looks like this:
This is just one scenario. In reality the cells of equal value might be two cells or 10 cells high.
I've got a test in another cell outside the main data table that compares values in K12 & K13 to determine if they are equal - and if they are it places the tied score into "U25". If K12 & K13 are equal, all competitors with the same value in K12 must enter a sudden death elimination event to determine who is in the top 6.
That will result in an additional column of values for each in column L (in this case L10:L15) after that event.
I need to select from Fx:Ly (in the above example F10:L15) after the sudden death elimination so I can do a sort.
I've found a sub routine on another site that I've nutted out and got it to work but it was based on testing for text (FALSE). I need to test for the number in a cell. If I use this routine and put FALSE in "U25" it selects the range I want. I need it to test for the value in U25 instead.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; min-height: 13.0px}span.s1 {color: #011993}span.s2 {color: #000000}</style>Sub test()
Dim lastrow As Long
Dim c As Range, rng As Range
'change Sheet1 to suit
With ThisWorkbook.Worksheets("TR125 Final")
lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
For Each c In .Range("K1:K" & lastrow)
If UCase(c.Text) = "FALSE" Then
If rng Is Nothing Then
Set rng = .Range("F" & c.Row).Resize(, 7)
Else
Set rng = Union(rng, .Range("F" & c.Row).Resize(, 7))
End If
End If
Next c
End With
If Not rng Is Nothing Then rng.Select
End Sub
What do I change the line If UCase... etc in red above to, so that it tests for a value in a cell? eg. U25 where I'm currently putting the value.
Thanks,
Dave