How to pass values

[Pookiemeister]

In my workbook I have seven sheets. Sheets four through seven contain values of product information for our four production lines.
The below code starts on sheet 4 and loops through each row of column B until it finds the value of the combobox located on the userform named Chattem or Chattemfrm.cmbPrdCde.Value. It then will select that cell containing that value and offset to obtain the new cells value and assign that value to the corresponding variable of either txtDz, txtCs, and txtUOM. These variables are needed for a formula located in on the Chattemfrm. Once the user clicks on the command button labeled Print or cmdPrint the formula is calculated. The txtbxdz.value comes from the Chattemfrm Userform.
An excerpt of the formula is below.

textValUp = ((txtbxdz.Value) / txtDz / txtCs) + 0.5 - 1E-16

The problem that I am having is how do I pass the value of those integers inside my sub to be used inside the above formula located on Chattemfrm. I thought about possibly declaring those variables as Public but wasn't sure how to do that in this situation or maybe there might be a better way.

Thank you for any help offered.

Option Explicit

Sub Test()

Dim ws_count, i, FinalRow, x, txtDz, txtCs As Integer
Dim txtUOM As String


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If Cells(x, 2) & " " & "(" & Cells(x, 3) & ")" = Chattemfrm.cmbPrdCde.Value Then
                Cells(x, 2).Select
                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value
                txtUOM = Cells(x, 2).Offset(0, 4).Value
            End If
        Next x
    Next i
End Sub

 

[JonXL]

Passing as a value vs a reference affects how the function treats the variable you pass it. If you pass it as a value, it basically works off a copy of your variable. If you pass it as a reference, your original variable will be updated to reflect any of the changes made to it during the function's run.

If you want to pass things around by value without using Public variables, you can try passing them through a function.

Public Function Test(TextBox as Double) as Double
Dim txtDz As Integer, txtCs As Integer
Dim txtUOM As String
Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count

    For i = 4 To ws_count
    
        With Worksheets(i)
        
            FinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
            
            For x = 1 To FinalRow
            
                If .Cells(x, 2).Text & " " & "(" & .Cells(x, 3).Text & ")" = Chattemfrm.cmbPrdCde.Value Then
                    txtDz = .Cells(x, 2).Offset(0, 2).Value
                    txtCs = .Cells(x, 2).Offset(0, 3).Value
                    txtUOM = .Cells(x, 2).Offset(0, 4).Value
                    '.Cells(x, 2).Interior.ColorIndex = 3
                End If
                
            Next x
            
        End With
        
    Next i
    
Test = ((TextBox) / txtDz / txtCs)
    
End Function

Adjust your formula to look like this:

textValUp = Test(txtbxdz.Value) + 0.5 - 1E-16

I'm very doubtful this will solve your error, but maybe some of the adjustments I made to your procedure will make a difference.

I'm also not sure this will produce the desired result even if it does get rid of the divide by zero error, since I'm not really sure what you're trying to accomplish.

Best of luck, though.

ABE: Also, what is your intention with txtUOM? Once the function finishes, it's done and I don't see you making use of it anywhere. Does it need to be a Public variable?

 

[Pookiemeister]

I am displaying all the code that might be causing the problem. I don't see any issue but I'm still new at VBA.
This is the code that comes before the module.

Private Sub cmdbtnDone_Click()    
    'Declare variables
    Dim r As Range, r1 As Range
            
    'Sets variables to a range
    Set r = Range(RefEdit1)
    Set r1 = r(1)
    
    wbName = ActiveWorkbook.Name
    
    Load Chattemfrm
    Chattemfrm.cmbSDPFLine.Value = ActiveWorkbook.Name
    Chattemfrm.cmbPrdCde.Value = r1.Offset(0, -6).Value
    Chattemfrm.txtbxPrdctNm.Value = r1.Offset(0, -5).Value
    Chattemfrm.txtBxLtNum.Value = r1.Offset(0, -3).Value
    Chattemfrm.txtBxShopNumber.Value = r1.Offset(0, 1).Value
    Chattemfrm.txtbxVndrLtNu.Value = r1.Offset(0, -2).Value
    Chattemfrm.txtbxdz = Me.txtbxRangeTotal.Value
        
    Select Case Chattemfrm.cmbSDPFLine.Value
        Case Is = "SDPF - LINE 1 (SLAT).xlsx"
            Chattemfrm.cmbSDPFLine.Value = "Slat"
        Case Is = "SDPF - LINE 2A.xlsx"
            Chattemfrm.cmbSDPFLine.Value = "Uhlmann"
        Case Is = "SDPF - LINE 3.xlsx"
            Chattemfrm.cmbSDPFLine.Value = "Korber"
        Case Is = "SDPF - LINE 4.xlsx"
            Chattemfrm.cmbSDPFLine.Value = "IMA"
    End Select
    
    Unload Me
    Workbooks(wbName).Activate
    ActiveWorkbook.Close
