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 ...
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.