2

I have below Excel VBA code in many places. It need to optimize to improve running speed. I do appreciate your help.

Someone within a loop:

For x = A_OFFSET_MARKETVALUE To A_OFFSET_CURRENT
    Cells(nLevel1Position, iColumn).Select
    Selection.Copy
    Cells(nLevel1Position, iColumn + x).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next x

For example: the above code copy Cells(11,13).Formula "=SUM(M12:M13)" to Cells(11,14).Formula, After copy and xlPasteFormulas Cells(11,14).Formula becomes "=SUM(N12:N13) does anybody know why? But the result is expected. So if I use below code to optimized it

Cells(nLevel1Position, iColumn + x).Formula = Cells(nLevel1Position, iColumn).Formula

The Cells(11,14).Formula always is "=SUM(M12:M13)". I do not know why? How should I optimized it and also get Cells(11,14).Formula value "=SUM(N12:N13)"

Someone is not in a loop:

Cells(nLevel1Position, iColumn).Select
Selection.Copy
Cells(nLevel1Position, iColumn + A_OFFSET_MARKETVALUE).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

The whole code is below for reference. There are many places need to be optimized. Unfortunately, I am very new to VBA,and my boss required it to be done in a very short time. So anybody can help me will be greate appreciated. Many many thanks.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Deep in Development
  • 497
  • 2
  • 8
  • 24
  • 1
    `Cells(nLevel1Position, iColumn + x).Formula = Cells(nLevel1Position, iColumn).Formula` – Scott Craner Jul 24 '19 at 20:05
  • 2
    Do not use `.Select` or `.Activate` See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Jul 24 '19 at 20:06
  • I had tried Cells(nLevel1Position, iColumn + x).Formula = Cells(nLevel1Position, iColumn).Formula but the result calculate value is not correct. – Deep in Development Jul 24 '19 at 20:09
  • If you show more of your code we can help more. At least show how you set the variables – Scott Craner Jul 24 '19 at 20:12
  • Hi Scott, Please see updated post with more code. many thanks! – Deep in Development Jul 24 '19 at 20:23
  • Hi Scott, the original code copy Cells(11,13).Formula "=SUM(M12:M13)" to Cells(11,14).Formula, Cells(11,14).Formula becomes to "=SUM(N12:N13), Do you know Why? – Deep in Development Jul 24 '19 at 21:40
  • @DeepinDevelopment That is because Excel adjusts formulas automatically (by default). You can stop that by using `$` like `=SUM($M12:$M13)` to fix the column or `=SUM($M$12:$M$13)` to fix column and row. – Pᴇʜ Jul 25 '19 at 13:36
  • I refer to you the **most active** [tag:VBA]-related question on S.O. : "[How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/8112776)" – ashleedawg Dec 12 '21 at 09:10

0 Answers0