itipu
02-27-2007, 11:53 PM
I have a code in a UserForm which goes like this:
Public Sub CommandButton1_Click()
which = ListBox2.Text
Sheets(ListBox1.Text).Activate
Unload Me
End Sub
Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Computer") > 0 And sh.Visible = True Then
ListBox1.AddItem sh.Name
ElseIf InStr(1, sh.Name, "RHN") > 0 And sh.Visible = True Then
ListBox2.AddItem sh.Name
End If
Next
End Sub
Public Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Select Case CloseMode
Case 0: frmExtract.HowClosed = "Forced"
Case 1: frmExtract.HowClosed = "Selected"
End Select
End Sub
which is declared in a module as Global which As String
I try to pass which to the code below located in a different form, but it doesn't work where I marked it with red ;( As always appreciate your help very much!!!!!!!!!!!!!!
Public Sub DifferenceRC(which)
Dim rngA As Range
Dim rngB As Range
Dim rngOut As Range
Dim rngCell As Range
Dim rngFind As Range
Dim Sheet As String
Dim sh As Worksheet
UserForm2.Show
Sheet = "Missing Billing Item"
On Error Resume Next
Set rngA = ActiveSheet.UsedRange
Set rngB = Sheets(which).UsedRange.Columns(1)
Set sh = Worksheets(Sheet)
On Error GoTo 0
If sh Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
Set sh = ActiveSheet
sh.Name = Sheet
End If
sh.Cells.ClearContents
For Each rngCell In rngA.Columns(1).Cells
Set rngFind = rngB.Find(rngCell.Value, , LookIn:=xlValues, LookAt:=xlWhole)
If rngFind Is Nothing Then
rngCell.EntireRow.Copy sh.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next
Unload frmExtract
End Sub
Public Sub CommandButton1_Click()
which = ListBox2.Text
Sheets(ListBox1.Text).Activate
Unload Me
End Sub
Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Computer") > 0 And sh.Visible = True Then
ListBox1.AddItem sh.Name
ElseIf InStr(1, sh.Name, "RHN") > 0 And sh.Visible = True Then
ListBox2.AddItem sh.Name
End If
Next
End Sub
Public Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Select Case CloseMode
Case 0: frmExtract.HowClosed = "Forced"
Case 1: frmExtract.HowClosed = "Selected"
End Select
End Sub
which is declared in a module as Global which As String
I try to pass which to the code below located in a different form, but it doesn't work where I marked it with red ;( As always appreciate your help very much!!!!!!!!!!!!!!
Public Sub DifferenceRC(which)
Dim rngA As Range
Dim rngB As Range
Dim rngOut As Range
Dim rngCell As Range
Dim rngFind As Range
Dim Sheet As String
Dim sh As Worksheet
UserForm2.Show
Sheet = "Missing Billing Item"
On Error Resume Next
Set rngA = ActiveSheet.UsedRange
Set rngB = Sheets(which).UsedRange.Columns(1)
Set sh = Worksheets(Sheet)
On Error GoTo 0
If sh Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
Set sh = ActiveSheet
sh.Name = Sheet
End If
sh.Cells.ClearContents
For Each rngCell In rngA.Columns(1).Cells
Set rngFind = rngB.Find(rngCell.Value, , LookIn:=xlValues, LookAt:=xlWhole)
If rngFind Is Nothing Then
rngCell.EntireRow.Copy sh.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next
Unload frmExtract
End Sub