This question has been asked, but I can't figure out an answer for my case. I know that this error occurs when the Range is somehow not specified. But, I have specified the range, or thought I did and it doesn't want to work.
The code is designed to create two new worksheets from a coded transcript and then add different formulas to count the codes in two different ways. The "LastRow" code works for the first sheet, but not for the second sheet and I can't figure out why or how to fix it.
I appreciate any suggestions.
Here is the code:
Sub FullDurationCoding()
' Format the file to run the Duration Codes on two new worksheets.
ActiveWorkbook.Worksheets(1).Name = "Transcript"
Sheets("Transcript").Select
Sheets("Transcript").Copy After:=Sheets("Transcript")
ActiveSheet.Name = "TopicDuration"
Sheets("Transcript").Select
Sheets("Transcript").Copy After:=Sheets("TopicDuration")
ActiveSheet.Name = "TurnDuration"
' Count number of lines till "@End"
Dim LastRow As Single
LastRow = Range("A" & Rows.Count).End(xlUp).Row
' Add TopicDuration formulas.
' Add Topic Duration Count formula to column C
Sheets("TopicDuration").Select
Range("C2:C" & LastRow).Formula = "=IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0""},B2))),1,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:UNT"",""$TOP:SLF:SPL"",""$TOP:OTH:UNT"",""$TOP:OTH:OVR:SPL""},B2))),C1+0,IF(OR(ISNUMBER(SEARCH({""TOP:SLF:CON:0"",""TOP:SLF:CON:MIX"",""TOP:OTH:CON:0"",""TOP:OTH:CON:MIX"",""TOP:OTH:OVR:CON:0"",""TOP:OTH:OVR:CON:MIX"",""TOP:OTH:OVR:FLW:0"",""TOP:OTH:OVR:FLW:MIX""},B2))),C1+1,C1+0)))"
Range("C1").Value = 1
' Add Topic Duration Calculation formula to column D
Range("D2:D" & LastRow).Formula = "=IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0""},B2))),C1,""NO"")"
Range("D1").Value = "Calculation"
' Add Subtotal Calulation to Cell E1 through E15
Range("E1").Formula = "=SUBTOTAL(1,D:D)"
' Filter Column D.
Range("A1:E" & LastRow).AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>NO"
' Add TurnDurationFormulas()
' Add Turn Duration Count formula to column C
Sheets("TurnDuration").Select
Range("C1").Value = 1
Range("C2").Formula = "=IF(A1=""CHI"",0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0"",""$TOP:OTH:CON:0"",""$TOP:OTH:CON:MIX"",""$TOP:OTH:OVR:CON:MIX"",""$TOP:OTH:OVR:CON:0""},B2))),1,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:UNT"",""$TOP:SLF:SPL"",""$TOP:OTH:UNT"",""$TOP:OTH:OVR:SPL""},B2))),C1+0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:CON:0"",""$TOP:SLF:CON:MIX""},B2))),C1+1,IF(A2=""com"",C1+0,IF(A1=""cod"",C1+0,IF(A1=""MOT"",C1+1,C1+0)))))))"
Range("C3").Formula = "=IF(A2=""CHI"",0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0"",""$TOP:OTH:CON:0"",""$TOP:OTH:CON:MIX"",""$TOP:OTH:OVR:CON:MIX"",""$TOP:OTH:OVR:CON:0""},B3))),1,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:UNT"",""$TOP:SLF:SPL"",""$TOP:OTH:UNT"",""$TOP:OTH:OVR:SPL""},B3))),C2+0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:CON:0"",""$TOP:SLF:CON:MIX""},B3))),C2+1,IF(OR(ISNUMBER(SEARCH({""$TOP:OTH:OVR:FLW:0"",""$TOP:OTH:OVR:FLW:MIX""},B3))),C1+1,IF(A3=""com"",C2+0,IF(A2=""cod"",C2+0,IF(A2=""MOT"",C2+1,C2+0))))))))"
Range("C4").Formula = "=IF(A3=""CHI"",0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0"",""$TOP:OTH:CON:0"",""$TOP:OTH:CON:MIX"",""$TOP:OTH:OVR:CON:MIX"",""$TOP:OTH:OVR:CON:0""},B4))),1,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:UNT"",""$TOP:SLF:SPL"",""$TOP:OTH:UNT"",""$TOP:OTH:OVR:SPL""},B4))),C3+0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:CON:0"",""$TOP:SLF:CON:MIX""},B4))),C3+1,IF(OR(ISNUMBER(SEARCH({""$TOP:OTH:OVR:FLW:0"",""$TOP:OTH:OVR:FLW:MIX""},B4))),C1+1,IF(A4=""com"",C3+0,IF(A3=""cod"",C3+0,IF(A3=""MOT"",C3+1,C3+0))))))))"
***** Range("C5:C" & LastRow).Formula = "=IF(A4=""CHI"",0,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0"",""$TOP:OTH:CON:0"",""$TOP:OTH:CON:MIX"",""$TOP:OTH:OVR:CON:MIX"",""$TOP:OTH:OVR:CON:0""},B5))),1,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:UNT"",""$TOP:SLF:SPL"",""$TOP:OTH:UNT"",""$TOP:OTH:OVR:SPL""},B5))),C4+0,IF(OR(""$TOP:SLF:CON:0"",""$TOP:SLF:CON:MIX""},B5))),C4+1,IF(OR(ISNUMBER(SEARCH({""$TOP:OTH:OVR:FLW:0"",""$TOP:OTH:OVR:FLW:MIX""},B5))),C1+1,IF(A5=""com"",C4+0,IF(A4=""cod"",C4+0,IF(A4=""MOT"",C4+1,C4+0))))))))"
' Add Turn Duration Calculation formula to column D
*** Range("D2:D" & LastRow).Formula = "=IF(C2=0,C1,IF(OR(ISNUMBER(SEARCH({""$TOP:SLF:NON:0"",""$TOP:SLF:NON:MIX"",""$TOP:OTH:NON:0"",""$TOP:OTH:NON:MIX"",""$TOP:OTH:OVR:NON:MIX"",""$TOP:OTH:OVR:NON:0""},B2))),C1,""NO"")"
Range("D1").Value = "Calculation"
' Add Subtotal Calulation to Cell E1 through E15
Range("E1").Formula = "=SUBTOTAL(1,D:D)"
' Filter Column D.
Range("A1:E" & LastRow).AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>NO"
End Sub
The line with the ***** is the one that initiates the error, but the line with the *** also gets the error when I go through the debug system. I'm guessing it must have something to do with trying to define Lastrow on two different sheets, but even when I split them into separate Sub actions and put the Dim code in the second sub, it still causes the error. This code worked earlier, but I needed to change the formula a little bit and once I did, now it won't do anything. (But I know it's not the formula because I fixed this formula in the same way on the previous lines and also in the topic duration section).
Please help. Thank you.
Selectand useWith ... End Withstatements. - Brian