Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > oh dear - why isn't my simple IF working?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default oh dear - why isn't my simple IF working?

It's an order form.

If someone saves it as a "draft", cell A101 has a "1" value in.

Now, my macro below is supposed to:

Save as a new name, email it, and IF A101 0, then delete the ol
sheet.


Code
-------------------
Sub Save_Click()
Dim lStr_CurFileName As String
Dim ws1 As Worksheet
Dim wb1 As Workbook

Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")


Range("T8").Value = Date
Application.DisplayAlerts = False
On Error Resume Next
myarray = Array("Completed Orders", "Partially Arrived Orders", "Draft Orders", "Placed Orders")
Sheets(myarray).Delete
ThisFile = Range("T3").Value
ThisDept = Range("S3").Value
ActiveWorkbook.SaveAs Filename:="J:\Purchase Orders\FM\Order " & ThisDept & ThisFile
Dim NextNo

If Draft 0 Then
Kill lStr_CurFileName
End If


InvNo = ws1.Range("A100")
NextNo = 1
Range("A100").Select

ActiveCell.Formula = InvNo + NextNo
Range("A1").Select
ActiveWorkbook.Save
With ActiveWorkbook
.SendMail ", _
Subject:="Purchase Order " & Format(Date, "dd/mmm/yy")
Application.DisplayAlerts = True
End With

Range("C46:F47").Select
Selection.ClearContents
ActiveSheet.Shapes("Picture 10").Select
Selection.Delete
ActiveSheet.Shapes("Picture 105").Select
Selection.Delete
Range("E45:F48").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveSheet.Shapes("Picture 106").Select
Selection.ShapeRange.IncrementLeft -1
Selection.ShapeRange.IncrementTop -530
Range("C46").Select
ActiveCell.FormulaR1C1 = "Save Delivery"
Range("C47").Select
ActiveCell.FormulaR1C1 = "Information"
Range("C46:C47").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With




Range("A1").Select
Sheets("Sheet1").Activate
End Sub



-------------------

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52387

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,058
Default oh dear - why isn't my simple IF working?
1. try setting Draft=ws1.Range("A101").Value
2. MSGBOX(Draft) just before the kill to make sure it has been set
--
Gary's Student


"drucey" wrote:


It's an order form.

If someone saves it as a "draft", cell A101 has a "1" value in.

Now, my macro below is supposed to:

Save as a new name, email it, and IF A101 0, then delete the old
sheet.


Code:
--------------------
Sub Save_Click()
Dim lStr_CurFileName As String
Dim ws1 As Worksheet
Dim wb1 As Workbook

Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")


Range("T8").Value = Date
Application.DisplayAlerts = False
On Error Resume Next
myarray = Array("Completed Orders", "Partially Arrived Orders", "Draft Orders", "Placed Orders")
Sheets(myarray).Delete
ThisFile = Range("T3").Value
ThisDept = Range("S3").Value
ActiveWorkbook.SaveAs Filename:="J:\Purchase Orders\FM\Order " & ThisDept & ThisFile
Dim NextNo

If Draft 0 Then
Kill lStr_CurFileName
End If


InvNo = ws1.Range("A100")
NextNo = 1
Range("A100").Select

ActiveCell.Formula = InvNo + NextNo
Range("A1").Select
ActiveWorkbook.Save
With ActiveWorkbook
.SendMail ", _
Subject:="Purchase Order " & Format(Date, "dd/mmm/yy")
Application.DisplayAlerts = True
End With

Range("C46:F47").Select
Selection.ClearContents
ActiveSheet.Shapes("Picture 10").Select
Selection.Delete
ActiveSheet.Shapes("Picture 105").Select
Selection.Delete
Range("E45:F48").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveSheet.Shapes("Picture 106").Select
Selection.ShapeRange.IncrementLeft -1
Selection.ShapeRange.IncrementTop -530
Range("C46").Select
ActiveCell.FormulaR1C1 = "Save Delivery"
Range("C47").Select
ActiveCell.FormulaR1C1 = "Information"
Range("C46:C47").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With




Range("A1").Select
Sheets("Sheet1").Activate
End Sub



--------------------


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=523878


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default oh dear - why isn't my simple IF working?
change

Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")



to

lStr_CurFileName = ActiveWorkbook.FullName
Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")

so the kill command knows what to delete.

That would be my guess.

--
Regards,
Tom Ogilvy


"drucey" wrote in
message ...

It's an order form.

If someone saves it as a "draft", cell A101 has a "1" value in.

Now, my macro below is supposed to:

Save as a new name, email it, and IF A101 0, then delete the old
sheet.


Code:
--------------------
Sub Save_Click()
Dim lStr_CurFileName As String
Dim ws1 As Worksheet
Dim wb1 As Workbook

Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")


Range("T8").Value = Date
Application.DisplayAlerts = False
On Error Resume Next
myarray = Array("Completed Orders", "Partially Arrived Orders", "Draft
Orders", "Placed Orders")
Sheets(myarray).Delete
ThisFile = Range("T3").Value
ThisDept = Range("S3").Value
ActiveWorkbook.SaveAs Filename:="J:\Purchase Orders\FM\Order " &
ThisDept & ThisFile
Dim NextNo

If Draft 0 Then
Kill lStr_CurFileName
End If


InvNo = ws1.Range("A100")
NextNo = 1
Range("A100").Select

ActiveCell.Formula = InvNo + NextNo
Range("A1").Select
ActiveWorkbook.Save
With ActiveWorkbook
.SendMail ", _
Subject:="Purchase Order " & Format(Date, "dd/mmm/yy")
Application.DisplayAlerts = True
End With

Range("C46:F47").Select
Selection.ClearContents
ActiveSheet.Shapes("Picture 10").Select
Selection.Delete
ActiveSheet.Shapes("Picture 105").Select
Selection.Delete
Range("E45:F48").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveSheet.Shapes("Picture 106").Select
Selection.ShapeRange.IncrementLeft -1
Selection.ShapeRange.IncrementTop -530
Range("C46").Select
ActiveCell.FormulaR1C1 = "Save Delivery"
Range("C47").Select
ActiveCell.FormulaR1C1 = "Information"
Range("C46:C47").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With




Range("A1").Select
Sheets("Sheet1").Activate
End Sub



--------------------


--
drucey
------------------------------------------------------------------------
drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=523878



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default oh dear - why isn't my simple IF working?

thanks chap

but still not working - it comes up with the prompt box (after th
draft setting) but it's a blank box :

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52387

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default oh dear - why isn't my simple IF working?

oooh second answer worked fine! thank you so much!


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=523878

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dear Excel Discussion Group Corcovadocowboy Excel Discussion (Misc queries) 4 September 6th 08 12:25 PM
dear [email protected] New Users to Excel 0 May 12th 08 09:28 AM
Simple formula NOT working :( Danny Lewis Excel Discussion (Misc queries) 3 August 1st 06 10:33 AM
This should be simple but it's not working for me! hellokitty New Users to Excel 4 April 12th 05 01:26 AM
To dear Tom Ogilvy, Ron de Bruin, Frank Kabel, Bob Phillips...and other experts J_J Excel Programming 0 October 28th 04 12:25 PM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement