In this part of my macro, I have pasted the new values into the first empty row of the sheet and then I want to sort. When I run the code, it gives me a Run-time error '1004': Application-defined or object-defined error
The full sub code:
The bit that keeps giving me an error:
Not sure what I'm doing wrong. If I take this line and place it in a Sub in Module1 and fill in the variables it runs fine. I also check the local variables and everything seems in order.
Local Variables from Immediate Window:
Even if you don't know what's going wrong here, thanks for your review and input. It is greatly appreciated!
The full sub code:
Code:
Sub NewProfile()
If PTBox.value = "" Then
PSBox.value = "SELECT PROFILE TYPE"
Exit Sub
End If
Set WB = ActiveWorkbook
Set SerialSheet = WB.Worksheets(PTBox.value)
D1 = D1Box.value
D2 = D2Box.value
D3 = D3Box.value
NPS = CDbl(ActiveWorkbook.Worksheets("PSN").Cells(2, 1).Text)
If D1Lbl.Visible = True Then
row = 1
col = 1
Do While D1Col = Empty
If SerialSheet.Cells(row, col).value = D1Lbl.Caption Then D1Col = col
col = col + 1
Loop
End If
If D2Lbl.Visible = True Then
row = 1
col = 1
Do While D2Col = Empty
If SerialSheet.Cells(row, col).value = D2Lbl.Caption Then D2Col = col
col = col + 1
Loop
Else: D2Col = 100
End If
If D3Lbl.Visible = True Then
row = 1
col = 1
Do While D3Col = Empty
If SerialSheet.Cells(row, col).value = D3Lbl.Caption Then D3Col = col
col = col + 1
Loop
Else: D3Col = 100
End If
col = 1
Do While PFCol = Empty
If SerialSheet.Cells(row, col).value = "ProfileSerial" Then PFCol = col
col = col + 1
Loop
row = 1
col = 1
Do While SerialSheet.Cells(row, PFCol).value <> NPS
row = row + 1
If SerialSheet.Cells(row, PFCol).value = "" Then If SerialSheet.Cells(row, D1Col).value = "" Then SerialSheet.Cells(row, col).value = NPS
Loop
If D1Lbl.Visible = True Then SerialSheet.Cells(row, D1Col).value = D1Box.value
If D2Lbl.Visible = True Then SerialSheet.Cells(row, D2Col).value = D2Box.value
If D3Lbl.Visible = True Then SerialSheet.Cells(row, D3Col).value = D3Box.value
'This Code Determines the furthest column (which is hopefully D3Col) and Sorts the SerialSheet
If D1Col < 100 Then MaxCol = D1Col
If D2Col < 100 Then If D2Col > MaxCol Then MaxCol = D2Col
If D3Col < 100 Then If D3Col > MaxCol Then MaxCol = D3Col
SerialSheet.Range(Cells(1, 1), Cells(row, MaxCol)).Sort Header:=xlYes, Key1:=Cells(1, D1Col), Order1:=xlAscending, Key2:=Cells(1, D2Col), Order2:=xlAscending, Key3:=Cells(1, D3Col), Order3:=xlAscending
End Sub
The bit that keeps giving me an error:
Code:
SerialSheet.Range(Cells(1, 1), Cells(row, MaxCol)).Sort Header:=xlYes, Key1:=Cells(1, D1Col), Order1:=xlAscending, Key2:=Cells(1, D2Col), Order2:=xlAscending, Key3:=Cells(1, D3Col), Order3:=xlAscending
Not sure what I'm doing wrong. If I take this line and place it in a Sub in Module1 and fill in the variables it runs fine. I also check the local variables and everything seems in order.
Local Variables from Immediate Window:
Code:
?D1
1.25
?D2
.25
?D3
.75
?NPS
10000016
?row
124
?col
1
?D1Col
2
?D2Col
3
?D3Col
4
?PFCol
1
?MaxCol
4
Even if you don't know what's going wrong here, thanks for your review and input. It is greatly appreciated!