USING RANDOM FUNCTION Values

Hi All I am creating a table with the following headings

Month
Prod Code
Item

I am using a random function to fill in the details
I want to fill in the item automatically in Item column based on the numbers generated in the prod.code column.

My prod code generates number between 1 and 20 only.
For example p002 in prod code the respective item column should show “Toyota”
For example p003 in prod code the respective item column should show “Honda”
For example p004 in prod code the respective item column should show “Volvo”

My code is below. Where & when should i assign the item values to the product code

Private Sub RAN_NUMB_Click()

Dim RN1 As Long
Dim RN2 As String
Dim RN3 As Range
Dim RN4 As Range
            
            
RN2 = InputBox("ENTER YOUR CHOICE" & vbNewLine _
            & "1.Months" & vbNewLine _
            & "2.Product Code" & vbNewLine _
            & "3.No of Items" & vbNewLine _
            & "4.Sales")
            

Select Case RN2

Case 1                                               'Months

For Each RN3 In Range("b2:b15")

RN3.Value = MonthName(Int((10 - 1 + 1) * Rnd + 1), True)

Next


Case 2                                                  'product Codes

For Each RN3 In Range("c2:C15")
RN3.Value = "PC00" & (Int((20 - 1 + 1) * Rnd + 1))
Next RN3

 
End Select


End Sub

Hi, grkchakri,

if you want to to hard-code the brand names you may work like this:

Private Sub RAN_NUMB_Click()

Dim RN1 As Long
Dim RN2 As String
Dim RN3 As Range
Dim RN4 As Range

'MsgBox ("Create Random Numbers for " & vbNewLine _
            & "1.Months" & vbNewLine _
            & "2.Product Code" & vbNewLine _
            & "3.No of Items" & vbNewLine _
            & "4.Sales")
            
            
RN2 = InputBox("ENTER YOUR CHOICE" & vbNewLine _
            & "1.Months" & vbNewLine _
            & "2.Product Code" & vbNewLine _
            & "3.No of Items" & vbNewLine _
            & "4.Sales")
            

Select Case RN2
  Case 1                 'Months
    For Each RN3 In Range("b2:b15")
      RN3.Value = MonthName(Int((10 - 1 + 1) * Rnd + 1), True)
    Next
  Case 2                     'product Codes
  
    For Each RN3 In Range("c2:C15")
      RN3.Value = "PC00" & (Int((20 - 1 + 1) * Rnd + 1))
    Next RN3
    
    For Each RN4 In Range("D2:D15")
      Select Case RN4.Offset(0, -1).Value
        Case "PC002"
          RN4.Value = "Toyota"
        Case "PC003"
          RN4.Value = "Honda"
        Case Else
          RN4.Value = RN4.Offset(0, 1).Value
      End Select
    Next RN4
End Select
End Sub

I´d suggest you work with a table and use WorksheetFnction.VLookUp to deliver the brand name.

Ciao,
Holger

you mean to say create a seprate product table with ids and names and use vlookup in my table . did i get that right

Hi, grkchakri,

exactly that - you would have less code and the ability to change within the sheet instead of changing code.

Ciao,
Holger