why would my macro stop?

[orsm6]

hi everyone, i got this workbook from a colleague. It runs a SAP transaction, exports the data, then brings the data into individual tabs in excel. The macro is designed to loop through a list of numbers, so if my column had 10 numbers, i'd end up with 10 individual sheets in my workbook.

the code works perfectly fine for the first number in the column, but when it processes the next number in the list the macro fails here and i cannot for the life of me work out why. the code is massive, so i have just posted the bit it breaks at.

'Find pack material totals cell addresses
  If Cells(i, 8).Value = "Z004" Then
    If Not packMaterialRange Is Nothing Then
        Set packMaterialRange = Union(packMaterialRange, Cells(i, 18))
    Else
        Set packMaterialRange = Cells(i, 18)
    End If
    End If

it specifically stops at the line: Set packMaterialRange = Union(packMaterialRange, Cells(i, 18))

the sheet that it is looking for the Z004 reference is correct, everything is.... just for some reason only on the second pass it will crash.
TIA

 

[bobsan42]

It would help if you post the error number and description.
But my guess is it has something to do with not properly specified ranges. Are you getting error 1004 ?
This may happen if packMaterialRange and Cells(i, 18) are on different sheets.
The first pass goes well because there is no Union of ranges:

Set packMaterialRange = Cells(i, 18)

A general rule is to use as complete references as possible, e.g. ThisWorkbook.Worksheets("Sheet5").Cells(i, 18)

 

[Trebor76]

Hi orsm6,

How are you defining (what variable are you using) for packMaterialRange? When I created a range variable like so at the top of the macro...

Dim packMaterialRange As Range

...it worked for me.

Robert

 

[orsm6]

hi both - here is the entire code. i'm hoping you can read through it lol. to me it looks a little messy, but see how you go.
i have included the sub that copies the text from the exported SAP report.

Hi bobsan42 - the error code and description is: run-time error '1004': Method 'union' of object '_Global' failed

Private Sub CopyTXT(destSht As Worksheet, sourceWbk As Workbook)

Set destinationWorkbook = ActiveWorkbook

Dim lastrow As Integer
Dim lastColumn As Integer

    lastrow = LastRowNumber(sourceWbk.Sheets(1))
    lastColumn = lastColNumber(sourceWbk.Sheets(1))

'copy the data
    sourceWbk.Activate
    sourceWbk.Sheets(1).Range(Cells(1, 1), Cells(lastrow, lastColumn)).Copy

'Open the sheet you're pasting into
    destinationWorkbook.Activate
    destSht.Activate

'Pick where to paste
    lastrow = LastRowNumber(destSht)
    Cells(lastrow + 1, 1).Select
    ActiveSheet.Paste

'clear the clipboard
    Application.CutCopyMode = False

'close the current file
    sourceWbk.Close


End Sub

this is the sub that breaks down....

Sub getCk13N()


'this sub needs to be changed to export tab spaced like COR3
'this will solve problem of having heaps of excel windows open up

Dim materialNumber As String
Dim numberOfMaterials As Integer
Dim materialSheet As Worksheet
Dim currentmaterialTextFile As Workbook
Dim BOMsWbk As Workbook

    Set BOMsWbk = ActiveWorkbook
    Set session = connectSAP

'add summary sheet
    'Sheets.Add
    'ActiveSheet.Name = "Summary"

    Worksheets("MaterialsList").Activate
    numberOfMaterials = Cells(Rows.Count, 1).End(xlUp).Row - 1

    For x = 1 To numberOfMaterials

    materialNumber = Worksheets("MaterialsList").Cells(x + 1, 1).Value
    Application.ScreenUpdating = False

'start Ck13n
    session.StartTransaction "CK13N"

