User Form or Formula?

[Justinian]

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?

 

[James006]

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

 

[Peter_SSs]

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

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

 

[Justinian]

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.

 

[ramirezja7]

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?

 

[Justinian]

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.

 

[James006]

You are welcome

 

[Peter_SSs]

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.

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

 

[Justinian]

Why a private subroutine? I tried your code but nothing happens when I enter an amount in F4.

 

[Peter_SSs]

I tried your code but nothing happens when I enter an amount in F4.

Did you install it in the correct place ? Refer to steps 1-4 of post 3.
Do you have macros enabled?