what's wrong with this?

[chammy88]

I have done the following:

Sub Group()
Sheets("calcs").Select
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Dim LR As Long, i As Long
With Sheets("AmplaData")
LR = .Range("AO" & Rows.Count).End(xlUp).row
For i = 2 To LR
With .Range("AO" & i)
If .Value = 1 Then .Offset(, -24).Resize(, 1).Copy Destination:=Sheets("calcs").Range("F" & Rows.Count).End(xlUp).Offset(1)
If .Value = 1 Then .Offset(, -22).Resize(, 1).Copy Destination:=Sheets("calcs").Range("G" & Rows.Count).End(xlUp).Offset(1)
If .Value = 1 Then .Offset(, -17).Resize(, 1).Copy Destination:=Sheets("calcs").Range("H" & Rows.Count).End(xlUp).Offset(1)
If .Value = 1 Then .Offset(, -14).Resize(, 1).Copy Destination:=Sheets("calcs").Range("I" & Rows.Count).End(xlUp).Offset(1)
If .Value = 1 Then .Offset(, -13).Resize(, 1).Copy Destination:=Sheets("calcs").Range("J" & Rows.Count).End(xlUp).Offset(1)
If .Value = 1 Then .Offset(, -12).Resize(, 3).Copy Destination:=Sheets("calcs").Range("K" & Rows.Count).End(xlUp).Offset(1)
End With
Next i
End With
Worksheets("Calcs").Range("F:F").NumberFormat = "dd/mm/yy hh:mm:ss"
Worksheets("Calcs").Range("G:G").NumberFormat = "dd/mm/yy hh:mm:ss"
If Worksheets("Calcs").Range("J:J").String = "Pilbara Iron.Brockman 2.Plant" Then Worksheets("Calcs").Range("J:J").String = "Pilbara Iron.Brockman 2.Primary Crushing"

The code works until it gets to the If statement in bold where I get a runtime error '438' : Object doesn't support this property or method.

Does anyone have any ideas/suggestions?


Thanks in advance

 

[shg]

There is no such property as String -- change it to Value. But you can't compare an array of values to a single string.

What are you trying to do?

 

[chammy88]

yeah, I realised that I had it as .value first and it didn't work so I tried .string but forgot to change it back before copying it into the thread.

I just want to say if anything in column J says "Pilbara Iron.Brockman 2.Plant" then chance it to "Pilbara Iron.Brockman 2. Primary Crushing"

Thanks

 

[chammy88]

*change

 

[shg]

Maybe

    Worksheets("Calcs").Range("J:J").Replace "Pilbara Iron.Brockman 2.Plant", _
                                             "Pilbara Iron.Brockman 2.Primary Crushing"

 

[chammy88]

You are a legend.

Thanks for that

 

[shg]

You're welcome, glad it worked for you.