'Enter Material Number, STD1, date etc.
    session.findById("wnd[0]/usr/subALL:SAPLCKDI:4611/subKOPF:SAPLCKDI:4620/ctxtCKI64A-MATNR").Text = materialNumber
    session.findById("wnd[0]/usr/subALL:SAPLCKDI:4611/subKOPF:SAPLCKDI:4620/ctxtCKI64A-WERKS").Text = Worksheets("MaterialsList").Range("E2")
    session.findById("wnd[0]/usr/subALL:SAPLCKDI:4611/tabsREITER/tabpALLG/ssubALLGEMEIN:SAPLCKDI:4612/ctxtCKI64A-KLVAR").Text = "STD1"
    session.findById("wnd[0]/usr/subALL:SAPLCKDI:4611/tabsREITER/tabpALLG/ssubALLGEMEIN:SAPLCKDI:4612/ctxtCKI64A-TVERS").Text = "1"
    session.findById("wnd[0]/usr/subALL:SAPLCKDI:4611/tabsREITER/tabpALLG/ssubALLGEMEIN:SAPLCKDI:4612/ctxtCKI64A-AMDAT").Text = Worksheets("MaterialsList").Range("E3")
    session.findById("wnd[0]/tbar[0]/btn[0]").press

'export file as tab delemited text
    session.findById("wnd[0]/shellcont[1]/shell").pressToolbarContextButton "&MB_EXPORT"
    session.findById("wnd[0]/shellcont[1]/shell").selectContextMenuItem "&PC"
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\TempVariancesFolder\"
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Material#" & materialNumber & "-CK13N" & ".txt"
    session.findById("wnd[1]/tbar[0]/btn[11]").press

    Set currentmaterialTextFile = Workbooks.Open("C:\TempVariancesFolder\Material#" & materialNumber & "-CK13N" & ".txt")


'add a new sheet for that material
    BOMsWbk.Activate
        Sheets.Add
        ActiveSheet.Name = materialNumber
   
    Set materialSheet = ActiveSheet

'this was to get it all in one sheet
'Set materialSheet = BOMsWbk.Sheets("All CK13N")

Call CopyTXT(materialSheet, currentmaterialTextFile)

'store the cell addresses for the totals for that SKU
    Dim materialName As String
    Dim rawPackMaterialTotal As String
    Dim conversionTotal As String
    Dim productCostTotal As String
    Dim packMaterialTotal As String
    Dim packMaterialRange As Range
    Dim packMaterialSumComponents As String
    Dim finalCK13Nrow As Long
    Dim headerRow As Integer
    Dim materialSubtotalRow As Integer
    Dim internalActivityRow As Integer



'which i values to cycle (need to automate this to count how many items there are)
    For i = 3 To 100

'find material name
    If Cells(i, 1).Value = "Material" Then
        materialName = Cells(i, 6).Address
    End If

'Find Totals locations
    If Cells(i, 3).Value = "Material" Then
    'replace materials total with a sum formula'(insert code to do that here)
        rawPackMaterialTotal = Cells(i, 18).Address
        materialSubtotalRow = i
    End If
    If Cells(i, 3).Value = "Internal Activity" Then
        conversionTotal = Cells(i, 18).Address
        internalActivityRow = i
    End If
    If Cells(i, 2).Value = "**" Then
        productCostTotal = Cells(i, 18).Address
        finalCK13Nrow = i
    End If

'Find pack material totals cell addresses
  If Cells(i, 8).Value = "Z004" Then
    If Not packMaterialRange Is Nothing Then
        Set packMaterialRange = Union(packMaterialRange, Cells(i, 18))
    Else
        Set packMaterialRange = Cells(i, 18)
    End If
    End If

'find header row (used later to replace the formulas for cost)
    If InStr(Cells(i, 18).Value, "Total") > 0 Then
        headerRow = i
    End If

'Find kg, mhr and DVL
    Dim kgBaseQty As String
    Dim mHR As String
    Dim dVL As String
   
'find machine hours
    If Cells(i, 7).Value = "MHR1" Then
        mHR = Cells(i, 14).Address
    End If
'find kg
    If Cells(i, 7).Value = "FIX1" Then
        kgBaseQty = Cells(i, 14).Address(ReferenceStyle:=xlR1C1)
    End If
       
