User Form or Formula?

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
I have a list of 200 names in column A and a corresponding list of rates in column B. What I need to do is be able to enter one rate and have that rate be entered next to each of the 200 names. So if I enter $1.00, $1.00 appears next to each person's name.

Would I use a user form or formula in order to accomplish this?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi,

Not sure to fully understand your problem ...

However, should you select your range B1:B200

You can type 1 ... and then use Ctrl Enter to fill in the whole range

Hope this will help
 
Upvote 0
What I need to do is be able to enter one rate ...
If that means enter one rate in column B beside any one of the names then try this Worksheet_Change event macro. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myRange
  
  Set myRange = Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
  If Not Intersect(Target, myRange) Is Nothing Then
    Application.EnableEvents = False
    myRange.Value = Intersect(Target, myRange).Cells(1).Value
    Application.EnableEvents = True
  End If
End Sub
 
Upvote 0
I was trying to avoid putting =$F$4 into all cells in column B with names but it seems that would be the easiest option.
 
Upvote 0
I guess my only comment in response to your question is will you ever need to change the pricing for anyone in your column A? If so, then maybe absolute might not be the best solution. Would the pricing ever need to change?
 
Upvote 0
It would change but my boss does not want to get too fancy with it. I mentioned that scenario manager would be easier but he did not want that.
 
Upvote 0
I was trying to avoid putting =$F$4 into all cells in column B with names ...
You didn't mention before that it was F4 where you want to enter the rate. ;)

Try this instead of my previous code. If your names don't start in row 2, alter the red 2 in the code below.
I have assumed that the names in column A are not the result of formulas.
After entering the code as described previously, enter a rate in F4 and it should appear in column B beside all the names in column A.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F4")) Is Nothing Then
    Application.EnableEvents = False
    Intersect(Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).EntireRow, Columns("B")).Value = Range("F4").Value
    Application.EnableEvents = True
  End If
End Sub
 
Upvote 0
Why a private subroutine? I tried your code but nothing happens when I enter an amount in F4.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top