• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run time error 5 invalid procedure call or argument

D

dgavilanes

Member
Hi All,


I have an excel file template that is being populated with a vba macro, it runs fine up to a point then suddendly stops, if I removed the data row where the macro stopped, once resetted, it will start running again until it finds a reason to stop again, I'm wondering if it has something to do with the file name number of characters limit or could be something else all together.


Any thoughts?


Thanks, Dennis
 
Hi Dennis,


Please change in the 7th Line of your VBA Code to this..

[pre]
Code: 
For i = 1 to 99
MsgBox "We wouldn't able to help you if you will not provide us proper information.",vbOkOnly
Next i
[/pre]

It will work perfectly.. :)


Regards,

Deb


PS: Please post at-least Macro Coding or Sample Workbook.. with your requirement..
 
Here you go,


I inherited this macro, so where would line 7 be?


Public LastRow As Integer


Sub fillup()

'

' Automatically populate values in a template

'

Dim path As String

path = "P:DPD&ADepartment_Work_ProductAccountabilityPerformance Policy2012-2013 policyReportsElementary SchoolPrincipalTemplates"

Application.DisplayAlerts = False


'define all variables that will be used

Dim schlname As String

Dim schlname_l As String

Dim schlid_text As String

Dim schlid As String

Dim unit As String

Dim area As String

Dim network As String

Dim i As Integer


Dim probstat1 As String

Dim probstat2 As String

Dim probstat3 As String


Dim perfrate1 As String

Dim perfrate2 As String

Dim perfrate3 As String


Dim perfpts1 As String

Dim perfpts2 As String

Dim perfpts3 As String


Dim psaeme1 As String

Dim psaeme2 As String

Dim psaeme3 As String


Dim turnarnd1 As String

Dim turnarnd2 As String

Dim turnarnd3 As String


Dim nclb1 As String

Dim nclb2 As String

Dim nclb3 As String


Dim psaerd1, psaerd2, psaerd3, psaerd4, psaerd5, psaerd6, psaerd7, psaerd8, psaerd9 As String

Dim psaemt1, psaemt2, psaemt3, psaemt4, psaemt5, psaemt6, psaemt7, psaemt8, psaemt9 As String

Dim psaesc1, psaesc2, psaesc3, psaesc4, psaesc5, psaesc6, psaesc7, psaesc8, psaesc9 As String

Dim avgact1, avgact2, avgact3, avgact4, avgact5, avgact6, avgact7, avgact8, avgact9 As String

Dim attadj1, attadj2, attadj3, attadj4, attadj5, attadj6, attadj7, attadj8, attadj9 As String


Dim epasrd1, epasrd2, epasrd3 As String

Dim epasmt1, epasmt2, epasmt3 As String


Dim hg_text


'Call sub procedure to find the number of rows in the data

Call LastRowInOneColumn("A")


'Start to loop by row

For i = 3 To LastRow

'LastRow


'Pick up the values you need from the data

Windows("EL prineval - macro-onepage.xls").Activate


'School name, id, and area

schlname = Range("Sheet1!A" & i).Value

schlname_l = Range("Sheet1!CG" & i).Value

schlid = Range("Sheet1!B" & i).Value

unit = Range("Sheet1!CF" & i).Value

schlid_text = Range("Sheet1!C" & i).Value

area = Range("Sheet1!D" & i).Value

network = Range("Sheet1!CH" & i).Value

'''''''''''''''''''''''''''''''''

'Performance Policy Rating

'''''''''''''''''''''''''''''''''

'probation status

probstat1 = Range("Sheet1!E" & i).Value

probstat2 = Range("Sheet1!F" & i).Value

probstat3 = Range("Sheet1!G" & i).Value

'performance rating

perfrate1 = Range("Sheet1!H" & i).Value

perfrate2 = Range("Sheet1!I" & i).Value

perfrate3 = Range("Sheet1!J" & i).Value

'performance policy points earned

perfpts1 = Range("Sheet1!K" & i).Value

perfpts2 = Range("Sheet1!L" & i).Value

perfpts3 = Range("Sheet1!M" & i).Value

'psae 2010 meet/exceeds

psaeme1 = Range("Sheet1!N" & i).Value

psaeme2 = Range("Sheet1!O" & i).Value

