Solved: Adjusting the last digit of a number with macro

[Anomandaris]

Hi again guys,

Seems like I keep coming up with weird requests, the latest one involves coming up with a macro that can change a number based on its last digit.
Lets say the number is 3266, so the macro should take the last digit '6' and divide it by 8 which gives us 0.75, this is then added to the previous three digits '326' to get '326.75'.

I need another macro that will divide the numbers in a column by 100.

I've attached a file with just one sheet(Sheet1)

You can see a few products with their respective prices. What I want to do is using Data filter- to select a product ....say 'Wheat' then running a macro so that only the Wheat prices change in that column, while the other product prices remain the same, then I would select another product using 'filter' and run a different macro, so product prices would be changed in the same column using different macros.
I hope I didnt make that sound confusing.
The new 'macro adjusted' prices should appear in the same column F, replacing the old value.


Thanks for reading, and hope this can be done...I couldnt think of any code to select the last digit of a number (and oh by last digit I dont mean a decimal place . In 5006.00 the last digit is 6).

[Bob Phillips]

1)

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "F" '<=== change to suit
Dim cell As Range
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For Each cell In .Cells(1, TEST_COLUMN).Resize(LastRow).SpecialCells(xlCellTypeVisible).Cells

If IsNumeric(cell.Value) Then

cell.Value = cell.Value \ 10 + (cell.Value Mod 10) / 8
End If
Next cell
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

2)

Enter 100 in a sdpare cell and copy it

Select the cells to be divided
Goto Edit>PasteSpecial... and click Divide
OK to fionish

Clear the cell with 100 in.

[Anomandaris]

Fantastic stuff! Works perfect

All hail XLD, Lord of VBAX!

thanx man, I owe you a few lunches now