May 15 2022 05:47 AM
So, I am trying to assign random codes to products in excel with the RANDBETWEEN, however, I don't want it to add a random number unless there is text in the Item section, every formula I try online gives me an error.
When selected empty first Item Table Cell comes as:
=[@Items]
Empty cell to the right
=[@[Assign Reference]]
I need to combine if "" or If+IFBLANK with RANDBETWEEN
So if =[@Items] is blank then leave blank but if not add Random Arrawy between 100000 and 999999
Everything I try gives one or another error
May 15 2022 05:55 AM
@ShearaKLC Try this:
=IF(ISBLANK([@Items]),"",RANDBETWEEN(100000,999999))
May 15 2022 06:08 AM
May 15 2022 06:11 AM
@ShearaKLC Glad I could help! Don't hesitate to come back here for more help if you can't resolve something within 30 minutes yourself :)
May 15 2022 06:12 AM - edited May 15 2022 06:13 AM
Looks like the formula won't work, as in not suitable for what I need, as it changes every time, I add a value in the next row below, I need it to stay assigned to the column once entered :( coding isn't my strength
May 15 2022 06:25 AM
@ShearaKLC RANDBETWEEN is indeed (re)generating random numbers every time the sheet recalculates. Sounds like you need a VBA solution (i.e. macro) that generates a random number and puts the value in a cell as a fixed value every time a new value is entered in the Items column of the table. Probably doable, but not by me. Sorry!
May 15 2022 06:49 AM
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Dim col As Range
Dim rng As Range
Set tbl = Me.ListObjects("Table1") ' change name if necessary
Set col = tbl.ListColumns("Items").DataBodyRange
If Not Intersect(col, Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(col, Target)
If rng.Value = "" Then
rng.Offset(0, 1).ClearContents
Else
rng.Offset(0, 1) = Application.RandBetween(100000, 9999999)
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
May 15 2022 06:56 AM