Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only from a cell with data in it
Here is my existing code if it helps any Joe Dim TestCalcs_rev22 As Workbook Dim Bondsdb As Workbook Dim shtinitial_information As Worksheet Dim shtsheet1 As Worksheet Dim cl As Integer Private Sub UserForm_Activate() With Workbooks("BondsDB.xls").Worksheets("sheet1") For cl = 2 To 5000 If .Cells(cl, 4) = "" Then Exit For Next End With cl = cl - 1 progListBox.RowSource = "[BondsDB.xls]sheet1!$A$2:$IF$" & Mid(Str(cl) 2) End Sub Private Sub btnOpen_Click() If progListBox.ListIndex = -1 Then Beep MsgBox "No Reference Selected!", vbExclamation, "" Exit Sub End If Set rng = Range(progListBox.RowSource).Columns(1).Cells cl = rng.Offset(progListBox.ListIndex, 0).Row With Workbooks("bondsdb.xls").Worksheets("sheet1") Load: 'Range("Issr") = .Cells(cl, 1) 'Range("Issr_local") = .Cells(cl, 2) 'Range("Principal") = .Cells(cl, 4) 'Range("Term") = .Cells(cl, 7) .Columns("AB:CI").Rows(cl).Copy Workbooks("TestCalcs_rev22.xls").Worksheets("initi al_information").Range("E30") _ PasteSpecial xlPasteAll, Transpose:=True '.Columns("Y:CF").Rows(cl).Copy 'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range(""). 'PasteSpecial xlPasteAll, Transpose:=True '.Columns("EE:HZ").Rows(cl).Copy 'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range("") _ 'PasteSpecial xlPasteAll, Transpose:=True End With Hide End Sub Private Sub btnCancel_Click() Hide End Su -- Joe ----------------------------------------------------------------------- JoeH's Profile: http://www.excelforum.com/member.php...fo&userid=1413 View this thread: http://www.excelforum.com/showthread.php?threadid=26267 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only from a cell with data in it
What about the code leaves you wanting? I see you are using "If
..Cells(cl, 4) = ""." Is that not working for you? The way I check for a null string is: If Len(.Cells(cl, 4).Text) < 1 Then ... See the SpecialCells method of the Range class. It allows you get blank cells, en masse. For example: Dim r as Range Set r = Columns("A:B").SpecialCells(xlCellTypeBlanks) Also See IsEmpty(), and the other 'Is' functions, perhaps. Sometimes it is useful to sort a range to remove the empties. JoeH wrote: Here is my existing code if it helps any Joe Dim TestCalcs_rev22 As Workbook Dim Bondsdb As Workbook Dim shtinitial_information As Worksheet Dim shtsheet1 As Worksheet Dim cl As Integer Private Sub UserForm_Activate() With Workbooks("BondsDB.xls").Worksheets("sheet1") For cl = 2 To 5000 If .Cells(cl, 4) = "" Then Exit For Next End With cl = cl - 1 progListBox.RowSource = "[BondsDB.xls]sheet1!$A$2:$IF$" & Mid(Str(cl), 2) End Sub Private Sub btnOpen_Click() If progListBox.ListIndex = -1 Then Beep MsgBox "No Reference Selected!", vbExclamation, "" Exit Sub End If Set rng = Range(progListBox.RowSource).Columns(1).Cells cl = rng.Offset(progListBox.ListIndex, 0).Row With Workbooks("bondsdb.xls").Worksheets("sheet1") Load: 'Range("Issr") = .Cells(cl, 1) 'Range("Issr_local") = .Cells(cl, 2) 'Range("Principal") = .Cells(cl, 4) 'Range("Term") = .Cells(cl, 7) Columns("AB:CI").Rows(cl).Copy Workbooks("TestCalcs_rev22.xls").Worksheets("initi al_information").Range("E30"). _ PasteSpecial xlPasteAll, Transpose:=True '.Columns("Y:CF").Rows(cl).Copy 'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range(""). 'PasteSpecial xlPasteAll, Transpose:=True '.Columns("EE:HZ").Rows(cl).Copy 'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range(""). _ 'PasteSpecial xlPasteAll, Transpose:=True End With Hide End Sub Private Sub btnCancel_Click() Hide End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy last cell with data in column E on one sheet to cell on anoth | Excel Worksheet Functions | |||
How do I copy data from main frame computer and keep data in cell | Excel Worksheet Functions | |||
Copy data in one cell to blank cell immediately below, repeat | Excel Worksheet Functions | |||
Data entry - Copy contents of cell typed in one cell to another ce | Excel Worksheet Functions | |||
How do I copy data from one cell to the next occurrence of data? | Excel Worksheet Functions |