Microsoft Excel - invalid procedure call or argument vba

Asked By Rajender Prasad on 24-Jan-13 02:04 AM
Dear All,

Getting an error like invalid procedure call or argument vba from the blow code. Please suggest.

Thanks


Dim accid As String
accid = Worksheets("SourceFile").Range("C" & i).Value
      If Trim(accid) = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "ACCT_ID Not Present"
      GoTo Nextloop
      ElseIf Len(accid) <> 10 Then
      Dim strAcctFrmt As String
          Dim res As String
     
      strAcctFrmt = "0000000000"
      res = Left(strAcctFrmt, Len(strAcctFrmt) - Len(accid)) & accid
      accid = res
      End If
Harry Boughen replied to Rajender Prasad on 24-Jan-13 05:04 AM
Hello Prasad,
Where is the value of i set?
Regards
Harry
Rajender Prasad replied to Harry Boughen on 24-Jan-13 08:28 AM

Actual code is below, as am very poor in optimization of code its too lenghty..

Sub Perform_Validations()
Application.ScreenUpdating = False
MsgBox ("Performing All the Validation, Please Wait....")

Worksheets("SourceFile").Activate
Range("AJ:AJ").Clear
Worksheets("SourceFile").Range("AJ2").Value = "Remarks"
Range("B3").Select
Dim count_value As Long
count_value = Worksheets("SourceFile").UsedRange.Rows.Count

Dim processedrows As Integer
'processedrows = 0
For i = 3 To count_value

'Dim strremarks As String
'If Worksheets("SourceFile").Range("A" & i).Value = "" And Worksheets("SourceFile").Range("B" & i).Value = "" And Trim(Worksheets("SourceFile").Range("C" & i).Value) = "" Then
'GoTo ClearCells
'End If