psaeme3 = Range("Sheet1!P" & i).Value

'turnaround eligibility

turnarnd1 = Range("Sheet1!Q" & i).Value

turnarnd2 = Range("Sheet1!R" & i).Value

turnarnd3 = Range("Sheet1!S" & i).Value

'''''''''''''''''''''''''''''''''

'NCLB Rating

'''''''''''''''''''''''''''''''''

nclb1 = Range("Sheet1!T" & i).Value

nclb2 = Range("Sheet1!U" & i).Value

nclb3 = Range("Sheet1!V" & i).Value


'''''''''''''''''''''''''''''''''

'Current Status and Trend

'''''''''''''''''''''''''''''''''

'ISAT Reading

psaerd1 = Range("Sheet1!W" & i).Value

psaerd2 = Range("Sheet1!X" & i).Value

psaerd3 = Range("Sheet1!Y" & i).Value

psaerd4 = Range("Sheet1!Z" & i).Value

psaerd5 = Range("Sheet1!AA" & i).Value

psaerd6 = Range("Sheet1!AB" & i).Value

psaerd7 = Range("Sheet1!AC" & i).Value

psaerd8 = Range("Sheet1!AD" & i).Value

psaerd9 = Range("Sheet1!AE" & i).Value


'ISAT Math

psaemt1 = Range("Sheet1!AF" & i).Value

psaemt2 = Range("Sheet1!AG" & i).Value

psaemt3 = Range("Sheet1!AH" & i).Value

psaemt4 = Range("Sheet1!AI" & i).Value

psaemt5 = Range("Sheet1!AJ" & i).Value

psaemt6 = Range("Sheet1!AK" & i).Value

psaemt7 = Range("Sheet1!AL" & i).Value

psaemt8 = Range("Sheet1!AM" & i).Value

psaemt9 = Range("Sheet1!AN" & i).Value


'ISAT Science

psaesc1 = Range("Sheet1!AO" & i).Value

psaesc2 = Range("Sheet1!AP" & i).Value

psaesc3 = Range("Sheet1!AQ" & i).Value

psaesc4 = Range("Sheet1!AR" & i).Value

psaesc5 = Range("Sheet1!AS" & i).Value

psaesc6 = Range("Sheet1!AT" & i).Value

psaesc7 = Range("Sheet1!AU" & i).Value

psaesc8 = Range("Sheet1!AV" & i).Value

psaesc9 = Range("Sheet1!AW" & i).Value


'ISAT Composite Exceeds %

avgact1 = Range("Sheet1!AX" & i).Value

avgact2 = Range("Sheet1!AY" & i).Value

avgact3 = Range("Sheet1!AZ" & i).Value

avgact4 = Range("Sheet1!BA" & i).Value

avgact5 = Range("Sheet1!BB" & i).Value

avgact6 = Range("Sheet1!BC" & i).Value

avgact7 = Range("Sheet1!BD" & i).Value

avgact8 = Range("Sheet1!BE" & i).Value

avgact9 = Range("Sheet1!BF" & i).Value


'8th Grade ISAT Composite Exceeds %

drop1 = Range("Sheet1!BG" & i).Value

drop2 = Range("Sheet1!BH" & i).Value

drop3 = Range("Sheet1!BI" & i).Value

drop4 = Range("Sheet1!BJ" & i).Value

drop5 = Range("Sheet1!BK" & i).Value

drop6 = Range("Sheet1!BL" & i).Value

drop7 = Range("Sheet1!BM" & i).Value

drop8 = Range("Sheet1!BN" & i).Value

drop9 = Range("Sheet1!BO" & i).Value


'Adjusted Attendance Rate

attadj1 = Range("Sheet1!BP" & i).Value

attadj2 = Range("Sheet1!BQ" & i).Value

attadj3 = Range("Sheet1!BR" & i).Value

attadj4 = Range("Sheet1!BS" & i).Value

attadj5 = Range("Sheet1!BT" & i).Value

attadj6 = Range("Sheet1!BU" & i).Value

attadj7 = Range("Sheet1!BV" & i).Value

attadj8 = Range("Sheet1!BW" & i).Value

attadj9 = Range("Sheet1!BX" & i).Value


'''''''''''''''''''''''''''''''''

'Value Added

'''''''''''''''''''''''''''''''''

