Solved

Excel vb script Error

Posted on 2013-06-05
4
229 Views
Last Modified: 2013-06-06
I am getting a mis-match error in a script that was written by someone else.  I'm not familiar with the spreadsheet, but I am thinking it's a simple fix. . .

Here is the code

Sub TwoMonthsSurveyDue()    '2 Month Warning for Survey Date
    Dim sApprovalStatus As String

Application.Calculation = xlCalculationManual
    iSht = "1"
    With Sheets(iSht)
        ShLastRow = .Cells(Rows.Count, "C").End(xlUp).Row
        Set ShRange = .Range("C7:C" & ShLastRow)
    End With

    For Each ShCell In ShRange    'get data from sheet(1)
        sApprovalStatus = ShCell.Offset(0, 7).Value
        If DateDiff("d", ShCell, Now) >= -60 Then
            ShCell.Select
            'MsgBox DateDiff("d", ShCell, Now) & " @ Row " & ShCell.Row 'for testing
            ShCell.Offset(0, 7).Value = "Due Date"
        Else
            'MsgBox DateDiff("d", ShCell, Now) & " @ Row " & ShCell.Row & " " & sApprovalStatus 'for testing
                If sApprovalStatus = "Due Date" Then
                    ShCell.Offset(0, 7).Value = "Approved"
                Else
                    ShCell.Offset(0, 7).Value = sApprovalStatus
                End If
        End If
    Next ShCell
    Range("A7").Select
    Application.Calculation = xlCalculationAutomatic
End Sub


(Edit: Both attachments redacted - Modulus Twelve)
Copy-of-Approved-Supplier-List-R.xlsm
Doc1-Redacted.docx
0
Comment
Question by:CodyPorter
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
Hi, CodyPorter.

Yes, just replace...
Set ShRange = .Range("C7:C" & ShLastRow)

Open in new window

...by...
Set ShRange = .Range("C8:C" & ShLastRow)

Open in new window

Regards,
Brian.
0
 

Author Comment

by:CodyPorter
Comment Utility
That worked perfectly!  Thank you.  Is that because someone added row 7?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, CodyPorter.

Is that because someone added row 7?
Possibly. Or it could be because on of Rows 1 to 5 were inserted.

I've just noticed the emails, phone numbers, contacts etc. If they are real then that file needs to be redacted. Please let me if that's the case and I'll take care of it.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now