Need Help On My codes

G

Guest

Hi
I have a marco written below, this is to find and open a file name with
"...H73FJ..."
My question is ... what or how should I add or write codes to my existing
macro below if I want to find a file name with "...H72FJ...." and if it is
not found .. then continue to find file name with "...H73FJ..." and if both
not found then message " No Production............" will appear. If anyone of
the files is found then continue macro. And then at the end I would like to
find the active file and closed it without any save changes...

Pls help ..... Thanks....


Private Sub V3DVH1stPassYield_Click()

'V3DVH1stPassYield_Click
'Recorded and Modified on 16 Aug 2005

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A4:L2500").ClearContents
If Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then
Range("L2505") = "No Production Build or No Datas Found. PLS VERIFY"
Range("L2505").Select
Selection.Interior.ColorIndex = 45
Else
Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") &
".csv"
Application.Run "V3_NewDeltaVH_Sort"
ActiveSheet.Range("A2:L2498").Select
Selection.Copy
Workbooks("DVH Template C Macro V3.xls").Activate
ActiveSheet.Name = "DVH V3 1st Pass"
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Windows("H73FJ" & Range("J2516") & ".csv").Activate
Application.CutCopyMode = False
ActiveWindow.Close SaveChanges:=False
'ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A2501").Select
End If

End Sub
 
B

Bob Phillips

Private Sub V3DVH1stPassYield_Click()

'V3DVH1stPassYield_Click
'Recorded and Modified on 16 Aug 2005

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A4:L2500").ClearContents
If Dir$(Range("I2513") & "H72FJ" & Range("J2516") & ".csv") = "" Or _
Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then
With Range("L2505")
.Value = "No Production Build or No Datas Found. PLS VERIFY"
.Interior.ColorIndex = 45
End With
Else
Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") &
".csv"
Application.Run "V3_NewDeltaVH_Sort"
ActiveSheet.Range("A2:L2498").Copy
Workbooks("DVH Template C Macro V3.xls").Activate
ActiveSheet.Name = "DVH V3 1st Pass"
Range("A4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Windows("H73FJ" & Range("J2516") & ".csv").Activate
Application.CutCopyMode = False
ActiveWindow.Close SaveChanges:=False
'ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]"
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A2501").Select

End Sub


--
HTH

Bob Phillips

ddiicc said:
Hi
I have a marco written below, this is to find and open a file name with
"...H73FJ..."
My question is ... what or how should I add or write codes to my existing
macro below if I want to find a file name with "...H72FJ...." and if it is
not found .. then continue to find file name with "...H73FJ..." and if both
not found then message " No Production............" will appear. If anyone of
the files is found then continue macro. And then at the end I would like to
find the active file and closed it without any save changes...

Pls help ..... Thanks....


Private Sub V3DVH1stPassYield_Click()

'V3DVH1stPassYield_Click
'Recorded and Modified on 16 Aug 2005

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A4:L2500").ClearContents
If Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then
Range("L2505") = "No Production Build or No Datas Found. PLS VERIFY"
Range("L2505").Select
Selection.Interior.ColorIndex = 45
Else
Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") &
".csv"
Application.Run "V3_NewDeltaVH_Sort"
ActiveSheet.Range("A2:L2498").Select
Selection.Copy
Workbooks("DVH Template C Macro V3.xls").Activate
ActiveSheet.Name = "DVH V3 1st Pass"
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Windows("H73FJ" & Range("J2516") & ".csv").Activate
Application.CutCopyMode = False
ActiveWindow.Close SaveChanges:=False
'ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A2501").Select
End If

End Sub
Click to expand...
 
G

Guest

Hi Bob,
Thanks for the rescue.. but how abt the Work book opening part and also the
active windows closing part??

Bob Phillips said:
Private Sub V3DVH1stPassYield_Click()

'V3DVH1stPassYield_Click
'Recorded and Modified on 16 Aug 2005

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A4:L2500").ClearContents
If Dir$(Range("I2513") & "H72FJ" & Range("J2516") & ".csv") = "" Or _
Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then
With Range("L2505")
.Value = "No Production Build or No Datas Found. PLS VERIFY"
.Interior.ColorIndex = 45
End With
Else
Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") &
".csv"
Application.Run "V3_NewDeltaVH_Sort"
ActiveSheet.Range("A2:L2498").Copy
Workbooks("DVH Template C Macro V3.xls").Activate
ActiveSheet.Name = "DVH V3 1st Pass"
Range("A4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Windows("H73FJ" & Range("J2516") & ".csv").Activate
Application.CutCopyMode = False
ActiveWindow.Close SaveChanges:=False
'ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]"
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A2501").Select

End Sub


--
HTH

Bob Phillips

ddiicc said:
Hi
I have a marco written below, this is to find and open a file name with
"...H73FJ..."
My question is ... what or how should I add or write codes to my existing
macro below if I want to find a file name with "...H72FJ...." and if it is
not found .. then continue to find file name with "...H73FJ..." and if both
not found then message " No Production............" will appear. If anyone of
the files is found then continue macro. And then at the end I would like to
find the active file and closed it without any save changes...

Pls help ..... Thanks....


Private Sub V3DVH1stPassYield_Click()

'V3DVH1stPassYield_Click
'Recorded and Modified on 16 Aug 2005

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A4:L2500").ClearContents
If Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then
Range("L2505") = "No Production Build or No Datas Found. PLS VERIFY"
Range("L2505").Select
Selection.Interior.ColorIndex = 45
Else
Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") &
".csv"
Application.Run "V3_NewDeltaVH_Sort"
ActiveSheet.Range("A2:L2498").Select
Selection.Copy
Workbooks("DVH Template C Macro V3.xls").Activate
ActiveSheet.Name = "DVH V3 1st Pass"
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Windows("H73FJ" & Range("J2516") & ".csv").Activate
Application.CutCopyMode = False
ActiveWindow.Close SaveChanges:=False
'ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A2501").Select
End If

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

re : Help Need on my codes 2
Macro not working properly on change in cell value 2
Macro not running properly on change in cell value 2
find an array, only finds first entry 4
Problem with Macro in MAC 3
NEED HELP!!!! 1
poor macro performance 2
help to adjust my code 2

Top