PDA

View Full Version : Solved: Join Two Codes Together



BENSON
05-03-2008, 10:48 PM
The two codes below work fine ,I am having trouble joining them to show two consecutive message boxes.I wish to join them into one piece of code,with the end result I get two message boxes appear onr after the other should a critera be met
Thanks for any help:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181

If i > 129 And i < 143 Then i = 143

If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.4 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i

If msg <> "" Then

msg = "YOU MAY WANT TO CHECK THE BUILD TO ON THESE ITEMS" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181

If i > 129 And i < 143 Then i = 143

If IsNumeric(.Cells(i, "n").Value) And IsNumeric(.Cells(i, "x").Value) Then

If .Cells(i, "n").Value = .Cells(i, "x").Value * 1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i

If msg <> "" Then

msg = "THE ITEMS LISTED ARE NOT RECORDED ON STOCK SHEET" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With
End Sub

Bob Phillips
05-04-2008, 01:15 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
Dim msg2 As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181

If i > 129 And i < 143 Then i = 143

If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.4 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
If .Cells(i, "n").Value = .Cells(i, "x").Value * 1 Then

msg2 = msg2 & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
End If
Next i

If msg <> "" Then

MsgBox = "YOU MAY WANT TO CHECK THE BUILD TO ON THESE ITEMS" & vbNewLine & vbNewLine & msg
End If
If msg2 <> "" Then

Msgbox = "THE ITEMS LISTED ARE NOT RECORDED ON STOCK SHEET" & vbNewLine & vbNewLine & msg2
End If
End With
End Sub

BENSON
05-04-2008, 10:27 PM
Thanks for the reply xld, but get a error message " Compile error End If Without Block If" I have highligted it in the code below.If I remove the "End If" highlighted, I get the error Message " Compile error Function call on left-hand side of assignment must return varient or object"
Thanks


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
Dim msg2 As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181

If i > 129 And i < 143 Then i = 143

If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.4 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
If .Cells(i, "n").Value = .Cells(i, "x").Value * 1 Then

msg2 = msg2 & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
End If

Next i

If msg <> "" Then

MsgBox = "YOU MAY WANT TO CHECK THE BUILD TO ON THESE ITEMS" & vbNewLine & vbNewLine & msg
End If
If msg2 <> "" Then

MsgBox = "THE ITEMS LISTED ARE NOT RECORDED ON STOCK SHEET" & vbNewLine & vbNewLine & msg2
End If
End With
End Sub

mdmackillop
05-04-2008, 11:52 PM
Remove End If as noted
Remove = from both MsgBox =

Bob Phillips
05-05-2008, 01:53 AM
BENSON,

Sorry about my typos, I just cut the code together, failed to test it. But ... when you get something like that, just experiment. Try deleting it and see what happens, it is what we do when developing. You'll learn more that way too.

BENSON
05-05-2008, 10:38 PM
Many Thanks works fine now