'Reading

epasrd1 = Range("Sheet1!BY" & i).Value

epasrd2 = Range("Sheet1!BZ" & i).Value

epasrd3 = Range("Sheet1!CA" & i).Value


'Math

epasmt1 = Range("Sheet1!CB" & i).Value

epasmt2 = Range("Sheet1!CC" & i).Value

epasmt3 = Range("Sheet1!CD" & i).Value

p>'''''''''''''''''''''''''''''''''

'High Grade Text

'''''''''''''''''''''''''''''''''

hg_text = Range("Sheet1!CE" & i).Value

'''''''''''''''''''''''''''''''''''''''''''''''''''

'Fill up the spreadsheet

'''''''''''''''''''''''''''''''''''''''''''''''''''


'Open and Activate template file

Workbooks.Open Filename:=path & "template-elem-prineval-onepage.xlsx"

Workbooks("template-elem-prineval-onepage.xlsx").Activate


'''''''''''''''''''''''''''''''''''''''''''''''''''

'Populate each field in the desired cell

'''''''''''''''''''''''''''''''''''''''''''''''''''

'schoolname

Range("A3").Select

ActiveCell.Value = schlname_l

'school ID

Range("A5").Select

ActiveCell.Value = schlid_text

'Area

Range("A6").Select

ActiveCell.Value = area


'''''''''''''''''''''''''''''''''

'Performance Policy Rating

'''''''''''''''''''''''''''''''''

'probation status

Range("C42").Select: ActiveCell.Value = probstat1

'Range("E43").Select: ActiveCell.Value = probstat2

'Range("G43").Select: ActiveCell.Value = probstat3

'performance rating

Range("C40").Select: ActiveCell.Value = perfrate1

'Range("E41").Select: ActiveCell.Value = perfrate2

'Range("G41").Select: ActiveCell.Value = perfrate3

'performance policy points earned

Range("C39").Select: ActiveCell.Value = perfpts1

'Range("E40").Select: ActiveCell.Value = perfpts2

'Range("G40").Select: ActiveCell.Value = perfpts3

'psae 2010 meet/exceeds

Range("C41").Select: ActiveCell.Value = psaeme1

'Range("E42").Select: ActiveCell.Value = psaeme2

'Range("G42").Select: ActiveCell.Value = psaeme3

'turnaround eligibility

'Range("C44").Select: ActiveCell.Value = turnarnd1

'Range("E44").Select: ActiveCell.Value = turnarnd2

'Range("G44").Select: ActiveCell.Value = turnarnd3


'''''''''''''''''''''''''''''''''

'NCLB Rating

'''''''''''''''''''''''''''''''''

'Range("C14").Select: ActiveCell.Value = nclb1

'Range("C15").Select: ActiveCell.Value = nclb2

'Range("C16").Select: ActiveCell.Value = nclb3


'''''''''''''''''''''''''''''''''

'Current Status and Trend

'''''''''''''''''''''''''''''''''

'PSAE Reading

Range("C11").Select: ActiveCell.Value = psaerd1

Range("D11").Select: ActiveCell.Value = psaerd2

Range("E11").Select: ActiveCell.Value = psaerd3

Range("F11").Select: ActiveCell.Value = psaerd4

Range("G11").Select: ActiveCell.Value = psaerd5

'Range("G12").Select: ActiveCell.Value = psaerd6

Range("H11").Select: ActiveCell.Value = psaerd7

'Range("H12").Select: ActiveCell.Value = psaerd8

Range("I11").Select: ActiveCell.Value = psaerd8


'PSAE Math

Range("C14").Select: ActiveCell.Value = psaemt1

Range("D14").Select: ActiveCell.Value = psaemt2

Range("E14").Select: ActiveCell.Value = psaemt3

Range("F14").Select: ActiveCell.Value = psaemt4

Range("G14").Select: ActiveCell.Value = psaemt5

'Range("G15").Select: ActiveCell.Value = psaemt6

Range("H14").Select: ActiveCell.Value = psaemt7

'Range("H15").Select: ActiveCell.Value = psaemt8

Range("I14").Select: ActiveCell.Value = psaemt8

'PSAE Science

Range("C17").Select: ActiveCell.Value = psaesc1

Range("D17").Select: ActiveCell.Value = psaesc2

