Find values greater than 0 in database and replace with 1

[triatomine]

Hi all,

I am trying to find all values in a large database greater than zero and replace them with 1 for a binary analysis. I have tried using find but am having no luck with a range of values. Suggestions?

thanks!

 

[Damon Ostrander]

Hi triatomine,

Here's a macro you can use to convert all non-zero numerical values in whatever range of cells you select to 1s.

Sub NonzeroToOne()
   'Changes all nonzero numerical values in the selected range to 1
   Dim Cell       As Range
   For Each Cell In Selection
      If IsNumeric(Cell) Then
         If Cell.Value <> 0 Then Cell.Value = 1
      End If
   Next Cell
End Sub

To install this macro in your workbook, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane. To run the macro go back to Excel, select the range of cells you want the macro to process, and Alt-TMM.

Keep Excelling.

Damon