PDA

View Full Version : For Each Loop Not Working



nirvehex
09-03-2014, 11:27 AM
Hi,

I'm trying to use this code:



Sub CleanExport()
For Each r In Range("Y2:Y" & Cells(Row.Count, "Y").End(xlUp).Row)
If r.Offset(0, -1).Value = "TOT" Then
r.Value = r.Value * 0.00495113169

End If
Next r
End Sub


to do the following:

I want the code to look at each numerical value in column Y and overwrite a new value if its corresponding row in column X reads "TOT". The new value will be what's currently in column Y * .00495113169.

My code keeps erroring out. Any ideas?

Thanks!

mancubus
09-03-2014, 12:10 PM
it should be Rows.Count and not Row.Count



Sub CleanExport()
For Each r In Range("Y2:Y" & Cells(Rows.Count, "Y").End(xlUp).Row)
If UCase(r.Offset(0, -1).Value) = "TOT" Then
r.Value = r.Value * 0.00495113169
End If
Next r
End Sub

nirvehex
09-03-2014, 12:49 PM
Worked like a charm! Thanks!

mancubus
09-03-2014, 12:55 PM
you're welcome.

thanks for the feedback. please mark the thread as solved from Thread Tools dropdown.

nirvehex
09-03-2014, 01:34 PM
Hey before I mark this complete...Is there a way to speed this code up so it doesn't go line by line? It takes 3+ minutes on a 1500 line excel sheet.

Any ideas?

Thanks.

mancubus
09-03-2014, 02:18 PM
i am nor sure 3-5 minutes is a long time. :)

perhaps...


Sub CleanExport()

Dim rng As Range

With Worksheets("Sheet1") 'change worksheet name to suit
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=24, Criteria1:="=TOT"
With .AutoFilter.Range
If .Rows.Count > 1 Then
For Each rng In .Columns(25).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
rng.Value = rng.Value * 0.00495113169
Next
End If
End With
.AutoFilterMode = False
End With

End Sub