| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not transferring?
With a lot of help here, I've written some code to compare 2
spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
variable not transferring?
Option Explicit
Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook needs to be placed at the top of a general module. Public variables in a general module have project wide scope. Public variables in a userform have module level scope by default although they can also be treated as properties of the class. -- Regards, Tom Ogilvy "davegb" wrote: With a lot of help here, I've written some code to compare 2 spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
variable not transferring?
Tom Ogilvy wrote: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook needs to be placed at the top of a general module. Public variables in a general module have project wide scope. Public variables in a userform have module level scope by default although they can also be treated as properties of the class. -- Regards, Tom Ogilvy Tom, thanks for your help. I had declared the variables publicly in both the userform module and the general module. When I read your reply, I remarked them out in the userform module. Now I'm getting a Subscript out of range error on that same code. Any suggestions? "davegb" wrote: With a lot of help here, I've written some code to compare 2 spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
variable not transferring?
Are you getting the error on one of these lines?
Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) If yes, then the current value in that combobox (that's causing the error) doesn't refer back to a workbook that's open. Could it be that the user hasn't made a choice and that .text value is still ""??? davegb wrote: Tom Ogilvy wrote: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook needs to be placed at the top of a general module. Public variables in a general module have project wide scope. Public variables in a userform have module level scope by default although they can also be treated as properties of the class. -- Regards, Tom Ogilvy Tom, thanks for your help. I had declared the variables publicly in both the userform module and the general module. When I read your reply, I remarked them out in the userform module. Now I'm getting a Subscript out of range error on that same code. Any suggestions? "davegb" wrote: With a lot of help here, I've written some code to compare 2 spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
variable not transferring?
Dave Peterson wrote: Are you getting the error on one of these lines? Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) If yes, then the current value in that combobox (that's causing the error) doesn't refer back to a workbook that's open. Could it be that the user hasn't made a choice and that .text value is still ""??? Thanks for your reply, Dave. I found the problem. The user supplying the spreadsheets to be compared has been renaming the first sheet instead of leaving it "Sheet1"! Doh! Thanks again to all. davegb wrote: Tom Ogilvy wrote: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook needs to be placed at the top of a general module. Public variables in a general module have project wide scope. Public variables in a userform have module level scope by default although they can also be treated as properties of the class. -- Regards, Tom Ogilvy Tom, thanks for your help. I had declared the variables publicly in both the userform module and the general module. When I read your reply, I remarked them out in the userform module. Now I'm getting a Subscript out of range error on that same code. Any suggestions? "davegb" wrote: With a lot of help here, I've written some code to compare 2 spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
variable not transferring?
Glad you found the problem.
(I didn't notice your not in your code. Sorry.) davegb wrote: Dave Peterson wrote: Are you getting the error on one of these lines? Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) If yes, then the current value in that combobox (that's causing the error) doesn't refer back to a workbook that's open. Could it be that the user hasn't made a choice and that .text value is still ""??? Thanks for your reply, Dave. I found the problem. The user supplying the spreadsheets to be compared has been renaming the first sheet instead of leaving it "Sheet1"! Doh! Thanks again to all. davegb wrote: Tom Ogilvy wrote: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook needs to be placed at the top of a general module. Public variables in a general module have project wide scope. Public variables in a userform have module level scope by default although they can also be treated as properties of the class. -- Regards, Tom Ogilvy Tom, thanks for your help. I had declared the variables publicly in both the userform module and the general module. When I read your reply, I remarked them out in the userform module. Now I'm getting a Subscript out of range error on that same code. Any suggestions? "davegb" wrote: With a lot of help here, I've written some code to compare 2 spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
variable not transferring?
(I didn't notice your notE in your code. Sorry.)
Dave Peterson wrote: Glad you found the problem. (I didn't notice your not in your code. Sorry.) davegb wrote: Dave Peterson wrote: Are you getting the error on one of these lines? Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) If yes, then the current value in that combobox (that's causing the error) doesn't refer back to a workbook that's open. Could it be that the user hasn't made a choice and that .text value is still ""??? Thanks for your reply, Dave. I found the problem. The user supplying the spreadsheets to be compared has been renaming the first sheet instead of leaving it "Sheet1"! Doh! Thanks again to all. davegb wrote: Tom Ogilvy wrote: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook needs to be placed at the top of a general module. Public variables in a general module have project wide scope. Public variables in a userform have module level scope by default although they can also be treated as properties of the class. -- Regards, Tom Ogilvy Tom, thanks for your help. I had declared the variables publicly in both the userform module and the general module. When I read your reply, I remarked them out in the userform module. Now I'm getting a Subscript out of range error on that same code. Any suggestions? "davegb" wrote: With a lot of help here, I've written some code to compare 2 spreadsheets. But when I couple the code to run a userform to get the filenames to compare with the code to align the data in corresponding rows, the second macro doesn't recognize the workbook names that have been saved in the first macro. Here's the code with the userform: Option Explicit Public wsNew As Worksheet Public wsOrig As Worksheet Public wbNew As Workbook Public wbOrig As Workbook Private Sub CancelButton_Click() Unload UserForm2 End Sub Private Sub OkButton_Click() 'Workbooks(ComboBox1.Text).Select Set wbNew = Workbooks(ComboBox1.Text) Set wbOrig = Workbooks(ComboBox2.Text) 'wbNew.Activate UserForm2.Hide Call EvenOutRows End Sub Private Sub UserForm_Initialize() Dim wbk As Workbook For Each wbk In Workbooks ComboBox1.AddItem wbk.Name ComboBox2.AddItem wbk.Name Next wbk End Sub And the other code: Sub EvenOutRows() Dim wsNew As Worksheet Dim wsOrig As Worksheet Dim lCurRow As Long Dim lrowNew As Long Dim lrowOrig As Long UserForm2.Show Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR WITHBLOCK VARIABLE NOT SET Set wsNew = wbNew.Worksheets("sheet1") lCurRow = 2 Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _ Not IsEmpty(wsOrig.Cells(lCurRow, 1)) If wsNew.Cells(lCurRow, 1) wsOrig.Cells(lCurRow, 1) Then wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsOrig.Cells(lCurRow, 1) wsNew.Cells(lCurRow, 1) Then wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1 ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then lCurRow = lCurRow + 1 End If Loop Application.Run "Compare.xla!Compare" End Sub The variables have been declared publicly in both modules. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transferring like data | Excel Discussion (Misc queries) | |||
| Transferring Data | Excel Worksheet Functions | |||
| Transferring Data | Excel Discussion (Misc queries) | |||
| Transferring rows with variable criteria | Excel Programming | |||
| transferring code from VBA to a VB dll | Excel Programming | |||
Linear Mode
