Error Message on a code

[carlleese24]

Hi

I have a code here that basically searches a value and puts a figure in the next few cells

For Each Name In Worksheets("rota").Range(.Cells(1, Sheets("Swipe_Data").Range("b16").Value), .Cells(1000, Sheets("Swipe_Data").Range("b16").Value)).SpecialCells(xlCellTypeConstants, 1)
With ActiveCell.Offset(0, 1)
Set c = .Find(Name.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Name.Offset(0, Sheets("Swipe_Data").Range("a16")).Value = c.Offset(0, 4).Value
End If
End With
Next Name

The error message points to this bit of the code .cells and says
compile error:
invalid or unqualified reference


Any help on this would appreciated

 

[Smitty]

Try taking the dot qualifier off (.Cells-->Cells). You don't need it as you're already in an explicit reference to the sheet. You'd need it if you were in a With statement. Unless you're trying to also refer to the Swipe_Data sheet.

HTH,

 

[carlleese24]

Hi Smitty

I removed the 2 dots on the cells now it looks like this

For Each Name In Worksheets("rota").Range(Cells(1, Sheets("Swipe_Data").Range("b16").Value), Cells(1000, Sheets("Swipe_Data").Range("b16").Value)).SpecialCells(xlCellTypeConstants, 1)
With ActiveCell.Offset(0, 1)
Set c = .Find(Name.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Name.Offset(0, Sheets("Swipe_Data").Range("a16")).Value = c.Offset(0, 4).Value
End If
End With
Next Name

Now it gives me this error
run time error 1004

application defined or object defined error


it highlights this code in yellow

For Each Name In Worksheets("rota").Range(Cells(1, Sheets("Swipe_Data").Range("b16").Value), Cells(1000, Sheets("Swipe_Data").Range("b16").Value)).SpecialCells(xlCellTypeConstants, 1)

 

[Smitty]

OK,

I didn't see the rest of the code line the first time around.

So you're trying to work on Row 1 in the rota sheet to Row 1000, with the value in the Swipe_Data sheet as the column reference?

 

[westconn1]

one of the values in the cells is empty or incorrect, check b16 in swipe data
also maybe you need to qualify the cells with the sheet reference

 

[carlleese24]

Hi Smitty


Yes that is correct

I have just solved it I think

For Each Name In Worksheets("rota").Range(Sheets("rota").Cells(1, Sheets("Swipe_Data").Range("b16").Value), Sheets("rota").Cells(1000, Sheets("Swipe_Data").Range("b16").Value)).SpecialCells(xlCellTypeConstants, 1)
With ActiveCell.Offset(0, 1)
Set c = .Find(Name.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Name.Offset(0, Sheets("Swipe_Data").Range("a16")).Value = c.Offset(0, 4).Value
End If
End With
Next Name

there is no errors now and it seems to be working fine

 

[Smitty]

Glad you got it sorted out.

That's one of those cases where it's often hard for someone to try to test a blank solution, but a lot of times when you work at it a bit you get there.