User Form or Formula?

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,342
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,236
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,342
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,342
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,236
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,342
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,236
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,081,983
Messages
5,362,551
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top