找到一个空单元格，并在一个巨大的文件中的另一列中写东西 [英] Find an empty cell and write something in another column in a huge file

问题描述

我有巨大的文件（超过500,000行），我需要找到M列是否等于，如果它等于，我需要写下N列中的东西

我使用=，因为 IsEmpty（）在这些文件中不起作用确定为什么）。

这是我目前的代码：（我删除了什么是不必要的）

  Sub sbVBA_COMMENTS_ExcelSheets（）
 
'CONSTANTS SERVICENTRE FILES（PROD FOUR）
 Const SC_STRLINE As Integer = 4'第一部分始终在线4 
 Const SC_COLNUM As String =B'部件号将始终位于每个EXCEL文件的B列中
 Const SC_COLMKT As String =K'新的营销代码将始终位于K COLUM中，用于每个EXCEL FILES 
 Const SC_COLDCT As String =M'新的折扣代码将始终位于每个EXCEL文件的M列中
 Const SC_COLPRB As String =N'新的注释和问题代码将始终位于M每个EXCEL文件的COLUM 
 
'VARIABLES 
 Dim RowCount As Long 
 Dim ct As Long 
 
'SET VARIABLES 
 RowCount = 0 
 ct = SC_STRLINE 
 
表格（4）。选择
 
'添加评论
带表格（4）
 RowCount = .Cells（.Rows.Count， A）结束（xlUp）.Row 
结束
 
虽然ct <= RowCount 
 
如果表（4）.Range（SC_COLDCT& ct）.Value =然后
 
表格（4）.Range（SC_COLPRB& ct）.Value =EMPTY
 
如果
 
 ct = ct + 1 
 
 Wend 
 
'FILTER BY COMMENTS 
 ActiveWorkbook.Worksheets（4）.AutoFilter.Sort.SortFields.Clear 
 ActiveWorkbook.Worksheets（4）.AutoFilter.Sort.SortFields.Add Key：= _ 
范围（SC_COLPRB&（SC_STRLINE  -  1）），SortOn：= xlSortOnValues，Order：= xlAscending，DataOption：= _ 
 xlSortNormal 
使用ActiveWorkbook.Worksheets（PROD FOUR）。AutoFilter.Sort 
 .Header = xlYes 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
。 SortMethod = xlPinYin 
。应用
结束
 
 End Sub 
  

这是工作...不知何故...但它是超级缓慢，当我运行宏，我的Excel死了几乎70％的时间。

有时，这个宏会导致一个问题，它会在某个原因的地方写下EMPTY ...

Basi cally，我正在寻找一个更快更干净的方式来做同样的事情。

有没有办法在做同样的事情时获得性能？

注意，我不是一个编码器，所以如果你可以以我能理解的方式回答我，那将不胜感激。

解决方案

尝试使用以下代码。而不是处理一个范围，它将范围转换成一个希望更快的数组。

  Sub sbVBA_COMMENTS_ExcelSheets（）
 
'CONSTANTS SERVICENTRE FILES（PROD FOUR）
 Const SC_STRLINE作为整数= 4'第一部分始终在线4 
 Const SC_COLNUM As String =B'部件号将始终位于B列中，用于每个EXCEL文件
 Const SC_COLMKT As String =K 新的营销代码将始终位于K COLUM中，用于每个EXCEL文件
 Const SC_COLDCT As String =M'新的折扣代码将始终位于每个EXCEL文件的M列中
 Const SC_COLPRB As String =N'新注释和问题代码将始终位于每个EXCEL文件的M列中
 
'VARIABLES 
 Dim RowCount As Long 
 Dim varray As Variant 
 Dim i As Long 
 
'SET VARIABLES 
 RowCount = 0 
 ct = SC_STRLINE 
 
'禁用不必要的障碍
 Application.ScreenUpdating = （$）
 
'找到最后一行
行unt = .Cells（.Rows.Count，A）。End（xlUp）.Row 
 
 varray = Range（SC_COLDCT& SC_STRLINE& ：& SC_COLDCT& RowCount）.Value 
 
对于i = UBound（varray，1）到LBound（varray，1）Step -1 
如果varray（i，1）=Then'if nothing 
 .Range（SC_COLPRB& i + 4）.Value =EMPTY
结束如果
下一个
 
 
'FILTER BY COMMENTS 
 .AutoFilter.Sort.SortFields.Clear 
 .AutoFilter.Sort.SortFields.Add Key：= _ 
范围（SC_COLPRB&（SC_STRLINE  -  1）），SortOn：= xlSortOnValues，Order：= xlAscending， DataOption：= _ 
 xlSortNormal 
 
