Random Number changes while running a macro when I don't want it to.

[jondavis1987]

I have the following formula in a cell. It is just a random number generator with conditions.

=IFERROR(IF(AND(F18>5,F18<8),((ROUND(RAND()+RANDBETWEEN(98+F18,102+F18),1))*453.6)*0.2485,IF(F18>8,((ROUND(RAND()+RANDBETWEEN(107,110.6),1))*453.6)*0.2485,((ROUND(RAND()+RANDBETWEEN(102,105),1))*453.6)*0.2485)),"")

When I get done entering all the information in the worksheet I start a macro.
The macro updates certain areas of other workbooks depending on a location name listed in B4. The thing I have noticed is when I use one certain location in the name this random generated number will change while the macro is running. It doesn't do this with any of the other locations. It wouldn't be a big deal but this number changes sometime between the line of code

   '   Copy PCF data from source workbook to destination workbook
    With pcfWS
        .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWS.Range("F5").Value
        .Range("B" & .Cells(Rows.Count, "B").End(xlUp).Row + 0).Value = srcWS.Range("K9").Value
    End With

and

 '   Save changes and close destination workbook
    secondDestWB.Close SaveChanges:=True
    
    '   Export source workbook to PDF
    With srcWB
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                        "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & LocationName & "\" & fName, Quality:=xlQualityStandard, _
                                        includeDocProperties:=True, ignoreprintareas:=False, openafterpublish:=True
    End With

This makes it where the random number that's saved in a different workbook doesn't match the pdf. There's no extra steps in this macro if that location is listed and there's no extra formula steps in any of the worksheet if that location is listed. Any suggestions or am I just gonna have to do the copy and paste method on the random number whenever it's this location?

 Option Explicit

