0

I am having an issue running a VBA script that has worked in the past. The code is attempting to take data from a database and an Excel sheet already generated, then run more calculations to create usable statistics. This portion is where the error comes in: 

Dim strCat, strPlan, strCustodian As String
Dim i, lAssets As Long
Dim cn As ADODB.Connection, rs As ADODB.Recordset 'ms access connection
Public Const cnnstr As String = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source=G:\OADBO.accdb;"
Dim rngA, rngB As Range
Option Explicit

Private Sub MapExisting()

Set rngA = wsEntry.Range("A1")



For i = 1 To WorksheetFunction.CountIf(wsEntry.Columns(1), "*") - 1
Set rngB = wsMapping.Range("B1")


Set rngA = wsEntry.Columns(1).Find(What:="*", After:=rngA, _
    LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False)


    strCat = rngA.Offset(0, 2).Value
    lAssets = rngA.Offset(0, 1).Value


Call UnprotectSheets


    Set rngB = wsMapping.Columns(2).Find(What:=strCat, After:=rngB, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)


        rngB.End(xlUp).Offset(1, 0).Value = rngA    * <——ERROR IS HERE *
        rngB.End(xlUp).Offset(0, 3).Value = lAssets

Jump:
Next i

'finds all empty rows and keeps as range- then hides all in range
'Call Hiderows

Call ProtectSheets

Application.ScreenUpdating = True

wsMapping.Activate



End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
Lee
  • 1
  • 1
  • Add the details of the error, including exactly where it fails in this code, in the main body of the question to make it easier for people to help you. – Greg Nov 02 '15 at 23:52
  • Why set `rngA` only to change it 3 lines later? Also, you realize most of your variables are declared type variant yes? – findwindow Nov 02 '15 at 23:52

1 Answers1

1

If the first Find operation fails, rngA will be set to Nothing.

If that happens, you will get an error when you assign it (the line that says ERROR IS HERE).

You can check for this like so:

If rngA Is Nothing Then
Excel Hero
  • 14,253
  • 4
  • 33
  • 40