LinkBack URL
About LinkBacks
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
Register To Reply
Bookmarks