| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help On My codes
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help On My codes
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help On My codes
Hi Bob,
Thanks for the rescue.. but how abt the Work book opening part and also the active windows closing part?? "Bob Phillips" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Zip Codes | New Users to Excel | |||
| Codes | Excel Discussion (Misc queries) | |||
| Need some help with codes | Excel Discussion (Misc queries) | |||
| Codes | Excel Worksheet Functions | |||
| VBA Codes | Excel Worksheet Functions | |||
Hybrid Mode
