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

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
I have the following formula in a cell. It is just a random number generator with conditions.
Excel Formula:
=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
VBA 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
VBA Code:
 '   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?
VBA Code:
 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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,766
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top