PDA

View Full Version : [SOLVED] Macro Run-time Error '1004' Application-defined or object-defined error



alisonclever
02-05-2020, 01:08 PM
Hello everyone,

The file worked well until I changed some of the conditions in the ElseIf statements (ex: "Green" to "Yellow", etc) and the macro has stopped working since then. I've stuck with this problem for 2 weeks and still have no idea how to fix it. I'm taking over a former colleague's position and the macro was what he did back then. Please help! thank you very much!

Some highlights:

1. Originally, we extract data from SAP (that's why there's a sheet called SAP in the file) and then Paste as Value the data onto "Master Worksheet" sheet. The pasted data range from column A to M. In other words, we start with pasting (paste as value) the data to "Master Worksheet" which currently returns error message.

Debug pointed out the following line was the source of the error:


Set sales_cell = changed_cell.Offset(, -1)

Can anyone please suggest me how to fix this?

2. The Macro was written to automate every time cells in columns "Sales" or "Production" receive values (Green, Yellow, Overdue, etc), then the cell in columns "Day" will automatically return the corresponding value stated in those If and ElseIf statements. Here, the sample file has 3 Days columns (Day 1, Day 2, Day 3)

3. The data is pasted from column A-M and that's the only time we change/touch those columns. I wanted to have the macro worked for any "Sales" and "Production" columns in my file of which the 1st Sales column starts at column M or the 14th column, the 2nd is column R and so on.

I'm aware that as of now my codes start at column A which causes the offset error. Can anyone please show me how I can adjust my codes so that it starts at column 14th (column N) because Columns A-M are used to paste data only.

Thank you so much! Please help!

Paul_Hossler
02-05-2020, 02:09 PM
If you make a change to col A on the Master Worksheet, it errors because column 1 (i.e. "A") with a Offset of -1 (means go 1 column to the left) = 0 and there is no column 0

Also there's a Worksheet Change event that gets called when there's any change to Master


Private Sub Worksheet_Change(ByVal Target As Range)

Call Module1.single_change(Target)

End Sub





Provide more details and we could probably clean that up

alisonclever
02-05-2020, 02:18 PM
Hi Paul,

Sorry for the missing information but the data is pasted from column A-M and that's the only time we change/touch those columns. I wanted to have the macro worked for any "Sales" and "Production" columns in my file of which the 1st Sales column starts at column N or the 14th column, the 2nd is column R and so on.

Do you know how I can make it start from column N instead?

Thank you very much!

Paul_Hossler
02-05-2020, 03:49 PM
Well, you can play with this. You'll probably have to tweak it, but I should get you started

New Sub called 'UpdateMaster' that copies SAP and adds formulas

I didn't like the WS event, so I changed it a lot

I don't like your formulas since they show 0, but there's some references to cells that are not in your sample




Option Explicit


Public Const colSales1 As Long = 14
Public Const colProduction1 As Long = 15
Public Const colDay1 As Long = 16
Public Const colStatus1 As Long = 17


Public Const colSales2 As Long = 18
Public Const colProduction2 As Long = 19
Public Const colDay2 As Long = 20
Public Const colStatus2 As Long = 21


Public Const colSales3 As Long = 22
Public Const colProduction3 As Long = 23
Public Const colDay3 As Long = 24
Public Const colStatus3 As Long = 25




Sub UpdateMaster()
Dim r As Range
Dim wsMaster As Worksheet, wsSAP As Worksheet


If MsgBox("Do you want to update 'Master Worksheet' from 'SAP'?", vbYesNo + vbQuestion + vbDefaultButton2, "Update Master") = vbNo Then
Exit Sub
End If


Set wsMaster = Worksheets("Master Worksheet")
Set wsSAP = Worksheets("SAP")


'IMPORTANT -- turn off events
Application.EnableEvents = False


'get rid of old data
wsMaster.Cells.Clear


'copy SAP
wsSAP.Cells(1, 1).CurrentRegion.Copy wsMaster.Cells(1, 1)


'add formulas - double "" inside string to get one - Don't know what AQ1 and AP1 are
Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(colStatus1)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(O2=N2,""Sales/Production"",IF(P2=O2,$AQ$1,IF(P2=N2,$AP$1,"""")))"


Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(colStatus2)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(S2=R2,""Sales/Production"",IF(T2=S2,$AQ$1,IF(T2=R2,$AP$1,"""")))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(colStatus3)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(W2=V2,""Sales/Production"",IF(X2=W2,$AQ$1,IF(X2=V2,$AP$1,"""")))"


'IMPORTANT -- turn on events
Application.EnableEvents = True


End Sub


Public Sub MasterChange(SPD As Range)
Dim rSales As Range
Dim rProduction As Range
Dim rDay As Range

Set rSales = SPD.Cells(1, 1)
Set rProduction = SPD.Cells(1, 2)
Set rDay = SPD.Cells(1, 3)

Application.EnableEvents = False
If rSales = "Rollup" And rProduction = "Green" Then
rDay = "Green"
ElseIf rSales = "Rollup" And rProduction = "Yellow" Then
rDay = "Yellow"
ElseIf rSales = "Rollup" And rProduction = "Red" Then
rDay = "Red"
ElseIf rSales = "Rollup" And rProduction = "Overdue" Then
rDay = "Overdue"
ElseIf rSales = "Rollup" And rProduction = "Rollup" Then
rDay = "Rollup"
ElseIf rSales = " " And rProduction = " " Then
rDay.ClearContents
End If
Application.EnableEvents = True
End Sub









Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, r1 As Range

Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(colSales1).Resize(, 3))
If Not r Is Nothing Then Call DoCells(r)


Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(colSales2).Resize(, 3))
If Not r Is Nothing Then Call DoCells(r)


Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(colSales3).Resize(, 3))
If Not r Is Nothing Then Call DoCells(r)




End Sub




Private Sub DoCells(r As Range)
Dim r1 As Range
For Each r1 In r.Cells
With r1
Select Case .Column
Case colSales1, colSales2, colSales3
Call MasterChange(.Resize(1, 3))
Case colProduction1, colProduction2, colProduction3
Call MasterChange(.Offset(0, -1).Resize(1, 3))
Case colDay1, colDay2, colDay3
Call MasterChange(.Offset(0, -2).Resize(1, 3))
End Select
End With
Next
End Sub

alisonclever
02-11-2020, 08:43 AM
Hi Paul_Hossler, sorry for the late reply and the missing info and thanks for the help.

The AQ1 = "Production" and AP1 = "Sales". The formula was meant to return which department Sales or Production was the driver of the Status column.


'add formulas - double "" inside string to get one - Don't know what AQ1 and AP1 are
Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(colStatus1)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(O2=N2,""Sales/Production"",IF(P2=O2,""Production"",IF(P2=N2,""Sales"","""")))"


Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(colStatus2)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(S2=R2,""Sales/Production"",IF(T2=S2,""Production"",IF(T2=R2,""Sales"","""")))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(colStatus3)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(W2=V2,""Sales/Production"",IF(X2=W2,""Production"",IF(X2=V2,""Sales"","""")))"


Thanks a lot for your help so far! The codes work great!