VBA Upon User Name Input Add Value To A1:A20, Next Value A21:A40 etc.

L

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
256
So what I need is an input box where if the user enters username1 then fill range A1:A1000 with a ID thats stored 20 times and then next ID.

See finished sample with only 5 instead of 20.

Thanks,

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]IF INPUT "User1" THEN[/TD]
[TD="class: xl63, width: 64"]IF INPUT "User2" THEN[/TD]
[TD="class: xl63, width: 64"]IF INPUT "User3" THEN[/TD]
[TD="class: xl63, width: 64"]IF INPUT "User4" THEN[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl63, width: 64"]User1's ID's[/TD]
[TD="class: xl63, width: 64"]User2's ID's[/TD]
[TD="class: xl63, width: 64"]User3 ID's[/TD]
[TD="class: xl63, width: 64"]User4 ID's[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]11[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]11[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]11[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]8[/TD]
[TD="class: xl64"]12[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]11[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9[/TD]
[TD="class: xl64"]14[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]11[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]11[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]13[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]8[/TD]
[TD="class: xl64"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]8[/TD]
[TD="class: xl64"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]8[/TD]
[TD="class: xl64"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]8[/TD]
[TD="class: xl64"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]8[/TD]
[TD="class: xl64"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9[/TD]
[TD="class: xl64"]14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9[/TD]
[TD="class: xl64"]14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9[/TD]
[TD="class: xl64"]14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9[/TD]
[TD="class: xl64"]14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9[/TD]
[TD="class: xl64"]14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]13[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]13[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]13[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]13[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]13[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
I've got this so far, see comments on what I need after but can't figure out!

Code: 
Sub User1()

Dim I As Variant, WS As Worksheet, Rng As Range, Clr As Range
Dim L As String, R As String, Ka As String, Ki As String
Dim X As Integer, Y As Integer, Z As Integer, XX As Integer


Set WS = ThisWorkbook.Sheets(2)
Set Rng = WS.Range("A1")
Set Clr = WS.Range("A1:A" & WS.Cells(Rows.Count, 1).End(xlUp).Row)


Clr.ClearContents


L = "1" ' need to loop 3 4 and 5 after.
R = "2" ' need to loop 6 after.
Ka = "7" ' need to loop 8 9 10 and 13 after.
Ki = "11" ' need to loop 12 and 14 after.


I = InputBox("Please enter your user name")


    Select Case I
        Case "Lewis"
            For X = 1 To 20
                Cells(X, 1).Value = L
            Next X
        Case "Rachel"
            For Y = 1 To 20
                Cells(Y, 1).Value = R
            Next Y
        Case "Karina"
            For Z = 1 To 20
                Cells(Z, 1).Value = Ka
            Next Z
        Case "Kim"
            For XX = 1 To 20
                Cells(XX, 1).Value = Ki
            Next XX
    End Select


End Sub
 
Last edited:
Upvote 0
How about this
Code: 
Sub AddId()
'Lewzerrr

    Dim Usr As String
    Dim arr As Variant
    Dim Rw As Long
    
    Usr = InputBox("Please enter your user name")
    Rw = 1
    Columns(1).ClearContents
    
    Select Case Usr
        Case "Lewis"
            For Each arr In Array(1, 3, 4, 5)
                Range("A" & Rw).Resize(20).Value = arr
                Rw = Rw + 20
            Next arr
    End Select
            
            

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
VBA Userform Date Format
Replies
7
Views
184
gilly01625
G
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
335
JMWh1t3
J
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
58
Peter_SSs
Peter_SSs
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
E
  • Question Question
If statement
Replies
4
Views
120
xlhelp15
xlhelp15

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

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