PDA

View Full Version : Extract data problem in the sheet



Ann_BBO
08-07-2007, 03:46 AM
Click the ?Input File? button to input the standard files in the listbox, say 3 files. Then, click the ?Data Analysis? button to extract the data from the 3 files into the third blank form in 1 sheet. It is the first time to analysis data.
After that, If i add the new 4 files in the listbox without closing the UserForm. Click the "Data Analysis can be extract the data of 4 files in the 4th-7th blank form in same sheet. (This part i had success to do it).
But if i click the "Delete Button" to delete the files in listbox before, then adding the 4 new files in listbox. It will only do the last file of this 4 files into the 4 th blank form.
My modified vba is shown below:
Private Sub cmdInput_Click()
.
.
Range("J8").Value = ListBox1.ListCount
End Sub

Private Sub cmd_Delete_Click()
.
.
Range("J8").Value = ListBox1.ListCount
Range("k8").Value = cmd_Delete.TakeFocusOnClick
End Sub

Private Sub cmdShowdata_Click()
Dim Tgt As Worksheet ' The name of the active.sheet
Dim Source As Range ' The name of the Filepaths, sheets, column in the source.workbook
Dim wbSource As Workbook ' The name of the source.workbooks
Dim cel As Range ' The individual cell of the active.sheet
Dim Rng As Range ' The range of the source.workbooks
Dim c As Range ' The target range of the source.workbooks
Dim x As Long ' The variable of the listBox1 sequence
Dim y As Integer ' The variable of the range distance between the Data range
Dim z As Integer
If Range("H8").Value = True Then

If Range("H8").Value = True And Range("k8").Value = True Then
z = 0
End If

If Range("J8").Value > Range("I8").Value Then
z = Range("I8").Value
End If

Else
z = 0
End If
For x = z To ListBox1.ListCount - 1
Application.ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open(Filename:=ListBox1.List(x))
Set Source = wbSource.Sheets(1).Columns(1)

If Range("k8").Value = True Then
y = (x + Range("I8").Value) * 38
End If

y = x * 38

' Show the filepath(s) according the ListBox1 sequence
Tgt.Cells((y + 49), 3).Value = Me.ListBox1.List(x)
.
.
Next x

Range("H8").Value = cmdShowdata.TakeFocusOnClick
Range("I8").Value = ListBox1.ListCount
End Sub

I suspect the problem occur in this part but i don't know why cannot do that.
y = (x + Range("I8").Value) * 38

Can anyone help me?:mkay
Thanks