Speed up Macro Loop

[jbench18]

Excel Help.Clean.xlsmExcel Help.Clean.xlsmHey everyone,

I'm looking for a better way to increase the speed of the macro code (referenced below). I was just curious if there were any other formulas or tricks I could use. It currently performs a loop bw every cell in a range C2:F32. It takes about 10 seconds on average for every score aka "macro run" to transfer over. It currently isn't that bad with the # of questions I have, but I'm working on a project where I would need to add additional questions, and I'm thinking that the 10 seconds for every questions may become cumbersome. Any help would defiantly be appreciated it!



"Sub MacroLog()

Dim rcell As Range

For Each rcell In Sheets("Scoring").Range("C2:F32")

If rcell.Value = "" Then

Sheets("Scoring (2)").Cells(rcell.Row, rcell.Column).Copy
rcell.PasteSpecial xlFormulas

End If

Next rcell

Sheets("Scoring").Select
Range("C2:F32").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Response Tool").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D1").Select
End Sub"

[stnkynts]

Please Login or Register  to view this content.

[jbench18]

Thanks for the quick response! The macro works perfect, and QUICK for the 1rst action / click. When you attempt to answer subsequent questions the macro gives a user error for the following line:

For Each rcell In Sheets("Scoring").Range("C2:F32").SpecialCells(xlCellTypeBlanks)

[stnkynts]

It is not clear to me what you are trying to do.

[jbench18]

I've added the file I'm working with to better give you an idea of what I'm trying to do.

Cell C5 on "Response Tool" tab is where the person will go and provide a score to a response (list range: 1-5). The macro will copy and paste this score after to another worksheet. The person will continue to answer another question (Cell C3:drop down box) and / or answer the same question for a different supplier (Cell C4: drop down box). This will continue until all the questions are answered for all the suppliers. Let me know if you need me to clarify anything...thanks for the help.

[stnkynts]

I am still having difficulty understanding whats going on but I think that is because you are approaching the problem from a different angle than I would. Assign this macro to the log button and see if it transfers the data to the scoring sheet like you want

Please Login or Register  to view this content.

[jbench18]

That is EXACTLY what I'm working for. I did a quick trial, and it worked perfect. I need to make some cosmetic changes (but I can do those myself). THANK You very much for the help! Much appreciated!