Forum Discussion
Combining "If" "" / If IFBLANK with another complex formula HELP
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 ...
ShearaKLC Try this:
=IF(ISBLANK([@Items]),"",RANDBETWEEN(100000,999999))
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
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- Been avoiding Macro as worried about compatibility issues with others I might send the file to.
Trying to see if I can have preset number entered somewhere when the items is filled in it prefills with those random numbers. Or add the numbers to the false not blank itself.
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!
