Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
End sub routine, move to next
I am new to VBA, but have been able to do some automation in a workbook
partially by recording a macro, by research, and a few questions to this website. Almost finished, but I need a little more help. On a worksheet in my workbook, I have a variable range, which, once found, I have a total put into Column L. The range is then copied and pasted into another workbook. However, if the total in Column L is 0.00, I need to skip the rest of the routine and move to the next sub routine called CopyCosts. Following is part of the sub routine I am working with: Range("H1:h45").Select Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select ActiveCell.Offset(-1, 0).Select ActiveCell.Value = Application.Sum([d2:d46]) Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Sheets("Input").Select Workbooks.Open Filename:="G:\" & Range("A1").Value & "\BD" What statement would be used so that if Application.Sum([d2:d46]) = 0.00, end, then go to next sub? Thanks -- Linda |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
End sub routine, move to next
Linda,
Use the IF command: If Application.Sum("D2:D46") = 0 Then call CopyCosts End Else ' Do something End if HTH, Matthew Pfluger "mathel" wrote: I am new to VBA, but have been able to do some automation in a workbook partially by recording a macro, by research, and a few questions to this website. Almost finished, but I need a little more help. On a worksheet in my workbook, I have a variable range, which, once found, I have a total put into Column L. The range is then copied and pasted into another workbook. However, if the total in Column L is 0.00, I need to skip the rest of the routine and move to the next sub routine called CopyCosts. Following is part of the sub routine I am working with: Range("H1:h45").Select Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select ActiveCell.Offset(-1, 0).Select ActiveCell.Value = Application.Sum([d2:d46]) Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Sheets("Input").Select Workbooks.Open Filename:="G:\" & Range("A1").Value & "\BD" What statement would be used so that if Application.Sum([d2:d46]) = 0.00, end, then go to next sub? Thanks -- Linda |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
End sub routine, move to next
I've tried what you wrote below. I'm getting the following error code:
"Run-time error '13' Type mismatch The VB Editor is highlighting: If Application.Sum ("d2:d46") = 0 Then Any ideas? -- Linda "Matthew Pfluger" wrote: Linda, Use the IF command: If Application.Sum("D2:D46") = 0 Then call CopyCosts End Else ' Do something End if HTH, Matthew Pfluger "mathel" wrote: I am new to VBA, but have been able to do some automation in a workbook partially by recording a macro, by research, and a few questions to this website. Almost finished, but I need a little more help. On a worksheet in my workbook, I have a variable range, which, once found, I have a total put into Column L. The range is then copied and pasted into another workbook. However, if the total in Column L is 0.00, I need to skip the rest of the routine and move to the next sub routine called CopyCosts. Following is part of the sub routine I am working with: Range("H1:h45").Select Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select ActiveCell.Offset(-1, 0).Select ActiveCell.Value = Application.Sum([d2:d46]) Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Sheets("Input").Select Workbooks.Open Filename:="G:\" & Range("A1").Value & "\BD" What statement would be used so that if Application.Sum([d2:d46]) = 0.00, end, then go to next sub? Thanks -- Linda |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
End sub routine, move to next
If Application.Sum(worksheets("worksheetnamehere").ra nge("D2:D46")) = 0 Then
mathel wrote: I've tried what you wrote below. I'm getting the following error code: "Run-time error '13' Type mismatch The VB Editor is highlighting: If Application.Sum ("d2:d46") = 0 Then Any ideas? -- Linda "Matthew Pfluger" wrote: Linda, Use the IF command: If Application.Sum("D2:D46") = 0 Then call CopyCosts End Else ' Do something End if HTH, Matthew Pfluger "mathel" wrote: I am new to VBA, but have been able to do some automation in a workbook partially by recording a macro, by research, and a few questions to this website. Almost finished, but I need a little more help. On a worksheet in my workbook, I have a variable range, which, once found, I have a total put into Column L. The range is then copied and pasted into another workbook. However, if the total in Column L is 0.00, I need to skip the rest of the routine and move to the next sub routine called CopyCosts. Following is part of the sub routine I am working with: Range("H1:h45").Select Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select ActiveCell.Offset(-1, 0).Select ActiveCell.Value = Application.Sum([d2:d46]) Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Sheets("Input").Select Workbooks.Open Filename:="G:\" & Range("A1").Value & "\BD" What statement would be used so that if Application.Sum([d2:d46]) = 0.00, end, then go to next sub? Thanks -- Linda -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sub routine | Excel Discussion (Misc queries) | |||
VBA routine (101) | Excel Programming | |||
SUB ROUTINE | Excel Programming | |||
Routine?? | Excel Programming | |||
Need VBA Routine | Excel Programming |