让这个vba代码更有效率 [英] Lets make this vba code more efficient

问题描述

我很熟悉编码，并尝试在VBA中进行许多录制和编辑，以便学习一些有用的功能。我也浏览过许多网站来了解VBA的基础知识。我终于完成了我正在开发的这个VBA小程序。你可以看看它，告诉我我可以改变什么来使其更有效率或适应其他电子表格？

首先，我复制并粘贴2从其他工作簿的工作表到这个新的工作簿，并使用这个程序。第一份工作表将提供有关所有产品和新产品的最新信息（无任何意见）。第二份工作表将基本上是我从前一天生成的工作表（其他人全天都提供的所有意见）。所以基本上是一个更新程序。大多数情况下，第二个工作表将上升到列R，但有时其他人将删除列，而使用的最后一个列将是Q。所以如果有人可以帮忙，这将是非常感谢。

我通常开始输入到期日期，所有者和位置到最后3个新的列。然后要确保它们的字体和间距与其他字体相同，我们会将它们更改为相同的字体。之后，我必须通过第二份工作表，并将到期日，所有者和位置的详细信息和注释复制到具有相同序列号的相应单元格（通常在F列）和装配体编号（通常为在E栏）。有很多相同的数字，但是一些序列号是一样的，这就是为什么我首先对序列号做了If语句。在所有的信息被复制之后，一些注释被突出显示各种颜色，所以我必须确保整个行也必须被突出显示。

之后那我把工作表的所有主体都改成一个字体，然后自动调整列和行，使它看起来更整洁。最后，我按照递减顺序订购第一个电子表格，然后将其复制并粘贴到新的电子表格，以便它是一个没有任何宏的常规电子表格。

I不知道如何编写一些代码，所以我刚刚复制并粘贴了我以前尝试过的记录宏。我所做的只是将其范围更改为至少覆盖最后一个条目。

  Dim a As Integer 
 Dim b As Integer 
 Dim s1 As Worksheet 
 Dim s2 As Worksheet 
 
 Set s1 = ThisWorkbook.Worksheets（Sheet1）
 Set s2 = ThisWorkbook.Worksheets Sheet2）
 
a = s1.UsedRange.Rows.Count 
b = s2.UsedRange.Rows.Count 
 
细胞（1,19）=到期日
细胞（1，20）=所有者
细胞（1，21）=位置
 
行（1：1）选择
 With Selection.Font 
 .Name =Arial
 .Size = 8 
结束
 Selection.Font.Bold = True 
带选择
 .Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlCenter 
结束
带Selection.Interior 
 .Pattern = xlNone 
 .TintAndShade = 0 
 .PatternTintAndShade = 0 
结束
 
 ActiveSheet.Range（$ A $ 1：$ U $ 500）。AutoFilter字段：= 3，Criteria1：== WO，_ 
运算符： = xlOr，Criteria2：== WR
行（2：500）。Sel ect 
 Selection.Delete Shift：= xlUp 
 ActiveSheet.AutoFilterMode = False 
 ActiveSheet.Range（$ A $ 1：$ U $ 500）。AutoFilter字段：= 8，Criteria1：= _ 
库存
 Selection.Delete Shift：= xlUp 
 ActiveSheet.AutoFilterMode = False 
 
 Dim i As Integer 
 Dim ii As Integer 
 
i = 2 
 ii = 2 
 
对于i = 2至a 
对于ii = 2至b 
如果s1.Cells（i，6 ）= s2.Cells（ii，6）然后
如果s1.Cells（i，5）= s2.Cells（ii，5）Then 
 s2.Range（s2.Cells（ii，18） ，s2.Cells（ii，21））。复制s1.Range（s1.Cells（i，18），s1.Cells（i，21））
 s1.Range（s1.Cells（i，1） ，s1.Cells（i，17））。Interior.ColorIndex = Cells（i，18）.Interior.ColorIndex 
 End If 
 End If 
 Next ii 
 Next i 
 
 With Selection.Font 
 .Name =Calibri
 .Size = 8 
 End with 
 
 Cells.Select 
 Cells.EntireColumn.AutoFit 
 Cells.EntireRow.AutoFit 
 
范围（A1）。选择
 ActiveWorkbook.Worksheets（Sheet1）Sort.SortFields.Clear 
 ActiveWorkbook.Worksheets（Sheet1）。Sort.SortFields.Add Key：= Range（A1），_ 
 SortOn：= xlSortOnValues，Order：= xlDescending，DataOption：= xlSortNormal 
使用ActiveWorkbook.Worksheets（Sheet1）排序
 .SetRange范围（A2：U500）
 .Header = xlNo 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
。应用
结束
  / pre> 
 
解决方案
始终符合您的范围
 code>单元格（1，19）
  
