Problems with data types & Find Method in Excel 1

[djburnheim]

I'm trying to write a procedure that finds a cell in a sheet then gets the value from the cell under it and depending on that value does something...I have basically got it working but am running into problems I think because of the data types...any suggestions would be great.

Dave

***code***

Dim Rota
Dim StartDate As String
Dim Team1 As Range
Dim Team2 As Range
Dim Team3 As Range

Set Team1 = WorkSheets("Weekly Stats&quot.Range("C6, C9, C11&quot
Set Team2 = WorkSheets("Weekly Stats&quot.Range("C7, C8, C12&quot
Set Team3 = WorkSheets("Weekly Stats&quot.Range("C10, C13, C14&quot

StartDate = Format(WorkSheets("Weekly Stats&quot.Range("D4&quot.Value, "dd-MMM&quot
With Worksheets("Rota&quot.Range("a1:f100&quot
Set DCell = .Find(StartDate)
If Not DCell Is Nothing Then
DRow = DCell.Row + 1
DColumn = DCell.Column
Rota = Cells(DRow, DColumn).Value
End If

If Rota = "1" Then
Team1.Select
Team1.Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Team2.Select
Team2.Activate
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With
Team3.Select
Team3.Activate
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End If

 

[RobBroekhuis]

Your code should work - you could make it slightly more compact using:

Set DCell = .Find(StartDate)
If Not DCell Is Nothing Then rota=DCell.offset(1,0)

I did notice that the first time you use "rota", it's not yet been assigned a value, in:
With Worksheets("Rota&quot.Range("a1:f100&quot

What is the problem you're encountering?
Rob

 

[djburnheim]

Thanks for the advice...I now have it working. It looks like it was poorly declared variables...Worksheets("Rota&quot refered to the worksheet by that name, that's why "rota" hadn't been assigned a value. Rather than declaring "rota" I changed it to "shift" and it seems to of fixed the problem. I've also used DCell.offset(1,0) as suggested.

Thanks again
Dave