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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
  • Solved
VBA Userform Date Format
2
Replies
12
Views
305
Jack George
J
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
343
JMWh1t3
J
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
80
Peter_SSs
Peter_SSs
G
  • Question Question
Format Pulled Data from Workbook into a Text Box
Replies
6
Views
132
Fuji
F
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
74
daveyc18
D

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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