K
KK
Private Sub cbFile_Click()
'returns your full file name.
File_Name = Application.GetOpenFilename("MS Excel Files (*.xls),*.xls")
'hence no len, no name...
If Len(File_Name) = 0 Then Exit Sub
tbFile.value = File_Name
End Sub
Private Sub cbStart_Click()
Workbooks.Open (tbFile)
Dim x1 As Integer
Dim y13 As Integer
Dim value As String
Dim count As String
Set wb = Workbooks.Open(tbFile, True, True)
y1 = 6
For x1 = 2 To 930
Worksheets("Task_Table1").Select
Application.Goto Reference:="R" & x1 & "C20"
ActiveCell.FormulaR1C1 =
"=IF((LEFT(RC[-17],LEN(RC[-17])-4)+1)>11,(IF(RC[-13]<>""Yes"",(IF(RC[-10]<>1,""YES"",""No"")),""No"")),""No"")"
value = ActiveCell.value
If (value <> "No") Then
Workbooks("Project_Analyzer_v2").Activate
' open the source workbook, read only
With ThisWorkbook.Worksheets("Duration Test")
..Range("B1").Formula = "File : " & tbFile.value
..Range("B2").Formula = "Test Completed : " & FormatDateTime(Now())
..Range("A5").Formula = "ID"
..Range("B5").Formula = "Task"
..Range("C5").Formula = "Duration"
..Range("D5").Formula = "Start Date"
..Range("E5").Formula = "Finish Date"
' read data from the source workbook
..Range("A" & y1).Formula = wb.Worksheets("Task_Table1").Range("A" &
x1).Formula
..Range("B" & y1).Formula = wb.Worksheets("Task_Table1").Range("B" &
x1).Formula
..Range("C" & y1).Formula = wb.Worksheets("Task_Table1").Range("C" &
x1).Formula
..Range("D" & y1).Formula = wb.Worksheets("Task_Table1").Range("D" &
x1).Formula
..Range("E" & y1).Formula = wb.Worksheets("Task_Table1").Range("E" &
x1).Formula
End With
y1 = y1 + 1
Else
End If
Next x1
End Sub
This is my coding,I had migrate out the set wb out ,but it run once
then pop out run time error "9" subscript out of range, if a add the
windows("tbFile").activate before the worksheet("Task_Table1") , same the
error , add windows(tbFile).activate then the error "type mismatch", i also
try to change windows(tbFile) to workbooks(tbFile) ,the error "type mismatch"
is display,anyone can help me ?
'returns your full file name.
File_Name = Application.GetOpenFilename("MS Excel Files (*.xls),*.xls")
'hence no len, no name...
If Len(File_Name) = 0 Then Exit Sub
tbFile.value = File_Name
End Sub
Private Sub cbStart_Click()
Workbooks.Open (tbFile)
Dim x1 As Integer
Dim y13 As Integer
Dim value As String
Dim count As String
Set wb = Workbooks.Open(tbFile, True, True)
y1 = 6
For x1 = 2 To 930
Worksheets("Task_Table1").Select
Application.Goto Reference:="R" & x1 & "C20"
ActiveCell.FormulaR1C1 =
"=IF((LEFT(RC[-17],LEN(RC[-17])-4)+1)>11,(IF(RC[-13]<>""Yes"",(IF(RC[-10]<>1,""YES"",""No"")),""No"")),""No"")"
value = ActiveCell.value
If (value <> "No") Then
Workbooks("Project_Analyzer_v2").Activate
' open the source workbook, read only
With ThisWorkbook.Worksheets("Duration Test")
..Range("B1").Formula = "File : " & tbFile.value
..Range("B2").Formula = "Test Completed : " & FormatDateTime(Now())
..Range("A5").Formula = "ID"
..Range("B5").Formula = "Task"
..Range("C5").Formula = "Duration"
..Range("D5").Formula = "Start Date"
..Range("E5").Formula = "Finish Date"
' read data from the source workbook
..Range("A" & y1).Formula = wb.Worksheets("Task_Table1").Range("A" &
x1).Formula
..Range("B" & y1).Formula = wb.Worksheets("Task_Table1").Range("B" &
x1).Formula
..Range("C" & y1).Formula = wb.Worksheets("Task_Table1").Range("C" &
x1).Formula
..Range("D" & y1).Formula = wb.Worksheets("Task_Table1").Range("D" &
x1).Formula
..Range("E" & y1).Formula = wb.Worksheets("Task_Table1").Range("E" &
x1).Formula
End With
y1 = y1 + 1
Else
End If
Next x1
End Sub
This is my coding,I had migrate out the set wb out ,but it run once
then pop out run time error "9" subscript out of range, if a add the
windows("tbFile").activate before the worksheet("Task_Table1") , same the
error , add windows(tbFile).activate then the error "type mismatch", i also
try to change windows(tbFile) to workbooks(tbFile) ,the error "type mismatch"
is display,anyone can help me ?