结束
 
使用ActiveWorkbook.Worksheets（PROD FOUR）。AutoFilter.Sort 
 .Header = xlYes 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
。应用
结束
 
 
 End Sub 
  

I have huge files (over 500,000 rows) and I need to find if the "M" column is equal to "", if it is equal to "", than I need to write down something in the "N" column.

I do it with = to "" because IsEmpty() isn't working in those files (not sure why).

This is my current code: (I removed what wasn't necessary)

Sub sbVBA_COMMENTS_ExcelSheets()

'CONSTANTS SERVICENTRE FILES (PROD FOUR)
Const SC_STRLINE As Integer = 4 'FIRST PART IS ALWAYS AT LINE 4
Const SC_COLNUM As String = "B" 'PART NUMBERS WILL ALWAYS BE IN THE B COLUM FOR EVERY EXCEL FILES
Const SC_COLMKT As String = "K" 'NEW MARKETING CODE WILL ALWAYS BE IN THE K COLUM FOR EVERY EXCEL FILES
Const SC_COLDCT As String = "M" 'NEW DISCOUNT CODE WILL ALWAYS BE IN THE M COLUM FOR EVERY EXCEL FILES
Const SC_COLPRB As String = "N" 'NEW COMMENTS AND PROBLEM CODE WILL ALWAYS BE IN THE M COLUM FOR EVERY EXCEL FILES

'VARIABLES
Dim RowCount As Long
Dim ct As Long

'SET VARIABLES
RowCount = 0
ct = SC_STRLINE

Sheets(4).Select

'ADD COMMENTS
With Sheets(4)
    RowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

While ct <= RowCount

    If Sheets(4).Range(SC_COLDCT & ct).Value = "" Then

        Sheets(4).Range(SC_COLPRB & ct).Value = "EMPTY"

    End If

    ct = ct + 1

Wend

'FILTER BY COMMENTS
ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add Key:= _
    Range(SC_COLPRB & (SC_STRLINE - 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("PROD FOUR").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

This is working... somehow... but it's SUPER SLOW and when I run the macro, my Excel dies almost 70% of the time.

Also sometimes, this macro will cause a problem where it will write down EMPTY everywhere for some reason...

Basically, i'm looking for a faster and cleaner way to do the same thing.

Is there a way to gain performance while doing the same thing?

Note that I'm not a coder, so if you could answer me in a way I can understand, that would be appreciated.

解决方案

Try using the below code. Instead of dealing with a range, this converts the range into an array which will hopefully be faster.

Sub sbVBA_COMMENTS_ExcelSheets()

'CONSTANTS SERVICENTRE FILES (PROD FOUR)
Const SC_STRLINE As Integer = 4 'FIRST PART IS ALWAYS AT LINE 4
Const SC_COLNUM As String = "B" 'PART NUMBERS WILL ALWAYS BE IN THE B COLUM FOR EVERY EXCEL FILES
Const SC_COLMKT As String = "K" 'NEW MARKETING CODE WILL ALWAYS BE IN THE K COLUM FOR EVERY EXCEL FILES
Const SC_COLDCT As String = "M" 'NEW DISCOUNT CODE WILL ALWAYS BE IN THE M COLUM FOR EVERY EXCEL FILES
Const SC_COLPRB As String = "N" 'NEW COMMENTS AND PROBLEM CODE WILL ALWAYS BE IN THE M COLUM FOR EVERY EXCEL FILES

'VARIABLES
Dim RowCount As Long
Dim varray As Variant
Dim i As Long

'SET VARIABLES
RowCount = 0
ct = SC_STRLINE

'disable unnecessary hindrances 
Application.ScreenUpdating = False
Application.Calculation = xlManual

With ThisWorkbook.Sheets(4)

'find last row
RowCount = .Cells(.Rows.Count, "A").End(xlUp).Row

varray = Range(SC_COLDCT & SC_STRLINE & ":" & SC_COLDCT & RowCount).Value

For i = UBound(varray, 1) To LBound(varray, 1) Step -1
    If varray(i, 1) = "" Then 'if nothing
       .Range(SC_COLPRB & i + 4).Value = "EMPTY"
    End If
Next


'FILTER BY COMMENTS
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add Key:= _
    Range(SC_COLPRB & (SC_STRLINE - 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

End With

With ActiveWorkbook.Worksheets("PROD FOUR").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


End Sub

这篇关于找到一个空单元格，并在一个巨大的文件中的另一列中写东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！