应该有工作表限定符
 工作表（Sheet1）。单元格（1，19）
  
这将保持正确的引用。
 
 
 
还删除所有。选择减缓：
 
 
 
这是：
 行（1： 1）。选择
 With Selection.Font 
 .Name =Arial
 ... 
  
成为：
  With Worksheet（Sheet1）。Rows（1：1）。Font 
 .Name =Arial
 ... 
  
或者你可以：
  With Worksheet（Sheet1）。Rows（1：1）
 With .Font 
 .Name =Arial
 .Size = 8 
 .Bold = True 
以
结束Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlCenter 
带.Interior 
 .Pattern = xlNone 
 .TintAndShade = 0 
 .PatternTintAndShade = 0 
结束
结束
  
 
I'm new to coding and have tried many recording and editing in VBA to learn somewhat what to do. I've also seached many sites to learn the basics of VBA. I'm finally finished with this VBA mini-program that I'm working on. Could you all take a look at it and tell me what I could change to make it more efficient or adaptable to other spreadsheets?

First of all, I copy and paste 2 worksheets from other workbooks into this new workbook and use this program on it. The first worksheet will have the most current information on all the products and new incoming products(without any comments). The second worksheet will basically be the worksheet I generated from the day before (with all the comments that other people have put onto it throughout the day). So basically it's an updating program. Most of the time, the second worksheet will go up to column R, but sometimes other people will delete a column and the last column used will be Q instead. So if someone could help in this it would be very much appreciated.

I usually start out entering in Due Date, Owner, and Location into the last 3 new columns. Then to make sure that they have the same font and spacing as the others, I change them to the same font accordingly. After that, I have to go through the 2nd worksheet, and copy the Due Date, Owner, and Location details and comments to the 1st worksheet in the corresponding cell that has the same serial number (usually in column F) and assembly number (usually in column E). There are a lot of the same assebly numbers but a few that are the same for serial numbers, that's why I did the If statements for the serial number first. After all the information is copied over, some of the comments are highlighted in various colors, so I have to make sure that the whole row has to be highlighted too.

After that, I change all of the body of the worksheet to a certain font, then autofit both columns and rows to make it look neater. Lastly, I order the 1st spreadsheet in decending order, then copy and paste it to a new spreadsheet so that it would be a regular spreadsheet without any macros on it.

I didn't know how to write some of the codes, so I just copied and pasted the recorded macros I tried before. All I did was change the range of it to cover the last entry at the least.
Dim a As Integer
Dim b As Integer
Dim s1 As Worksheet
Dim s2 As Worksheet

Set s1 = ThisWorkbook.Worksheets("Sheet1")
Set s2 = ThisWorkbook.Worksheets("Sheet2")

a = s1.UsedRange.Rows.Count
b = s2.UsedRange.Rows.Count

Cells(1, 19) = "Due Date"
Cells(1, 20) = "Owner"
Cells(1, 21) = "Location"

Rows("1:1").Select
With Selection.Font
    .Name = "Arial"
    .Size = 8
End With
Selection.Font.Bold = True
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

ActiveSheet.Range("$A$1:$U$500").AutoFilter Field:=3, Criteria1:="=WO", _
    Operator:=xlOr, Criteria2:="=WR"
Rows("2:500").Select
Selection.Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$1:$U$500").AutoFilter Field:=8, Criteria1:= _
    "Inventory"
Selection.Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False

Dim i As Integer
Dim ii As Integer

i = 2
ii = 2

For i = 2 To a
    For ii = 2 To b
        If s1.Cells(i, 6) = s2.Cells(ii, 6) Then
            If s1.Cells(i, 5) = s2.Cells(ii, 5) Then
                s2.Range(s2.Cells(ii, 18), s2.Cells(ii, 21)).Copy s1.Range(s1.Cells(i, 18), s1.Cells(i, 21))
                s1.Range(s1.Cells(i, 1), s1.Cells(i, 17)).Interior.ColorIndex = Cells(i, 18).Interior.ColorIndex
            End If
        End If
    Next ii
Next i

With Selection.Font
    .Name = "Calibri"
    .Size = 8
End With

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

Range("A1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A2:U500")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

解决方案
Always qualify your Ranges
Cells(1, 19)
Should have the worksheet qualifier
Worksheets("Sheet1").Cells(1, 19)
This keeps proper references.

Also remove all .Select it slows it down:

This:
Rows("1:1").Select
    With Selection.Font
        .Name = "Arial"
        ...
Becomes:
With Worksheet("Sheet1").Rows("1:1").Font
    .Name = "Arial"
    ...
or you could:
With Worksheet("Sheet1").Rows("1:1")
    With .Font
        .Name = "Arial"
        .Size = 8
        .Bold = True
    End with
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With


                        
这篇关于让这个vba代码更有效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！