Any Excel Expert?

jgsi
jgsi Posts: 5,062
edited July 2013 in The cake stop
I want to edit out erroneous power spikes from a data file imported into Excel.
Basically does anyone have a macro to zap eg any number in excess of 1000 to 0 in a column?
Or simple instructions on how to devise this in Excel..
ta in advance

Comments

  • CiB
    CiB Posts: 6,098
    edited July 2013
    Create a macro, press Alt+F11 to open the Macro editor then paste this into the code block.
    Dim R As Integer
For R = 1 To 10000
    Range("A" + CStr(I)).Select
    If Range("A" + CStr(I)).Value = "" Then Exit For
    If CInt(Range("A" + CStr(I))) > 999 Then Range("A" + CStr(I)).Value = "0"
Next

    It's a bit basic but will work. Change the A to whichever column your data is in and the 10000 to a number that's a bit closer to the number of rows of data. Change the 1 in 1 to 10000 to the row number that your data starts in.

    Invoice to follow. :)
  • jgsi
    jgsi Posts: 5,062
    Thank you muchly, sir.. cheque's in the post already :wink:
  • CiB
    CiB Posts: 6,098
    Note the edit. I'm not saying what it was mind. :)

Categories