Excel Macros - need help 1

[luke99]

hi, first time user of this forum, so thanks in advance to any comments to my question:

i want to create a macro that will increase values by 10% of each cell in a user selected range. and if the increasement is below 10 then increase the cell value by 10 units.

this is it. thanks.

Luke

 

[smozgur]

This may help. Runs for selection and increasing value variable is incVal.

Sub IncreaseMacro()
Dim myrng As Range
Dim mycell
Dim incVal As Double
incVal = 0.1
Set myrng = Selection
For Each mycell In myrng.Cells
mycell.Value = IIf(mycell.Value * incVal < 10, mycell.Value + 10, mycell.Value * (1 + incVal))
Next mycell
End Sub

Regards

 

[luke99]

thanks that has given me an idea how to go round it. i have wrote my code see below. and it does exactly what i wanted. thanks. Luke99

code:
Sub IncreaseByPercentage()
'
' Macro recorded 21/12/2002 by Luke99
'

Dim UserRange As Range
Dim Percentage As Integer
Dim IncreaseVal As Integer
'declare variables

Percentage = InputBox(&quot;Enter the percentage&quot;, &quot;Increase values by %&quot;, vbOK)
'ask user for percentage increase

Set UserRange = Selection
'set range of cells selected by user

For Each Cell In UserRange.Cells
IncreaseVal = Cell.Value / 100 * Percentage
'calculate increase value for cell value

If IncreaseVal > 10 Then
Cell.Value = Cell.Value + IncreaseVal
' if the increase value is higher then 10, then add to the cell value
Else
Cell.Value = Cell.Value + 10
'if the increase value is below or equall 10, then add the minimun increase value (10)
End If

Next Cell
'repeat the above action for each cell in user selection

End Sub

 

[smozgur]

Perfect! Oz

http://www.TheOfficeExperts.com