PDA

View Full Version : End If without Block If



sgr
05-13-2010, 09:36 PM
I am in a bind. Please help.

I have a workbook in which Sheet 1 is populated by the user. Sheet 2 reorganizes the data (user doesn't touch or see Sheet 2). The user clicks a command button that will ultimately upload data from Sheet 2 into another workbook.
When the user clicks the command button, a user form loads.
1. ListBox 1 contains a list of items from the first column of Sheet2 -Good.
2. Items can be added from ListBox1 to ListBox2 - Good.
3. The row (from Sheet2) corresponding to the item in ListBox2 is copied and pasted in another workbook when I click the item - Sorta Good. The row that is copied and pasted is the row that happens to be highlighted in Sheet1:banghead: . I tried to acivate Sheet2, but then I get a End IF without Block If error.
4. I would like to add all corresponding rows that are listed in ListBox2 with the "Upload ALL" command button, but you can forget about it if I can't get past #3.

Any help would be greatly appreciated. Code is below.

Private Sub CommandButton1_Click()
Dim i As Long, msg As String

With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
End If
Next i
End With

If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else

ListBox2.AddItem msg

End If

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
End Sub
Private Sub ListBox1_Click()
End Sub

Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
'select the data and copy to new worksheet
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
With ws
Cells.Find(What:=msg, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:=False, SearchFormat:=False).Select
irow = ActiveCell.Row
Rows(irow).Select
Selection.Copy
Workbooks.Open Filename:="K:\qc database\trial database.xls"
'Find last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
'Paste data
Windows("trial database.xls").Activate
ActiveSheet.Paste
End If
Next i
'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False


End With
End Sub

Private Sub UserForm_Click()
End Sub

Private Sub UserForm_Initialize()
' populates from Sheet2

Dim varlist As Variant
Dim lngrow As Long

Lrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Plist = Sheets("Sheet2").Range("A2:C" & Lrow)
ListBox1.List = Plist

End Sub

mikerickson
05-13-2010, 10:16 PM
The With ws needs an End With.
Technicly, the With ws shouldn't be used, since it is inside, but not modifying, the With ListBox2.


Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
'select the data and copy to new worksheet
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
With ws
Cells.Find(What:=msg, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:=False, SearchFormat:=False).Select
irow = ActiveCell.Row
Rows(irow).Select
Selection.Copy
Workbooks.Open Filename:="K:\qc database\trial database.xls"
'Find last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
'Paste data
Windows("trial database.xls").Activate
ActiveSheet.Paste
End With
End If
Next i
'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False

End With
End Sub

sgr
05-13-2010, 10:45 PM
Thanks, mikerickson.

I had it that way before, but it would not pull the data off of sheet2. How can i copy the data from Sheet2? I thought I would have to activate Sheet2 and copy corresponding rows? Maybe just a reference to Sheet2?:dunno
</IMG>

Bob Phillips
05-14-2010, 03:16 AM
Untested



Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Dim wbTrial As Workbook
Dim wsTrial As Worksheet
Dim ws As Worksheet
Dim cell As Range

Set ws = Worksheets("Sheet2")
Set wbTrial = Workbooks.Open(Filename:="K:\qc database\trial database.xls")
Set wsTrial = wbTrial.Worksheets(1)
Application.ScreenUpdating = False

'select the data and copy to new worksheet
With ListBox2

For i = 0 To .ListCount - 1

If .Selected(i) Then

msg = msg & .List(i)
With ws

Set cell = .Cells.Find(What:=msg, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
'Find last row of data
NextRow = wsTrial.Cells(wsTrial.Rows.Count, "A").End(xlUp).Row + 1
cell.EntireRow.Copy wsTrial.Cells(NextRow, "A")
End With
End If
Next i

'Closes trial database
wbTrial.Close True

Set wbTrial = Nothing
Set wsTrial = Nothing
Set ws = Nothing

Application.CutCopyMode = False
End With
End Sub

sgr
05-14-2010, 07:05 AM
Thanks again, xld.

The macro pulls from the correct sheet, but not the data that corresponds to the item in the listbox. I've attached the workbook. Sheet3 is what I would like the database to look like if items 1, 3 and 7 are selected. I wish I could copy from Sheet1, but I need to rearrange and filter the data on Sheet1 before I can paste it into the database.

Thanks for your time.