'processedrows = processedrows + 1

      Dim Result As Integer
      Result = Worksheets("SourceFile").Range("AE" & i).Value - Worksheets("SourceFile").Range("AF" & i).Value
      If Result <> 9 Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "DIG_LEFT is not equal 9"
      End If

      Dim Result1 As Integer
      Result1 = Worksheets("SourceFile").Range("AF" & i).Value
      If Result1 <> 6 Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "DIG_RIGHT is not equal 6"
      End If

      If Worksheets("SourceFile").Range("B" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "SP ID Not Present"
      GoTo Nextloop
      End If
      Dim accid, accid1 As String
      accid = Trim(Worksheets("SourceFile").Range("C" & i).Value)
      accid1 = Replace(accid, " ", "")
          If Trim(accid1) = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "ACCT_ID Not Present"
      GoTo Nextloop
      ElseIf Len(accid1) <> 10 Then
      Dim strAcctFrmt As String
          Dim res As String
     
      strAcctFrmt = "0000000000"
      res = Left(strAcctFrmt, Len(strAcctFrmt) - Len(accid1)) & accid1
      accid1 = res
      End If

      If Worksheets("SourceFile").Range("D" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "Meter Replacement date Not Present"
      GoTo Nextloop
      End If

      If Worksheets("SourceFile").Range("E" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "Serial Number Not Present"
      GoTo Nextloop
      End If

      If Worksheets("SourceFile").Range("G" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "Meter Type Not Present"
      GoTo Nextloop
      Else
      If UCase(Worksheets("SourceFile").Range("G" & i).Value) = "EML" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ESL" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "EDL" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETL" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETH" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETLS" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETHS" Then
        Else
      Worksheets("SourceFile").Range("AJ" & i).Value = "Meter Type Is Invalid"
        GoTo Nextloop
      End If
    End If

      If Worksheets("SourceFile").Range("N" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "Final Reading(KWH) Not Present"
      GoTo Nextloop
      End If

      If UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETL" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETH" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETLS" _
      Or UCase(Worksheets("SourceFile").Range("G" & i).Value) = "ETHS" Then
      If Worksheets("SourceFile").Range("O" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "FIN_RD_KVAH is mandatory"
      GoTo Nextloop
      End If
      End If
     
       If Worksheets("SourceFile").Range("O" & i).Value = "" Then
   Worksheets("SourceFile").Range("O" & i).Value = "NULL"
   Else
   GoTo Nextloop
   End If
  
      If Worksheets("SourceFile").Range("U" & i).Value = "" Then
   Worksheets("SourceFile").Range("U" & i).Value = "NULL"
   Else
    GoTo Nextloop
   End If
  
      If Worksheets("SourceFile").Range("AB" & i).Value = "" Then
   Worksheets("SourceFile").Range("AB" & i).Value = "NULL"
   Else
   GoTo Nextloop
   End If

      If Worksheets("SourceFile").Range("T" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "NEW METER DATA SERIAL NUMBER Not Present"
      GoTo Nextloop
      End If
     
     
        If Worksheets("SourceFile").Range("V" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "NEW METER DATA Meter Type Not Present"
      GoTo Nextloop
      Else
      If UCase(Worksheets("SourceFile").Range("V" & i).Value) = "EML" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ESL" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "EDL" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETL" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETH" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETLS" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETHS" Then
        Else
      Worksheets("SourceFile").Range("AJ" & i).Value = "Meter Type Is Invalid"
        GoTo Nextloop
      End If
    End If

'      If Worksheets("SourceFile").Range("V" & i).Value = "" Then
'      Worksheets("SourceFile").Range("AJ" & i).Value = "NWE METER DATA Meter Type Not Present"
'      GoTo Nextloop
'      ElseIf UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "EML" _
'      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "ESL" _
'      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "EDL" _
'      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "ETL" _
'      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "ETH" _
'      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "ETLS" _
'      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) <> "ETHS" Then
'      Worksheets("SourceFile").Range("AJ" & i).Value = "Meter Type Is Invalid"
'      GoTo Nextloop
'      End If

      If Worksheets("SourceFile").Range("AC" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "Initial Reading(KWH) Not Present"
      GoTo Nextloop
      End If

If UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETL" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETLS" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETH" _
      Or UCase(Worksheets("SourceFile").Range("V" & i).Value) = "ETHS" Then
      If Worksheets("SourceFile").Range("AD" & i).Value = "" Then
      Worksheets("SourceFile").Range("AJ" & i).Value = "FIN_RD_KVAH is mandatory"
      GoTo Nextloop
      End If
      End If

'      If Worksheets("SourceFile").Range("AD" & i).Value = "" Then
'      Worksheets("SourceFile").Range("AJ" & i).Value = "Initial Reading(KVAH) Not Present"
'      GoTo Nextloop
'      End If
If Worksheets("SourceFile").Range("AJ" & i).Value = "" Then
Worksheets("SourceFile").Range("AJ" & i).Value = "Ok"
GoTo Nextloop
End If


Nextloop:
Next i

'ClearCells:
''processedrows = processedrows + 3
''If processedrows <> count_value Then
''Worksheets("SourceFile").Rows(processedrows & ":" & count_value).Clear
''End If
''count_value = processedrows
'Columns("A:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Worksheets("Master").Activate
With Worksheets("Master").Shapes.Range(Array("RoundedRectangle3")).Select
    With Selection.ShapeRange.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent3
      .Solid
    End With
    End With
    MsgBox ("Uploaded File Has " & count_value & "Rows")
    MsgBox ("Done!!!! With the Validations.....")
Worksheets("Master").Shapes("RoundedRectangle4").Visible = True
Worksheets("Master").Shapes("RoundedRectangle5").Visible = True
Worksheets("Master").Activate
count_value = 0

End Sub

Harry Boughen replied to Rajender Prasad on 24-Jan-13 02:55 PM
Hello Prasad,
First off, the snippet of code that you provided in your first post is different to the one that you have provided this time.
Second, I have tested the first piece of code and the second piece of code (by themselves) and they both seem to work.  So are you sure that this piece of code is where the fault is occurring.  Can you check in the VBA editor and find exactly which line is at fault.  It is usually highlighted in yellow.  If you get a user dialog, click the 'Debug' button and it will show you the offending location.
Third, I can't see where you use the result of your attempt to pad out the account id value.
Regards
Harry
Rajender Prasad replied to Harry Boughen on 24-Jan-13 11:59 PM
hi,
I am getting the error in below from the second piece of code.
I mabe it bold below the line which am getting error.

    Dim accid, accid1 As String
    accid = Trim(Worksheets("SourceFile").Range("C" & i).Value)
    accid1 = Replace(accid, " ", "")
      If Trim(accid1) = "" Then
    Worksheets("SourceFile").Range("AJ" & i).Value = "ACCT_ID Not Present"
    GoTo Nextloop
    ElseIf Len(accid1) <> 10 Then
    Dim strAcctFrmt As String
      Dim res As String
   
    strAcctFrmt = "0000000000"
    res = Left(strAcctFrmt, Len(strAcctFrmt) - Len(accid1)) & accid1
    accid1 = res
    End If

Regards,
Prasad
Harry Boughen replied to Rajender Prasad on 25-Jan-13 12:48 AM
Hello Prasad,
Well, that code is working absolutely fine for me.
This suggests to me that there is something amiss with the value that is being assigned to accid1.
Have you checked the values that are being assigned to the various variables?  You can do this, when the macro stops, by hovering over/selecting the code where the variable occurs or by setting a quick watch under the Debug menu and if necessary setting a break point in the code (select the line where you would like to stop and press F9).
As an aside, if you want to have your padded account ID appear as such in a spreadsheet cell you will need:
    res = "'" & Left(strAcctFrmt, Len(strAcctFrmt) - Len(accid1)) & accid1
Or it will have to go into a properly formatted cell and then you don't need the padding code which would be one way to make your problem go away.  If it is going to be used only as a string in VBA (and as I mentioned it is not used in the code you have shown) then it should be OK.
Regards
Harry
Rajender Prasad replied to Harry Boughen on 25-Jan-13 01:51 AM

Still same problem...the bold line is still getting an error like Invalid Procedure Call or Argument.'
'

Thanks.. I will check this.. One more thing Harry.

Sheet Used range is 10000, But processed rows 100, how can I delete the remaining rows.
Ex: User dragged S No to 10000, but actual rows are only 100.
Before I perform the validations I want delete those unnecessary rows.

Any help..Please

Regards,Prasad

Harry Boughen replied to Rajender Prasad on 25-Jan-13 02:33 AM
Hello Prasad,
Select Row 101.  Click on the row marker at the left.  Then Shift,End,Down at the same time.  Then select Edit from the menu and Delete.
Harry
Rajender Prasad replied to Harry Boughen on 25-Jan-13 03:25 AM
Dear Harry,  I am expecting this to be done trhough macros VBA harry not manually.
please help

Prasad
Harry Boughen replied to Rajender Prasad on 25-Jan-13 03:45 AM
Hello Prasad,
Sub DeleteRows()
    Rows("101:10000").Select
    Selection.Delete
    Range("A1").Select
End Sub

Harry
Rajender Prasad replied to Harry Boughen on 25-Jan-13 04:56 AM
this will always delete from 101 row, but we are not sure the exact number of processed rows harry.

It should be dynamic.

Regards,
Prasad
Harry Boughen replied to Rajender Prasad on 25-Jan-13 05:49 AM
Hi Prasad
If you have a count of the number of processed rows
Rows(NoOfProcessedRows&":10000").Select
Harry