divide selected cell by 100

[jmhultin]

I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).

These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.

Any help, please?

[royUK]

Use the double click event

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveCell.Value = ActiveCell.Value / 100
End Sub

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

[Leith Ross]

Hello jmhultin,

This macro will allow you to change 1 or more cells. The cells need not be contiguous. Copy this code into a VBA module. You can then assign a key combination to run the macro. Press ALT+F8 to bring up the Macro Dialog to assign the key combination to run the macro.

Sub DivideBy100()

  Dim Cell As Range
  
    For Each Cell In Selection
      Cell = Cell / 100
    Next Cell
    
End Sub

[royUK]

You don't need to loop, select the cells then run this

Option Explicit

Sub x()
Selection.Value = Selection.Value / 100
End Sub

[jmhultin]

I'm sure both solutions worked, but I used Leith's and it worked great. Thanks.

In the future (because I'm not into coding), is there a was to do this by recording a macro?

[DonkeyOte]

Roy, I'm not sure your code would work on multiple cells (and/or non contiguous selections), another approach avoiding Iteration on a cell by cell basis which would also account for mixed data types would be:

Dim rngArea As Range
For Each rngArea In Selection.Areas
    With rngArea
        .Value = Evaluate("IF(ISNUMBER(" & .Address & ")," & .Address & "/100,REPT(" & .Address & ",1))")
    End With
Next rngArea

[royUK]

You can record a macro, but you would need to edit the resulting code to be dynamic. The recorded code would only work exactly as you record it, i.e. not work on different ranges.

Not: the second code that I posted will do exactly what Leith's does, but without the loop and therefore potentially faster.

[royUK]

It worked when I tested it on natches of single cells which is what I thought the OP had. It will fail if the selection contains blocks of cells

[DonkeyOte]

Would it not simply apply the first result to all single cells within the non-contiguous selection though ? (regardless of their own values should they differ from the first)

[jmhultin]

Thanks - appreaciate all your input