tpoynton
01-16-2006, 01:51 PM
Greetings,
I read johnske's article on how to improve the efficiency of VBA code, but am struggling with how to make this quickerhttp://vbaexpress.com/forum/images/smilies/banghead.gif.
For Each cell In dataRange
If cell.Value Like "*#*" Then
Cells(iRow, iColCount).Value = WorksheetFunction.PercentRank(dataRange, cell.Value) * 100
Else: Cells(iRow, iColCount).Value = ""
End If
iRow = iRow + 1
Next cell
I discovered that there is a variable of type "worksheetfunction" and tried using that, but it requires the variables to have data when setting it; in this case, i dont know what cell.value is until it is encountered in the loop. There may not be a way to improve the efficiency, but it was worth asking! I noticed that if the datarange contains more than a couple thousand rows, this takes a VERY LONG TIME to complete, and excel appears frozen.
I use similar looping procedures in other places, and the code works relatively quickly - the only thing making this procedure different is the use of the worksheetfunction inside of it.
any thoughts are appreciated!
I read johnske's article on how to improve the efficiency of VBA code, but am struggling with how to make this quickerhttp://vbaexpress.com/forum/images/smilies/banghead.gif.
For Each cell In dataRange
If cell.Value Like "*#*" Then
Cells(iRow, iColCount).Value = WorksheetFunction.PercentRank(dataRange, cell.Value) * 100
Else: Cells(iRow, iColCount).Value = ""
End If
iRow = iRow + 1
Next cell
I discovered that there is a variable of type "worksheetfunction" and tried using that, but it requires the variables to have data when setting it; in this case, i dont know what cell.value is until it is encountered in the loop. There may not be a way to improve the efficiency, but it was worth asking! I noticed that if the datarange contains more than a couple thousand rows, this takes a VERY LONG TIME to complete, and excel appears frozen.
I use similar looping procedures in other places, and the code works relatively quickly - the only thing making this procedure different is the use of the worksheetfunction inside of it.
any thoughts are appreciated!