Excel macro to find cells with numbers larger than 20 in a specific column and then divide the matching cells by 1000

Question

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.

Thanks,

Simon

Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. — Siddharth Rout, Jan 13 '14 at 04:52
+1 @SiddharthRout. Wish that option was still a valid (private) close button click! — brettdj, Jan 13 '14 at 08:56
@brettdj: See my comment [HERE](http://stackoverflow.com/questions/21085858/removing-text-around-a-number-using-vba) :( — Siddharth Rout, Jan 13 '14 at 09:00

score 1 · Accepted Answer · answered Jan 13 '14 at 04:54

1

Let's say your Kb values start in row 1 of column A, write this formula in column 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.

answered Jan 13 '14 at 04:54

Bernard Saucier

2,240
1
19
28

Thanks Bernard, that was exactly what I needed to do. Much simpler than a Macro! – user3188967 Jan 13 '14 at 04:58

Sathish Kothandam · Answer 2 · 2014-01-13T05:13:16.893

1

Macro Version .. if u have data in A column

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

edited Jan 13 '14 at 05:13

answered Jan 13 '14 at 04:59

Sathish Kothandam

1,530
3
16
34

`Dim lrow As Integer` I would recommend changing `Integer` to `Long` – Siddharth Rout Jan 13 '14 at 05:01
+ 1 for an alternative excel solution :) – Siddharth Rout Jan 13 '14 at 05:02
Eagle eye . Updated - Sid :) – Sathish Kothandam Jan 13 '14 at 05:03
There is one other thing as well but then that will be too much :D... would rather give you the [LINK](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) I am referring to the `DOT` before `Rows.Count` – Siddharth Rout Jan 13 '14 at 05:06
Just written above code in notepad :D -Again Updated ..thanks Sid – Sathish Kothandam Jan 13 '14 at 05:14

Excel macro to find cells with numbers larger than 20 in a specific column and then divide the matching cells by 1000

2 Answers2