What would you do? User needs to generate a random number and ideally not leave the worksheet
Hey Gang! I have a spreadsheet (see below) that requires the user to generate unique identifiers for the PCODE row and the BUNDLE IDENTIFIER row. What I have them doing is clicking on another worksheet and clicking the appropriate button, then return to the original worksheet and paste the value into the cell. The buttons are connected to a VBA routine that does the actual generation.
I have been struggling with how I can do this from within the same worksheet. The worksheet consists of different row types (PCODE, BUNDLE IDENTIFIER, COMPONENT), as the user builds out their bundle.
Update – I have found a method, which has the formula embedded in the target cell. The formula is connected to a VBA function. This works however, I need to copy that value into subsequent rows, and if I drag it, the formula comes along, so I am forced to copy and paste values which is a tad clunky.
formula – =ConcatRandomNumberWithSuffix(“B”)
It will also be subject to changing the value if i refresh the spreadsheet which would be a disaster.
Sub GeneratePCODE_Click()
Dim randomNumber As Long
Dim formattedNumber As String
Dim finalString As String
Dim ws As Worksheet
Dim genPCODEBUNDLEID As Worksheet
Dim cell As Range
! FOR PCODE
‘ Set the worksheet location for the output variables
Set genPCODEBUNDLEID = ThisWorkbook.Sheets(“Generate_PCODE_BUNDLEID”)
Set cell = genPCODEBUNDLEID.Range(“D9”)
cell.Value = “” ‘ clear out the value in the spreadsheet
genPCODEBUNDLEID.Calculate ‘ recalculate the sheet
‘ Generate a random number between 1,000,000 and 9,999,998
randomNumber = Application.WorksheetFunction.RandBetween(1000000, 9999998)
‘ Format the number with leading zeros to make it a 7-digit number
formattedNumber = Format(randomNumber, “0000000”)
‘ Concatenate the formatted number with “Q”
finalString = formattedNumber & “Q”
‘ Set the PCODE value
cell.Value = finalString
End Sub
Sub GenerateBUNDLEID_Click()
Dim randomNumber As Long
Dim formattedNumber As String
Dim finalString As String
Dim ws As Worksheet
Dim genPCODEBUNDLEID As Worksheet
Dim cell As Range
! FOR BUNDLEID
‘ Set the worksheet location for the output variables
Set genPCODEBUNDLEID = ThisWorkbook.Sheets(“Generate_PCODE_BUNDLEID”)
Set cell = genPCODEBUNDLEID.Range(“D18”)
cell.Value = “” ‘ clear out the value in the spreadsheet
genPCODEBUNDLEID.Calculate ‘ recalculate the sheet
‘ Generate a random number between 1,000,000 and 9,999,998
randomNumber = Application.WorksheetFunction.RandBetween(1000000, 9999998)
‘ Format the number with leading zeros to make it a 7-digit number
formattedNumber = Format(randomNumber, “0000000”)
‘ Concatenate the formatted number with “B”
finalString = formattedNumber & “B”
cell.Value = finalString
End Sub
Hey Gang! I have a spreadsheet (see below) that requires the user to generate unique identifiers for the PCODE row and the BUNDLE IDENTIFIER row. What I have them doing is clicking on another worksheet and clicking the appropriate button, then return to the original worksheet and paste the value into the cell. The buttons are connected to a VBA routine that does the actual generation. I have been struggling with how I can do this from within the same worksheet. The worksheet consists of different row types (PCODE, BUNDLE IDENTIFIER, COMPONENT), as the user builds out their bundle. Update – I have found a method, which has the formula embedded in the target cell. The formula is connected to a VBA function. This works however, I need to copy that value into subsequent rows, and if I drag it, the formula comes along, so I am forced to copy and paste values which is a tad clunky.formula – =ConcatRandomNumberWithSuffix(“B”)It will also be subject to changing the value if i refresh the spreadsheet which would be a disaster. Sub GeneratePCODE_Click()Dim randomNumber As LongDim formattedNumber As StringDim finalString As StringDim ws As WorksheetDim genPCODEBUNDLEID As WorksheetDim cell As Range! FOR PCODE’ Set the worksheet location for the output variablesSet genPCODEBUNDLEID = ThisWorkbook.Sheets(“Generate_PCODE_BUNDLEID”)Set cell = genPCODEBUNDLEID.Range(“D9”)cell.Value = “” ‘ clear out the value in the spreadsheetgenPCODEBUNDLEID.Calculate ‘ recalculate the sheet’ Generate a random number between 1,000,000 and 9,999,998randomNumber = Application.WorksheetFunction.RandBetween(1000000, 9999998)’ Format the number with leading zeros to make it a 7-digit numberformattedNumber = Format(randomNumber, “0000000”)’ Concatenate the formatted number with “Q”finalString = formattedNumber & “Q”‘ Set the PCODE valuecell.Value = finalStringEnd SubSub GenerateBUNDLEID_Click()Dim randomNumber As LongDim formattedNumber As StringDim finalString As StringDim ws As WorksheetDim genPCODEBUNDLEID As WorksheetDim cell As Range! FOR BUNDLEID’ Set the worksheet location for the output variablesSet genPCODEBUNDLEID = ThisWorkbook.Sheets(“Generate_PCODE_BUNDLEID”)Set cell = genPCODEBUNDLEID.Range(“D18”)cell.Value = “” ‘ clear out the value in the spreadsheetgenPCODEBUNDLEID.Calculate ‘ recalculate the sheet’ Generate a random number between 1,000,000 and 9,999,998randomNumber = Application.WorksheetFunction.RandBetween(1000000, 9999998)’ Format the number with leading zeros to make it a 7-digit numberformattedNumber = Format(randomNumber, “0000000”)’ Concatenate the formatted number with “B”finalString = formattedNumber & “B”cell.Value = finalStringEnd Sub Read More