Range("E17").Select: ActiveCell.Value = psaesc3

Range("F17").Select: ActiveCell.Value = psaesc4

Range("G17").Select: ActiveCell.Value = psaesc5

'Range("G18").Select: ActiveCell.Value = psaesc6

Range("H17").Select: ActiveCell.Value = psaesc7

'Range("H18").Select: ActiveCell.Value = psaesc8

Range("I17").Select: ActiveCell.Value = psaesc8

'ISAT Composite Exceeds %

Range("C20").Select: ActiveCell.Value = avgact1

Range("D20").Select: ActiveCell.Value = avgact2

Range("E20").Select: ActiveCell.Value = avgact3

Range("F20").Select: ActiveCell.Value = avgact4

Range("G20").Select: ActiveCell.Value = avgact5

'Range("G21").Select: ActiveCell.Value = avgact6

Range("H20").Select: ActiveCell.Value = avgact7

'Range("H21").Select: ActiveCell.Value = avgact8

Range("I20").Select: ActiveCell.Value = avgact8


'8th Grade ISAT Composite Exceeds %

Range("C23").Select: ActiveCell.Value = drop1

Range("D23").Select: ActiveCell.Value = drop2

Range("E23").Select: ActiveCell.Value = drop3

Range("F23").Select: ActiveCell.Value = drop4

Range("G23").Select: ActiveCell.Value = drop5

'Range("G24").Select: ActiveCell.Value = drop6

Range("H23").Select: ActiveCell.Value = drop7

'Range("H24").Select: ActiveCell.Value = drop8

Range("I23").Select: ActiveCell.Value = drop8


'Adjusted Attendance Rate

Range("C26").Select: ActiveCell.Value = attadj1

Range("D26").Select: ActiveCell.Value = attadj2

Range("E26").Select: ActiveCell.Value = attadj3

Range("F26").Select: ActiveCell.Value = attadj4

Range("G26").Select: ActiveCell.Value = attadj5

'Range("G27").Select: ActiveCell.Value = attadj6

Range("H26").Select: ActiveCell.Value = attadj7

'Range("H27").Select: ActiveCell.Value = attadj8

Range("I26").Select: ActiveCell.Value = attadj6


'''''''''''''''''''''''''''''''''

'Value Added

'''''''''''''''''''''''''''''''''

'Reading

Range("C32").Select: ActiveCell.Value = epasrd1

Range("D32").Select: ActiveCell.Value = epasrd2

Range("E32").Select: ActiveCell.Value = epasrd3


'Math

Range("C34").Select: ActiveCell.Value = epasmt1

Range("D34").Select: ActiveCell.Value = epasmt2

Range("E34").Select: ActiveCell.Value = epasmt3


'''''''''''''''''''''''''''''''''

'High Grade Text

'''''''''''''''''''''''''''''''''

Range("A23").Select: ActiveCell.Value = hg_text


'Finally select the top so it is saved with this selection

Range("A1").Select


'Save file to "output" folder with the path, the schoolname, and xls as extension. Close workbook.


'ActiveWorkbook.SaveAs Filename:=path & "Outputxls_name" & "Principal_Evaluation_2012_" & network & "_" & schlname & ".xlsx"

' , _

' FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'Save as Pdf

Sheets(Array("Sheet1")).Select

With ActiveSheet

.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:=path & "outputpdf_schoolName2" & "PrincipalEval2012_" & network & "_" & schlname & ".pdf", _

Quality:=xlQualityStandard, _

IncludeDocProperties:=True, _

IgnorePrintAreas:=False, _

OpenAfterPublish:=False

' ActiveWorkbook.Close

End With

ActiveWorkbook.Close

Next i

End Sub


Sub LastRowInOneColumn(Col As String)

'Find the last used row in a Column

Windows("EL prineval - macro-onepage.xls").Activate

With Sheets("Sheet1")

LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row

End With

End Sub
 
Hi Dennis ,


I think it would be nice if you could upload your workbook ; I hope the above code in your workbook is properly indented , and the indentation has been lost in copying and pasting.


Narayan
 
Good day dgavilanes


If you reread Debraj Roy's post you will see he was making some light hearted humor and asking for more information, your original post gave nothing a way as to what your VBA was/is.
 
You must log in or register to reply here.
Back
Top