Loop: copy/paste problem when updating a lagre Database (EXCEL 2010)

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
524
Office Version
  1. 365
Platform
  1. Windows
Hi
My Excel Database goes like this (simplified):

Column A: Project number
Column B: Calculated Project Cost
Range ("Formula")
Number of rows: pretty much (+20 000)

Now I want to recalculate the values Project Cost (Col B) for some Projects (e.g. All Project except "BASIC") by first using formula - then paste as value (Whole column B afterwards)
"BASIC" Project Cost should remain as original

By using the macro below I sort of manage to do the trick, but it takes all the time in the world!
So I hope there is a much more smoother way to do this
Could anyone please help me on this one?


Sub LoopTest()
Sheets("Database").Select

Dim Project
Project= "BASIC"
Range("Formula").Copy

Range("A1").Select
For MY_ROWS = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Not (IsError(Range("b" & MY_ROWS).Value)) Then
If Range("a" & MY_ROWS).Value <> Project Then
Range("b" & MY_ROWS).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End If
Next MY_ROWS
End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
524
Office Version
  1. 365
Platform
  1. Windows
Maybe its easier to set a filter: all project except "Basic" and then paste the formula?
But I don't know how to set this kind of filter either ....cant get there by recording
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top