Change valve of a formula to value of a cell

[hartless43]

In the following code I found elsewhere and, in the line, "For i = 1 To Int(Rng.Cells.Count * 0.45) I would like to replace the 0.45 with a value of a cell which in my case is Range("L2"). This code will remove a percentage of numbers from a range but here you have to manually write in each percentage.

[

Sub RemovePercentage()
Dim Rng As Range
Dim i As Long, x As Long, y As Long

Set Rng = Range("B2:J10")

On Error GoTo ErrHandler

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For i = 1 To Int(Rng.Cells.Count * 0.45)
retry:
    x = WorksheetFunction.RandBetween(1, Rng.Rows.Count)
    y = WorksheetFunction.RandBetween(1, Rng.Columns.Count)
    If Rng.Cells(x, y) <> "" Then
        Rng.Cells(x, y).ClearContents
    Else
        GoTo retry
    End If
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thats it and sure someone can figure this out for me.


Thanks

Jerry, a senior citizen

 

[Joe4]

Just use:

For i = 1 To Int(Rng.Cells.Count * Range("L2").Value)

 

[hartless43]

Thanks, already have tried that and this line below it " If Rng.Cells(x, y) <> "" Then" debugs. Question is there any way to upload the entire file. It is very small.

 

[Joe4]

It works just fine for me.

Do you have any error values in your cells B2:J10?
Or perhaps any merged or protected cells?

Note that you cannot attach files here, but MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

So what ended up being the issue?

 

[hartless43]

Just use:

For i = 1 To Int(Rng.Cells.Count * Range("L2").Value)

Joe4. Found the problem. Using your suggestion, I changed the L2 value to a decimal Ex: 0.45 Thanks.

 

[Joe4]

Joe4. Found the problem. Using your suggestion, I changed the L2 value to a decimal Ex: 0.45 Thanks.

OK. What was it before? Did you have a text value?

 

[hartless43]

It was just a number with no decimal like 45

 

[Joe4]

It was just a number with no decimal like 45

Ah yes, it needs to either be a decimal or a percent (.45 or 45%)!
(Though you could have them enter a whole number. Then you would just need to divide the number by 100 in your code).
You may want to add some VBA code to your procedure to verify the number is a valid entry at the beginning of it.