'find DVL
    If Cells(i, 7).Value = "DVL1" Then
       dVL = Cells(i, 14).Address
    End If
       
Next i

'get the cell addresses for pack material totals to be summed
    If Not packMaterialRange Is Nothing Then
        packMaterialSumComponents = packMaterialRange.Address
    Else
        packMaterialSumComponents = ""
    End If

'make table which lists pack, raw, conversion and total
    Dim tableStartRow As Integer
    Dim tableStartColumn As Integer

        tableStartRow = 16
        tableStartColumn = 23

'record address of the new pack material total
        packMaterialTotal = Cells(tableStartRow + 1, tableStartColumn + 1).Address
       
'add row titles
    Cells(tableStartRow + 1, tableStartColumn - 1) = "$/Base Qty"
    Cells(tableStartRow + 2, tableStartColumn - 1) = "$/kg"
       
'Raws = total materials - pack
    Cells(tableStartRow, tableStartColumn) = "Raw (inc SFG)"
    Cells(tableStartRow + 1, tableStartColumn).FormulaLocal = "=" & rawPackMaterialTotal & "-" & packMaterialTotal
       
'Pack (checking that there is pack materials and setting to 0 if not)
    Cells(tableStartRow, tableStartColumn + 1) = "Pack"
        If packMaterialSumComponents <> "" Then
            Cells(tableStartRow + 1, tableStartColumn + 1).FormulaLocal = "=SUM(" & packMaterialSumComponents & ")"
        Else
            Cells(tableStartRow + 1, tableStartColumn + 1) = 0
        End If
       
'Conversion
    Cells(tableStartRow, tableStartColumn + 2) = "Conversion"
    Cells(tableStartRow + 1, tableStartColumn + 2).FormulaLocal = "=" & conversionTotal
       
'Total
    Cells(tableStartRow, tableStartColumn + 3) = "Total"
    Cells(tableStartRow + 1, tableStartColumn + 3).FormulaLocal = "=" & productCostTotal
       
'make $/kg line
    Cells(tableStartRow + 2, tableStartColumn).FormulaR1C1 = "=(R[-1]C[0])" & "/" & kgBaseQty
    Range(Cells(tableStartRow + 2, tableStartColumn), Cells(tableStartRow + 2, tableStartColumn + 3)).FillRight
       
'format as $$$
    Range(Cells(tableStartRow + 1, tableStartColumn), Cells(tableStartRow + 2, tableStartColumn + 3)).NumberFormat = "$#,##0.00"
       
'add materialName
    Cells(tableStartRow, tableStartColumn - 1).FormulaLocal = "=" & materialName
       
'replace the hardcoded values with formulas
'replace the total price with formula then fill down for materials
    Cells(headerRow + 2, 18).FormulaR1C1 = "=(R[0]C[-4]/R[0]C[-1])*R[0]C[-2]"
    Range(Cells(headerRow + 2, 18), Cells(materialSubtotalRow - 2, 18)).FillDown
   
'for conversion
    Cells(materialSubtotalRow + 2, 18).FormulaR1C1 = "=(R[0]C[-4]/R[0]C[-1])*R[0]C[-2]"
    Range(Cells(materialSubtotalRow + 2, 18), Cells(finalCK13Nrow - 4, 18)).FillDown
 
'replace the subtotals'replace materials subtotal
    Cells(materialSubtotalRow, 18).FormulaLocal = "=sum(" & Range(Cells(headerRow + 2, 18), Cells(materialSubtotalRow - 2, 18)).Address & ")"

'replace conversion subtotal
    Cells(internalActivityRow, 18).FormulaLocal = "=sum(" & Range(Cells(materialSubtotalRow + 2, 18), Cells(internalActivityRow - 2, 18)).Address & ")"

'replace total
    Cells(finalCK13Nrow, 18).FormulaLocal = "=" & Cells(materialSubtotalRow, 18).Address & "+" & Cells(internalActivityRow, 18).Address

'tidy up each material page and add layout.
    Columns("B:E").AutoFit
    Columns("G:U").AutoFit
    Columns("W:Z").AutoFit
    Range("A16:z16").Interior.ColorIndex = 48
   
