selecting a range of cells

I have a problem in selecting several ranges of cells. What i wish to do is to look at some data and identify where a certain cell occurs. In this case it contains the text "Calshot". I then want to select this cell and the 8 cells above it. The macro searches for all of these ranges ( down one column). Once selected i wish to copy and paste to another sheet. However, I can't seem to get the macro to select all the ranges of cells.

So far I have this:

Sub copydata()

Do
If ActiveCell = ("Calshot") Then
Range("Calshot").Offset(-8, 0).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If


ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))

End Sub

which doesn't work. The problem lies with the 'Range' line of code. If i remove this the macro runs and selects every cell containing the word "Calshot". I just can't select a range of cells based upon this one cell which appears at irregular points down the column.

So far I'm just experimenting so I'm filling the cells with colour to check it's working. copying and pasting is not a problem.

Any help would be much appreciated.

Luke

Hello Luke,

You are right about the range. You are trying to use the string value "Calshot" as an address. This will only work if you have a named range = "Calshot". Here is what to change...

Is:
Range("Calshot").Offset(-8, 0).Select


Change To:
ActiveCell.Offset(-8, 0).Select

Hope this clarifies things a bit,
Leith Ross

Originally Posted by Leith Ross

Hello Luke,

You are right about the range. You are trying to use the string value "Calshot" as an address. This will only work if you have a named range = "Calshot". Here is what to change...

Is:
Range("Calshot").Offset(-8, 0).Select


Change To:
ActiveCell.Offset(-8, 0).Select

Hope this clarifies things a bit,
Leith Ross

I tried this and the code now only selects the cell 8 rows above the cell containing the text "Calshot". how can i select a range 8 rows high based upon a single cell that i have told the code to select?

Hello Luke,

To select 8 rows up from a cell selected by code, you must have the Range address for the single cell or multiple cells in a variable somewhere, like input from an InputBox, TextBox, ComboBox, or another worksheet dedicated to this Range.

For this example, let's say the user types the cell address into TextBox1.

Dim Rng As Range

TextBox1.Text = "A10"
Rng = ActiveSheet.Range(TextBox1.Text)

Rng.Offset(-8, 0).Select

This will select 8 rows above the cell address typed in by the user.

Hope this answers your question,
Leith Ross