Looking for help with speeding up or rewrite on vba code.

Looking for help on this to see if i can find a faster way.
What it does is takes the number that is entered in the "stock in" sheet and adds it to the corresponding cell in the "Master Stock Sheet Hidden" then moves on to the next cell until it gets to the end of the 3000 cells.
But it takes for ever to enter the stock.

What i would like it to do is if the "stock in" sheet cell contains stock input, then match the stock codes between both sheets and then add the stock to the "Master Stock Sheet Hidden" in the corresponding cell.

Bellow is what i am currently using any help advice would be great.


Sub StockIn()
'
' StockIn Macro
'
With Application
.Application.ScreenUpdating = False
.Calculation = xlManual
.EnableEvents = False
End With
Dim a As Long
Dim b As Long
Dim answer As Long
Dim myrow As String
Dim r As Integer
For r = 6 To 3000
myrow = "G" & r
a = Sheets("Stock In").Range(myrow)
b = Sheets("Master Stock Sheet Hidden").Range(myrow)
answer = a + b
Sheets("Master Stock Sheet Hidden").Range(myrow) = answer
Sheets("Stock In").Range(myrow).ClearContents
Next r
ActiveWorkbook.Save
MsgBox "Stock Added"
With Application
.Application.ScreenUpdating = True
.Calculation = xlAutomatic
.EnableEvents = False
End With
'
End Sub

Hi,

I think it's slow because you do so many things in the loop. Is this faster? (I also set .EnableEvent = True at the end, I think that's what you wanted. If no, feel free to change it back.)

Please Login or Register  to view this content.

hi tried it but not sure where its pulling things from and putting them to i have included an example of my sheet thanks for your support.

Unfortunately I can't test your file properly, because I have an earlier excel version (Excel 2003).

Have you tried my macro? Just replace your code with my code.

Also, make sure you make a copy of the original file before you try it.

You're exactly right. So the correct code should be this.

Please Login or Register  to view this content.

yes notice that after it started writing across the row but now sorted thanks for all your help.

seconds instead of minutes lol

Anything looping that many times is going to be slow. See if this is any faster:

Please Login or Register  to view this content.

BTW, in future it's probably easier if you post workbooks where the sheets you'd like people to look at aren't hidden

Great replies, thank you for showing more solutions guys.

Wow didnt think it could get quicker thanks for the replies.


What do you have to change to subtract the stock as i cant see the plus calculation.
Cheers
Chris

In my code you have to change this line:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

Thanks to everyone that help me in solving this problem got the action down to under 5 sec's for imputing 3000 lines not to shabby.

Cheers

Chris