Macro to Fill a Range

[superior147]

This is my first post so forgive me if I omit anything I should have included.

I am trying to write a simple macro that, on the click of a button, will use an input box to record a percentage value and then will apply it to a range of cells.

I have achieved this much, when using a fixed range of cellls (as follows)...

Sub Set_Target_Rate()

target = InputBox("Set target rate (%):", "Target Rate")
Range("G7:G60000").Select
Selection.FormulaR1C1 = target / 100

End Sub

But what I want to be able to do is to say, for example, fill column G with the percentage value only as far as there is data in column F. I have been trying to use a count of the values in column F and use this as my end reference for my G range (if that makes sense?), but it's not working.

Any advice would be gratefully appreciated, many thanks....

 

[Fat Cat]

This should add the percentage value to the column;

Sub Set_Target_Rate() 

Dim firstRow As Long
Dim lastRow As Long

firstRow = 7   'change to your first row number
lastRow = Range("F65536").End(xlUp).Row

target = InputBox("Set target rate (%):", "Target Rate")
Range("G" & firstRow & ":G" & lastRow).Value = target / 100
End Sub

 

[al_b_cnu]

Hi,

Maybe you want this code which sets column G to the appropriate %age of the corresponding cell in column F:

Sub ApplyPercent()
Dim bError As Boolean
Dim R As Range
Dim vPercent As Variant, vCur As Variant
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Do
    bError = False
    vPercent = Application.InputBox(prompt:="Set target rate (%):", _
                                    Title:="Target Rate")
    If vPercent = False Then Exit Sub
    If Val(vPercent) <= 0 Or Val(vPercent) >= 100 Then
        MsgBox "Please set a %age >0 and < 100"
        bError = True
    End If
Loop Until bError = False
For Each R In Range("G1:G" & Cells(Rows.Count, "F").End(xlUp).Row)
    vCur = R.Offset(0, -1).Text
    If IsNumeric(vCur) Then
        R.FormulaR1C1 = "=R[0]C[-1]*" & vPercent & "/ 100"
    End If
Next R
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Alternatively, you could just place the appropriate formula in cell G1 and double click the cell in the bottom right hand corner which will copy the formula down the column

PS - Welcome to the board!