Macro to Fill a Range

superior147

New Member
Joined
Nov 9, 2005
Messages
1
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....
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
This should add the percentage value to the column;

Code:
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

Maybe you want this code which sets column G to the appropriate %age of the corresponding cell in column F:
Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,913
Messages
5,575,005
Members
412,634
Latest member
Sumanmathew
Top