acerlaptop
New Member
- Joined
- Feb 17, 2020
- Messages
- 44
- Office Version
- 2013
- Platform
- Windows
Hi All,
I have a file with macro in it. There are two different macros in it but I want to merge them using "Call". Both macros are working when run separately, but if I merge them, one of them stops with an error.
The firsts macro is named Sub RAW_DATA(). This is where I want to merge the second macro which is Sub LE0872_SUM_UPDATE(). The error comes from the second macro when I merge it with the first macro.
I have a file with macro in it. There are two different macros in it but I want to merge them using "Call". Both macros are working when run separately, but if I merge them, one of them stops with an error.
VBA Code:
Dim r As Long
For r = hlpR To 1 Step -1
If Not Sheets("0872 Summary").Cells(r, lstCs + 1) = isBlank And _
Not Sheets("0872 Summary").Cells(r, lstCs + 1) = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "00") Then
Rows(r).Delete 'with error when merged with the other Macro
End If
Next r
The firsts macro is named Sub RAW_DATA(). This is where I want to merge the second macro which is Sub LE0872_SUM_UPDATE(). The error comes from the second macro when I merge it with the first macro.
VBA Code:
Sub RAW_DATA()
Application.ScreenUpdating = False
Call LE0872_SUM_UPDATE
Dim aCbs As Integer
Dim aRbs As Integer
Dim aCpl As Integer
Dim aRpl As Integer
Dim LRbooking As Integer
Dim LRraw As Integer
Dim lRbs As Integer
Dim lRpl As Integer
Sheets("Booking (RAW)").Activate
Range("A2:I2").Select
Selection.ClearContents
Rows(3 & ":" & Worksheets("Booking (RAW)").Rows.Count).Delete
'========== BS [start] ==========
Sheets("Booking").Activate
Range("E2").Select
LRbooking = Worksheets("Booking").Cells(Rows.Count, 1).End(xlUp).Row
Do While True
aRbs = ActiveCell.Row
aCbs = ActiveCell.Column
If Cells(1, aCbs).Value = "Total" Then
Cells(Rows.Count, aCbs).End(xlUp).Select
lRbs = lRbs + 1
Exit Do
Else
If aRbs > LRbooking Then
Cells(2, aCbs + 1).Select
Else
If Selection.Value = 0 Or Selection.Value = "" Or Selection.Offset(0, -(aCbs - 2)).Value = "" Then
Cells(aRbs + 1, aCbs).Select
Else
lRbs = Worksheets("Booking (RAW)").Cells(Rows.Count, 7).End(xlUp).Row
Worksheets("Booking (RAW)").Range("A" & lRbs + 1) = Selection.Offset(0, -(aCbs - 1))
Worksheets("Booking (RAW)").Range("B" & lRbs + 1) = Selection.Offset(0, -(aCbs - 2))
Worksheets("Booking (RAW)").Range("C" & lRbs + 1) = Selection.Offset(0, -(aCbs - 3))
Worksheets("Booking (RAW)").Range("D" & lRbs + 1) = Selection.Offset(0, -(aCbs - 4))
Worksheets("Booking (RAW)").Range("E" & lRbs + 1) = Selection.Offset(-(aRbs - 1), 0)
Worksheets("Booking (RAW)").Range("F" & lRbs + 1).FormulaR1C1 = "=LEFT(RC5,4)"
Worksheets("Booking (RAW)").Range("F" & lRbs + 1).Value = Worksheets("Booking (RAW)").Range("F" & lRbs + 1).Value
Worksheets("Booking (RAW)").Range("G" & lRbs + 1) = Selection
Worksheets("Booking (RAW)").Range("H" & lRbs + 1).FormulaR1C1 = "=IF(RC7>0,""DR"",""CR"")"
Worksheets("Booking (RAW)").Range("H" & lRbs + 1).Value = Worksheets("Booking (RAW)").Range("H" & lRbs + 1).Value
Worksheets("Booking (RAW)").Range("I" & lRbs + 1).FormulaR1C1 = "=ROUND(ABS(RC7),2)"
Worksheets("Booking (RAW)").Range("I" & lRbs + 1).Value = Worksheets("Booking (RAW)").Range("I" & lRbs + 1).Value
Cells(aRbs + 1, aCbs).Select
End If
End If
End If
Loop
'========== BS [end] ==========
'========== PL [start] ==========
Sheets("Booking").Activate
Range("E2").Select
Do While True
aRpl = ActiveCell.Row
aCpl = ActiveCell.Column
If Cells(1, aCpl).Value = "Total" Then
Cells(Rows.Count, aCpl).End(xlUp).Select
lRpl = lRpl + 1
Exit Do
Else
If aRpl > LRbooking Then
Cells(2, aCpl + 1).Select
Else
If Selection.Value = 0 Or Selection.Value = "" Or Selection.Offset(0, -(aCpl - 2)).Value = "" Then
Cells(aRpl + 1, aCpl).Select
Else
lRpl = Worksheets("Booking (RAW)").Cells(Rows.Count, 7).End(xlUp).Row
Worksheets("Booking (RAW)").Range("A" & lRpl + 1) = Selection.Offset(0, -(aCpl - 3))
Worksheets("Booking (RAW)").Range("B" & lRpl + 1) = Selection.Offset(0, -(aCpl - 4))
Worksheets("Booking (RAW)").Range("C" & lRpl + 1) = Selection.Offset(0, -(aCpl - 1))
Worksheets("Booking (RAW)").Range("D" & lRpl + 1) = Selection.Offset(0, -(aCpl - 2))
Worksheets("Booking (RAW)").Range("E" & lRpl + 1) = Selection.Offset(-(aRpl - 1), 0)
Worksheets("Booking (RAW)").Range("F" & lRpl + 1).FormulaR1C1 = "=LEFT(RC5,4)"
Worksheets("Booking (RAW)").Range("F" & lRpl + 1).Value = Worksheets("Booking (RAW)").Range("F" & lRpl + 1).Value
Worksheets("Booking (RAW)").Range("G" & lRpl + 1) = Selection * -1
Worksheets("Booking (RAW)").Range("H" & lRpl + 1).FormulaR1C1 = "=IF(RC7>0,""DR"",""CR"")"
Worksheets("Booking (RAW)").Range("H" & lRpl + 1).Value = Worksheets("Booking (RAW)").Range("H" & lRpl + 1).Value
Worksheets("Booking (RAW)").Range("I" & lRpl + 1).FormulaR1C1 = "=ROUND(ABS(RC7),2)"
Worksheets("Booking (RAW)").Range("I" & lRpl + 1).Value = Worksheets("Booking (RAW)").Range("I" & lRpl + 1).Value
Cells(aRpl + 1, aCpl).Select
End If
End If
End If
Loop
'========== PL [end] ==========
Sheets("Booking (RAW)").Activate
LRraw = Worksheets("Booking (RAW)").Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:I2").Copy
Range("A2:I" & LRraw).PasteSpecial xlPasteFormats
Sheets("GENERATOR").Activate
MsgBox "Data conversion SUCCESSFUL!", vbOKOnly + vbInformation
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub
VBA Code:
Sub LE0872_SUM_UPDATE()
Application.ScreenUpdating = False
Dim actC As Integer
Dim actR As Integer
Dim hlpR As Integer
Dim lstCs As Integer
lstCs = Sheets("0872 Summary").Cells(1, Columns.Count).End(xlToLeft).Column
hlpR = Sheets("0872 Summary").Cells(Rows.Count, lstCs + 1).End(xlUp).Row
If hlpR = 1 Then
Call MAIN_UPDATE
Sheets("0872 Summary").Activate
Range("A1").Select
Sheets("GENERATOR").Activate
Application.ScreenUpdating = True
ActiveWorkbook.Save
Else
If Not Sheets("0872 Summary").Cells(hlpR, lstCs + 1) = isBlank And _
Sheets("0872 Summary").Cells(hlpR, lstCs + 1) = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "00") Then
Sheets("0872 Summary").Activate
Range("A1").Select
Sheets("GENERATOR").Activate
Application.ScreenUpdating = True
ActiveWorkbook.Save
Else
Dim r As Long
For r = hlpR To 1 Step -1
If Not Sheets("0872 Summary").Cells(r, lstCs + 1) = isBlank And _
Not Sheets("0872 Summary").Cells(r, lstCs + 1) = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "00") Then
Rows(r).Delete
End If
Next r
Call MAIN_UPDATE
Sheets("0872 Summary").Activate
Range("A1").Select
Sheets("GENERATOR").Activate
Application.ScreenUpdating = True
ActiveWorkbook.Save
End If
End If
End Sub
Sub MAIN_UPDATE()
Dim actC As Integer
Dim actR As Integer
Dim lstC As Integer
Dim lstR As Integer
Dim hlpC As Integer
lstC = Sheets("Booking").Cells(1, Columns.Count).End(xlToLeft).Column
lstR = 3
hlpC = Sheets("0872 Summary").Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("Booking").Activate
Cells(1, lstC).Select
Do While True
actR = ActiveCell.Row
actC = ActiveCell.Column
If Cells(actR, actC).Value = "" Then
Exit Do
Else
If Selection.Value = 0 Then
Cells(actR + 1, actC).Select
Else
If Selection.Offset(0, -(actC - 2)).Value = "0872" Or Selection.Offset(0, -(actC - 4)).Value = "0872" Then
Rows(actR).Copy
Sheets("0872 Summary").Activate
Rows(lstR).Select
Range("A" & lstR).Activate
Selection.Insert Shift:=xlDown
Cells(lstR, hlpC + 1).Value = WorksheetFunction.Text(Month(Sheets("GENERATOR").Range("D3")), "'00")
Sheets("Booking").Activate
Cells(actR + 1, actC).Select
Else
Cells(actR + 1, actC).Select
End If
End If
End If
Loop
End Sub