[B][COLOR=#ff0000]    Call Test[/COLOR][/B]
    Chattemfrm.Show
    
End Sub

This is the module code again.

Option Explicit
Public [B]txtDz[/B] As Integer, [B]txtCs[/B] As Integer
Public [B]txtUOM[/B] As String
Sub Test()


Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If Cells(x, 2) & " " & "(" & Cells(x, 3) & ")" = Chattemfrm.cmbPrdCde.Value Then
                Cells(x, 2).Select
                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value
                txtUOM = Cells(x, 2).Offset(0, 4).Value
            End If
        Next x
    Next i
End Sub

And lastly is the cmdbtnPrint_Click() I bold faced typed the Dim of the same declared variables as the Sub.

Private Sub cmdbtnPrint_Click()
    
    Dim MyCount As Long
    Dim iDate As String
    Dim ShopNum As Integer
    Dim iDateNum As Integer
    Dim Rtn_num As Integer
    Dim rtn_ans As String
    Dim myWrkBk As Workbook
    Dim mySheet As Worksheet
    Dim textValUp As Long
    Dim textValDown As Long
    Dim numLenA As Long
    Dim numLenF As Long
    Dim startNum As Integer
    Dim [B]txtUOM[/B] As String
    Dim [B]txtCs[/B] As Long
    Dim [B]txtDz[/B] As Long
    Dim totRows As Long, i As Long
    
  'Converts month names to numerical value equivalent
   Select Case cmbMonth
        Case Is = "January"
            iDateNum = "1"
        Case Is = "February"
            iDateNum = "2"
        Case Is = "March"
            iDateNum = "3"
        Case Is = "April"
            iDateNum = "4"
        Case Is = "May"
            iDateNum = "5"
        Case Is = "June"
            iDateNum = "6"
        Case Is = "July"
            iDateNum = "7"
        Case Is = "August"
            iDateNum = "8"
        Case Is = "September"
            iDateNum = "9"
        Case Is = "October"
            iDateNum = "10"
        Case Is = "November"
            iDateNum = "11"
        Case Is = "December"
            iDateNum = "12"
    End Select
    
    'Places/unloads inputed data from textboxes into its corresponding cell
    'frmChattem.Hide
    
    iDate = iDateNum
    strE2 = "'" & cmbPrdCde.Text
    Worksheets("Placard").Activate
    Range("'Placard'!$E$2").Value = CStr(Left(strE2, 9))
    Range("'Placard'!$E$4").Value = txtBxLtNum.Text
    Range("'Placard'!$E$6").Value = txtBxShopNumber.Text
    Range("'Placard'!$E$8").Value = iDate & "/" & "      " & "/" & Year(Date) & "     " & CheBxX & "   " & CheBxY & "   " & CheBxZ
    Range("'Placard'!$E$11").Value = txtbxPrdctNm.Text
    Worksheets("Finished Goods Summary").Activate
    Range("'Finished Goods Summary'!$C$2:$E$2").Value = iDate & "/" & "      " & "/" & Year(Date)
    Range("'Finished Goods Summary'!$C$3:$E$3").Value = Range("'Placard'!$E$2").Value
    Range("'Finished Goods Summary'!$C$4:$E$4").Value = txtbxPrdctNm.Text
    Range("'Finished Goods Summary'!$C$5:$E$5").Value = txtBxLtNum.Text
    Range("'Finished Goods Summary'!$C$6:$E$6").Value = txtBxShopNumber.Text
    Worksheets("Solid Dose Usage Record").Activate
    Range("'Solid Dose Usage Record'!$C$3:$D$3").Value = txtbxStckNum.Text
    Range("'Solid Dose Usage Record'!$C$5:$D$5").Value = txtBxLtNum.Text
    Range("'Solid Dose Usage Record'!$C$7:$D$7").Value = txtbxVndrLtNu.Text
    Range("'Solid Dose Usage Record'!$G$3:$K$3").Value = txtbxPrdctNm.Text
    
    textValUp = ((txtbxdz.Value) / txtDz / txtCs) + 0.5 - 1E-16
    textValDown = ((txtbxdz.Value) / txtDz / txtCs) - 0.5 + 1E-16
    startNum = 1


    Set myWrkBk = ActiveWorkbook
    Set mySheet = myWrkBk.Sheets("Finished Goods Summary")
    Do
        If textValUp >= 30 Then
            numLenA = 30
        Else
            numLenA = textValUp
        End If
        If textValDown >= 30 Then
            numLenF = 30
        Else
            numLenF = textValDown
        End If
        
        With mySheet
            .Range("A9:A38").ClearContents
            .Range("E9:E38").ClearContents
            .Range("F9:F38").ClearContents
            .Range("G9:I38").ClearContents
            .Range("J9:K38").ClearContents


            If numLenA > 0 Then
                .Cells(9, "A") = startNum
                .Range(.Cells(9, "A"), .Cells(numLenA + 8, "A")).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
                .Range(.Cells(9, "E"), .Cells(numLenA + 8, "E")).Value = txtUOM
                .Range(.Cells(9, "G"), .Cells(numLenA + 8, "G")).Value = txtDz
            End If
            If numLenF > 0 Then
                .Range(.Cells(9, "F"), .Cells(numLenF + 8, "F")).Value = txtCs
                .Range(.Cells(9, "J"), .Cells(numLenF + 8, "J")).Value = txtCs * txtDz
            End If
        End With
        textValUp = textValUp - 30
        textValDown = textValDown - 30
        startNum = startNum + 30
        
        'Put code here to print
        If textValUp > 0 Then
            Sheets("Finished Goods Summary").PrintOut
        Else
            Sheets("Finished Goods Summary").PrintOut
        End If
    Loop While textValUp > 0
        MyCount = ((txtbxdz.Value) / txtDz / txtCs) + 0.5 - 1E-16
        Call IncrementPrint(MyCount)
End Sub

Thank You.

 

[Pookiemeister]

So I figured out where the problem occurs but now I'm not sure why? OK...as I am stepping through the code.

Sub Test()

Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If Cells(x, 2) & " " & "(" & Cells(x, 3) & ")" = Chattemfrm.cmbPrdCde.Value Then
                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value
                txtUOM = Cells(x, 2).Offset(0, 4).Value
                Cells(x, 2).Interior.ColorIndex = 3
            End If
            Cells(x, 2).Select
        Next x
    Next i
End Sub

The below code never retrieves a value and I don't know why.

txtDz = Cells(x, 2).Offset(0, 2).ValuetxtCs = Cells(x, 2).Offset(0, 3).Value
txtUOM = Cells(x, 2).Offset(0, 4).Value

Thank you.

 

[JonXL]

First, did you try the version of this loop I pasted earlier that doesn't involve activating and selecting sheets and cells?

Sub Test()

Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
    
        With Worksheets(i)
        
            FinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
            
            For x = 1 To FinalRow
            
                If .Cells(x, 2).Text & " " & "(" & .Cells(x, 3).Text & ")" = Chattemfrm.cmbPrdCde.Value Then
                    txtDz = .Cells(x, 2).Offset(0, 2).Value
                    txtCs = .Cells(x, 2).Offset(0, 3).Value
                    txtUOM = .Cells(x, 2).Offset(0, 4).Value
                    '.Cells(x, 2).Interior.ColorIndex = 3
                End If
                
            Next x
            
        End With
        
    Next i
End Sub

Second, while you're stepping through the code, you can always figure out what your cell references are pointing to by typing them into the immediate window (View>Immediate Window; or Ctrl+G) preceded by a ?; for example:

?Worksheets(i).Cells(x, 2).Offset(0, 2).Address

... will tell you the address of the cell in question. You might find that your code isn't referencing the cells you expect it to be referencing. You can also use it to check the value of your strings in your If statement to make sure that they are, indeed, identical and so properly causing the Then to fire.

Third, is there a reason you are using Offset to reference the cells you want the values from? I don't use Offset unless I'm going to include a variable somewhere in the Offset argument, but since your Offset is the same through every iteration of the loop, you can probably get rid of it and just directly reference the column you want along with the variable row (x):

                    txtDz = .Cells(x, 4).Value
                    txtCs = .Cells(x, 5).Value
                    txtUOM = .Cells(x, 6).Value