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....
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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,526
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,504
Messages
5,832,106
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top