'add links to summary page
    Worksheets("Summary").Activate
        Cells(x + 1, 1).FormulaLocal = "=" & materialNumber & "!" & Cells(tableStartRow, tableStartColumn - 1).Address
        Cells(x + 1, 2).FormulaLocal = "=" & materialNumber & "!" & Cells(tableStartRow + 2, tableStartColumn).Address(True, False)
        Range(Cells(x + 1, 2), Cells(x + 1, 5)).FillRight
        Columns("A").AutoFit
   
'show progress of working through list as you go
    Application.ScreenUpdating = True
        Worksheets("MaterialsList").Activate
        Worksheets("MaterialsList").Cells(x + 1, 2).Value = "done"
        Worksheets("MaterialsList").Range("B" & x + 1).Select
Next x

End Sub

 

[orsm6]

Hi orsm6,

How are you defining (what variable are you using) for packMaterialRange? When I created a range variable like so at the top of the macro...

Dim packMaterialRange As Range

...it worked for me.

Robert

thanks Robert, hopefully the code i posted just before helps you see the whole scenario. As i mentioned in top of thread, the macro will run absolutely fine on the first material. when it then loops back to run the report for the second material code it will add a sheet, paste the data in, but then crash

 

[offthelip]

I think your problem is because you are trying to set a union which includes ranges on two different worksheets, which excel doesn't like. This is because it works for the first file , but when you come to the next file packMaterialRange is set to the range in the first file so this line of text:

If Not packMaterialRange Is Nothing Then

is true .
I suggest changing the if statement to a flag which you set to true before the loop and set it false in the if state ment like this:

firstiteration=True
for i= 3 to 100
....
...
...
If firstiteration Then
        Set packMaterialRange = Cells(i, 18)
        firstiteration =False 
    Else
        Set packMaterialRange = Union(packMaterialRange, Cells(i, 18))
    End If

 

[orsm6]

I think your problem is because you are trying to set a union which includes ranges on two different worksheets, which excel doesn't like. This is because it works for the first file , but when you come to the next file packMaterialRange is set to the range in the first file so this line of text:

If Not packMaterialRange Is Nothing Then

is true .
I suggest changing the if statement to a flag which you set to true before the loop and set it false in the if state ment like this:

firstiteration=True
for i= 3 to 100
....
...
...
If firstiteration Then
        Set packMaterialRange = Cells(i, 18)
        firstiteration =False
    Else
        Set packMaterialRange = Union(packMaterialRange, Cells(i, 18))
    End If

thank you offthelip..... that has worked absolutely fine. I am struggling with the code, but i understand why it needs to be written like that.
champion... thank you!!

 

[CephasOz]

If the problem is just that you are trying to Union ranges on 2 different sheets, then adding the following line before the line with the Union should fix it.

packMaterialRange.Parent.Activate

 

[orsm6]

If the problem is just that you are trying to Union ranges on 2 different sheets, then adding the following line before the line with the Union should fix it.

packMaterialRange.Parent.Activate

Hi CephasOz - this does make the macro fully run through without error, however on the second sheet it doesn't fill the sheet with the formulas that it is meant to.

 

[orsm6]

I think your problem is because you are trying to set a union which includes ranges on two different worksheets, which excel doesn't like. This is because it works for the first file , but when you come to the next file packMaterialRange is set to the range in the first file so this line of text:

If Not packMaterialRange Is Nothing Then

is true .
I suggest changing the if statement to a flag which you set to true before the loop and set it false in the if state ment like this:

firstiteration=True
for i= 3 to 100
....
...
...
If firstiteration Then
        Set packMaterialRange = Cells(i, 18)
        firstiteration =False
    Else
        Set packMaterialRange = Union(packMaterialRange, Cells(i, 18))
    End If

Hi offthelip..... the macro runs straight through as i said... but i noticed that when it finishes and i check the formulas that the macro fills the range with formulas incorrectly.

i'll try and screenshot it somehow.