Sub Recycle_Gradations()
    
    Dim srcWB        As Workbook
    Dim destWB       As Workbook
    Dim fName        As String
    Dim destName     As String
    Dim wsName       As String
    Dim smplWS       As Worksheet
    Dim sievesWS     As Worksheet
    Dim srcWS        As Worksheet
    Dim LocationName As String
    Dim pcfWS        As Worksheet
    Dim secondDestWB As Workbook
    
    Set srcWB = Workbooks("Recycle Concrete Templates")
    Set srcWS = srcWB.Sheets("Ag Base")
    wsName = srcWS.Range("D22").Text
    fName = srcWS.Range("C22").Value
    LocationName = srcWS.Range("B4").Value

    
    '   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Aggregates\Recycled Concrete\Ag Base Yearly Chart.xlsx"
        Set destWB = Workbooks("Ag Base Yearly Chart")
        Set smplWS = destWB.Sheets("Samples")
        Set sievesWS = destWB.Sheets("Sieves")
    
    '   Unhide_Multiple_Sheets()
    sievesWS.Visible = True
    smplWS.Visible = True
    
    '   Copy Sieve data from source workbook to destination workbook
    With sievesWS
        .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(7, 1).Value = srcWS.Range("H3").Value
        .Range("B" & .Cells(Rows.Count, "B").End(xlUp).Row - 6).Resize(7, 1).Value = srcWS.Range("A2").Value
        .Range("C" & .Cells(Rows.Count, "C").End(xlUp).Row - 6).Resize(7, 1).Value = srcWS.Range("F5").Value
        .Range("D" & .Cells(Rows.Count, "D").End(xlUp).Row - 6).Resize(7, 1).Value = srcWS.Range("B4").Value
        .Range("F" & .Cells(Rows.Count, "F").End(xlUp).Row - 6).Resize(7).Value = srcWS.Range("A12:A18").Value
        .Range("G" & .Cells(Rows.Count, "G").End(xlUp).Row - 6).Resize(7).Value = srcWS.Range("F12:F18").Value
        .Range("H" & .Cells(Rows.Count, "H").End(xlUp).Row - 6).Resize(7).Value = srcWS.Range("L12:L18").Value
        .Range("I" & .Cells(Rows.Count, "I").End(xlUp).Row - 6).Resize(7).Value = srcWS.Range("M12:M18").Value
        .Range("J" & .Cells(Rows.Count, "J").End(xlUp).Row - 6).Resize(7, 1).Value = srcWS.Range("H10").Value
        .Range("L" & .Cells(Rows.Count, "L").End(xlUp).Row - 6).Resize(7, 1).Value = srcWS.Range("J4").Value
    End With
    
    '   Copy Samples data from source workbook to destination workbook
    With smplWS
        .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWS.Range("H3").Value
        .Range("B" & .Cells(Rows.Count, "B").End(xlUp).Row + 0).Value = srcWS.Range("A2").Value
        .Range("C" & .Cells(Rows.Count, "C").End(xlUp).Row + 0).Value = srcWS.Range("F5").Value
        .Range("D" & .Cells(Rows.Count, "D").End(xlUp).Row + 0).Value = srcWS.Range("B4").Value
        .Range("F" & .Cells(Rows.Count, "F").End(xlUp).Row + 0).Value = srcWS.Range("K19").Value
        .Range("G" & .Cells(Rows.Count, "G").End(xlUp).Row + 0).Value = srcWS.Range("K20").Value
        .Range("H" & .Cells(Rows.Count, "H").End(xlUp).Row + 0).Value = srcWS.Range("K22").Value
        .Range("L" & .Cells(Rows.Count, "L").End(xlUp).Row + 0).Value = srcWS.Range("J4").Value
    End With
    
    '   Hide_Multiple_Sheets()
    sievesWS.Visible = False
    smplWS.Visible = False
    
    '   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
    
    '   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Aggregates\Recycled Concrete\Ag Base PCF.xlsx"
    Set secondDestWB = Workbooks("Ag Base PCF")
    Set pcfWS = Workbooks("Ag Base PCF").Sheets(wsName)
    
    '   Copy PCF data from source workbook to destination workbook
    With pcfWS
        .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWS.Range("F5").Value
        .Range("B" & .Cells(Rows.Count, "B").End(xlUp).Row + 0).Value = srcWS.Range("K9").Value
    End With
    
    '   Save changes and close destination workbook
    secondDestWB.Close SaveChanges:=True
    
    '   Export source workbook to PDF
    With srcWB
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                        "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & LocationName & "\" & fName, Quality:=xlQualityStandard, _
                                        includeDocProperties:=True, ignoreprintareas:=False, openafterpublish:=True
    End With
End Sub

 

[Joe4]

Yes, I think RANDBETWEEN will recalculate as other cells on the sheet are being recalculated.
Since you are already using VBA, why not have VBA do the Random number part at the beginning of the code, and then paste the value into your sheet.
Then it will not change.

 

[jondavis1987]

Yes, I think RANDBETWEEN will recalculate as other cells on the sheet are being recalculated.
Since you are already using VBA, why not have VBA do the Random number part at the beginning of the code, and then paste the value into your sheet.
Then it will not change.

Like just have the formula inserted into the cell and then copy and paste it back into the cell? I almost thought about just having the random number generated be hidden somewhere and then having the vba do the range.value=range.value of the random number generator. Thanks for the advice.

 

[Joe4]

Like just have the formula inserted into the cell and then copy and paste it back into the cell? I almost thought about just having the random number generated be hidden somewhere and then having the vba do the range.value=range.value of the random number generator. Thanks for the advice.

I was thinking more of doing the whole random number generation done right in VBA, and then paste it to the cell, but whatever makes the most sense for you.

 

[jondavis1987]

I was thinking more of doing the whole random number generation done right in VBA, and then paste it to the cell, but whatever makes the most sense for you.

Honestly the thought of figuring out how to turn that into vba code just sounds mentally exhausting today.

 

[Joe4]

Honestly the thought of figuring out how to turn that into vba code just sounds mentally exhausting today.

If you have a formula that is working the way you want, then you can keep it that way.
I like your idea of doing it some random cell somewhere, and just copy it over at the beginning of your macro.