Variable value does not pass between the 2 books

K

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to run a macro of my book 2 ("Corrections_Data.xlsm") from my book 1 ("Machine_Data.xls"), in my book 1 I have two variables called "k_miter" and "k_machine" that count the last line filled in a certain column in a specific spreadsheet. For the value of the variable to pass from one book to another I created a parameter in book 2, but for some reason the value of the variable is not passed between the 2 books, can someone help me?


Book1:

Code: 
Private Sub FillCells()

    Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Activate
    k_machine = Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(Rows.Count, "B").End(xlUp).Offset(1).Row
    
    Cells(k_machine, "B").Value = MachineName
    Cells(k_machine, "C").Value = N_OT
    Cells(k_machine, "D").Value = Tec
    Cells(k_machine, "E").Value = Date_Todayt
    Cells(k_machine, "F").Value = hour
    Cells(k_machine, "G").Value = A_Grid_Orig
    Cells(k_machine, "H").Value = C_Grid_Orig
    Cells(k_machine, "I").Value = A2
    Cells(k_machine, "J").Value = A4
    Cells(k_machine, "K").Value = Xrel_90
    
    
    'AC After
    Cells(k_machine, "M").Value = A_Grid_Orig_D
    Cells(k_machine, "N").Value = C_Grid_Orig_D
    Cells(k_machine, "O").Value = A2_D
    Cells(k_machine, "P").Value = A4_D
    Cells(k_machine, "Q").Value = Xrel_90_D
    
    
    'Sphere Before
    Cells(k_machine, "S").Value = P666
    Cells(k_machine, "T").Value = P709
    Cells(k_machine, "U").Value = P710
    Cells(k_machine, "V").Value = P713
    Cells(k_machine, "W").Value = A0
    Cells(k_machine, "X").Value = A_90
    Cells(k_machine, "Y").Value = A90
    Cells(k_machine, "Z").Value = Delta_A
    
    
    'Sphere After
    Cells(k_machine, "AB").Value = P666r
    Cells(k_machine, "AC").Value = P709r
    Cells(k_machine, "AD").Value = P710r
    Cells(k_machine, "AE").Value = P713r
    Cells(k_machine, "AF").Value = A0r
    Cells(k_machine, "AG").Value = A_90r
    Cells(k_machine, "AH").Value = A90r
    Cells(k_machine, "AI").Value = Delta
    
    
    'Compensation Before
    Cells(k_machine, "AK").Value = C0_A
    Cells(k_machine, "AL").Value = C315_A
    Cells(k_machine, "AM").Value = C270_A
    Cells(k_machine, "AN").Value = C225_A
    Cells(k_machine, "AO").Value = C180_A
    Cells(k_machine, "AP").Value = C135_A
    Cells(k_machine, "AQ").Value = C90_A
    Cells(k_machine, "AR").Value = C45_A
    Cells(k_machine, "AS").Value = InitialSpread
    
    
    'Compensation After
    Cells(k_machine, "AU").Value = C0
    Cells(k_machine, "AV").Value = C315
    Cells(k_machine, "AW").Value = C270
    Cells(k_machine, "AX").Value = C225
    Cells(k_machine, "AY").Value = C180
    Cells(k_machine, "AZ").Value = C135
    Cells(k_machine, "BA").Value = C90
    Cells(k_machine, "BB").Value = C45
    
    
    'Spreads
    Cells(k_machine, "BE").Value = InitialSpread
    Cells(k_machine, "BF").Value = InitialSpread
    Cells(k_machine, "BG").Value = Runnout
    
    Application.Run "Corrections_Data.xlsm! Text_In_Cells_Offset", k_miter

End Sub



Book2:

VBA Code: 
Public Sub Text_In_Cells_Offset(k_machine As Long)

    'Color Cells 1

    If Cells(k_machine, "I").Value < 0 And Cells(k_machine, "J").Value < 0 Then
    
        If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
            ActiveCell.Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
    
        End If
    
              
    ElseIf Cells(k_machine, "I").Value > 0 And Cells(k_machine, "J").Value < 0 Then

        If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
        
        End If
    
        
          
    ElseIf Cells(k_machine, "I").Value > 0 And Cells(k_machine, "J").Value > 0 Then

        If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
        
        End If
          
          
          
    ElseIf Cells(k_machine, "I").Value < 0 And Cells(k_machine, "J").Value > 0 Then

        If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_miter, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
        
        End If
        
    End If

    
    

    'ColorCells2
    
    If Cells(k_machine, "K").Value < -13 * 1 Or Cells(k_machine, "K").Value > 13 Then
        Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
        Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
        
    ElseIf Cells(k_machine, "K").Value >= -13 * 1 Or Cells(k_machine, "K").Value <= 13 Then
        Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
        Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
        Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
    
    End If




    'Color Cells 3

    If Cells(k_machine, "O").Value < 0 And Cells(k_machine, "P").Value < 0 Then
    
        If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
            ActiveCell.Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
    
        End If
    
              
    ElseIf Cells(k_machine, "O").Value > 0 And Cells(k_machine, "P").Value < 0 Then

        If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
        
        End If
    
        
          
    ElseIf Cells(k_machine, "O").Value > 0 And Cells(k_machine, "P").Value > 0 Then

        If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
        
        End If
          
          
          
    ElseIf Cells(k_machine, "I").Value < 0 And Cells(k_machine, "J").Value > 0 Then

        If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
        
        ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_miter, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 4
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
            Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
        
        End If
        
    End If


0 Value.JPG
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
I don't see where you have initialized k_miter. So, in this case, k_miter will be empty. And so when it's passed to Text_In_Cells_Offset, k_machine gets assigned 0 since it's declared as Long. However, 0 cannot be used as a value for the row. As a reuslt, Cells(k_machine, "I").Value causes an error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
I can't insert the array values in the excel sheet
Replies
5
Views
768
RoryA
RoryA
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
285
Alex Blakenburg
A
B
  • Question
How do i open a csv file created in excel and do a find and replace of this file and save as a new file without strange characters ??
Replies
1
Views
618
Special-K99
S
S
  • Question
Worksheet Change Event
Replies
2
Views
380
sspatriots
S
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
376
sspatriots
S

Forum statistics

Threads
1,215,013
Messages
6,122,694
Members
449,092
Latest member
snoom82

Share this page

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
Back
Top