Forum Discussion
Combining "If" "" / If IFBLANK with another complex formula HELP
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!
- OMG thank you, I am new to all this and have learning difficulties, but trying desperately to learn to hopefully work in digital content.
Think I was missing a ) in some attempts or putting the = by Rand... So every time I tried fixing one place I had it wrong in another.
My health has been deteriorating meaning I might no longer be able to do my job in a few years, so trying to work on my skillset and things I can do from home, I don't really have higher qualifications so can't really find remote work. I had been stuck on this one for hours, where I can usually find the answers within 30 minutes. I do try finding the answers first without bothering anyone, but I was trying for hours.
THANK YOU SO MUCHShearaKLC Glad I could help! Don't hesitate to come back here for more help if you can't resolve something within 30 minutes yourself 🙂
