For any distinct value, ask an input through an inputbox

Paul82

New Member
Joined
Feb 25, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Hi,

i hope someone can help me with this matter

I try to explain it very simple

My excel sheet has different values in column A, but there could be also duplicates

1582623748888.png


I want now that for every value in Column 1, vba asks me to put a value into an input box and pasts the answer in the corresponding filed(s) in column 2.

For the excel above i want that vba ask me 3 times to input a value (A, B and C) and then copies the value in the column 2

for example, It asks me one time to input a value for "A" and copy the input from inputbox in cells B2, B3 and B4

thanks in advance
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I give up
Paul. I have run Yongle's code on my version of your data. It only asks for A once, then B once, then fills all the rows with the 2 answers. I can't see how this is wrong for what you want?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Paul82

New Member
Joined
Feb 25, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Hi Paul. Sorry I forgot to say. To use my solution you will need to have the VBScripting reference turned on. In the VB editor, select Tools, References, and scroll down to find 'Microsoft Scripting Runtime'. Check the box by it and try again. But if your organisation has restrictions beyond that, then I can't help I don't think.

now i could activate that option

works exactly as i need

thank you very much
 

Paul82

New Member
Joined
Feb 25, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
I assume that all cells in column B are empty before procedure runs

Another option (which is less elegant than solution posted by @jmacleary)
VBA Code:
Sub GetInput()
    Dim cel As Range, cel2 As Range, lastCel As Range, v As Variant
    Application.ScreenUpdating = False
    Set lastCel = Range("A" & Rows.Count).End(xlUp)
    For Each cel In Range("A2", lastCel)
        If cel.Offset(, 1) = "" Then
            v = InputBox("Enter value for " & cel.Value, "User Input")
            For Each cel2 In Range(cel, lastCel)
                If cel2 = cel Then cel2.Offset(, 1) = v
            Next cel2
        End If
    Next cel
End Sub
works now as it should, thank you very much
 

Paul82

New Member
Joined
Feb 25, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Paul. I have run Yongle's code on my version of your data. It only asks for A once, then B once, then fills all the rows with the 2 answers. I can't see how this is wrong for what you want?
yes both running now
thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,988
Messages
5,599,217
Members
414,297
Latest member
dalkarl

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