vba Excel宏在特定列中查找数字大于20的单元格,然后将匹配的单元格除以1000
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/21084050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro to find cells with numbers larger than 20 in a specific column and then divide the matching cells by 1000
提问by user3188967
I have a number of log files that I am importing into Excel and I am trying to create a macro that can find numbers greater than 20 in a specific column then divide the cells that match the criteria by 1000 (converting from Kb to Mb). Any help would be greatly appreciated.
我有许多要导入 Excel 的日志文件,我正在尝试创建一个宏,该宏可以在特定列中查找大于 20 的数字,然后将符合条件的单元格除以 1000(从 Kb 转换为 Mb)。任何帮助将不胜感激。
Thanks,
谢谢,
Simon
西蒙
回答by Bernard Saucier
Let's say your Kb values start in row 1 of column A, write this formula in column B :
假设您的 Kb 值从 A 列的第 1 行开始,在 B 列中写下这个公式:
=IF(A1>20, A1/1000, A1)
Basically, what this does, is tell the computer that if A1 is greater than 20, put A1/1000 in this cell, otherwise put A1. Stretching this formula down the column will give you the right formula for each row.
基本上,它的作用是告诉计算机,如果 A1 大于 20,则将 A1/1000 放入该单元格中,否则放入 A1。将此公式向下延伸到列将为您提供每行的正确公式。
回答by Sathish Kothandam
Macro Version .. if u have data in A column
宏版本 .. 如果你在 A 列中有数据
Sub test()
Dim erange As Range
Dim lrow As long
With ActiveSheet
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For Each erange In .Range("A2:A" & lrow)
If erange.Value > 20 Then
erange.Offset(0, 1).Value = erange.Value / 1000
End If
Next erange
End With
End Sub