User Form or Formula?

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,357
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,546
Office Version
365
Platform
Windows
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
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,357
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

New Member
Joined
Sep 26, 2019
Messages
1
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

Well-known Member
Joined
Aug 9, 2009
Messages
1,357
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
You are welcome
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,546
Office Version
365
Platform
Windows
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
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,357
Why a private subroutine? I tried your code but nothing happens when I enter an amount in F4.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,546
Office Version
365
Platform
Windows
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?
 

Forum statistics

Threads
1,084,776
Messages
5,379,812
Members
401,629
Latest member
LEMANOIS

Some videos you may like

This Week's Hot Topics

Top