CODEWITHSUNDEEP
excelvba
user11091170
user11091170

Reputation:

On Error, populate cell and continue with loop

The following block of code is a loop intended to look for the value of cell(i, 1) of spreadsheet A in spreadsheet B and return a value of TRUE if found, false if not in cells(i, 5). At the moment, it will error out if it cannot find the value:

Sub findCell()
    Dim ETLCell As Range
    Dim mifidCell As Range
    Dim last_row_A As Long
    Dim last_row_B As Long
    
    last_row_A = Worksheets("Spreadsheet A").UsedRange.rows.Count
    last_row_B = Worksheets("Spreadsheet B").UsedRange.rows.Count
    
    'Loop which returns the TRN beside each column
    For i = 2 To last_row_A
        
        Set ETLCell = Worksheets("Spreadsheet B").Columns("B:B").Find(What:=Worksheets("Spreadsheet A").Cells(i, 1).Value)
        Set mifidCell = Worksheets("Spreadsheet A").Cells(i, 1).Value
        
        If ETLCell Is Nothing Then
            ETLCell = "BLANK"
            Worksheets("Reconciliation").Cells(i, 2).Value = "False"
        End If
        
        Worksheets("Reconciliation").Cells(i, 1).Value = ETLCell
        
        If ETLCell = mifidCell Then
            Worksheets("Reconciliation").Cells(i, 2).Value = "True"
        End If
    Next i
End Sub

Upvotes: 0

Views: 103

Answers (1)

Robert Todar
Robert Todar

Reputation: 2145

Default Member

On Objects, such as the Range Object there is a thing called a Default Member. In the case of Range the default is .Value.

' This example
Range("A1") = 1234

' is the same as this example
Range("A1").Value = 1234

Read vba-trap-default-members for greater detail as why this a good thing to avoid using in your code.

Errors

In your code, you are trying to assign a value to a Range object that is Nothing, which it would fail as there is no range to assign the value to.

If ETLCell Is Nothing Then
    ' ETLCell is nothing (aka it's not referecning a cell.)
    ' Therefore you can not assign a value to the non-cell!
    ETLCell = "BLANK"

    ' Same as ETLCell.value = "BLANK"

Additionally, you are attempting to set a Range object to a .Value. This too will fail, as your data types do not line up. To assign this, you must remove .Value.

Dim mifidCell As Range ' Declared as a range

' Unable to set a `Range` to a value. Must remove `.Value`!
Set mifidCell = Worksheets("Spreadsheet A").Cells(i, 1).Value

Solution

With what you have provided, it's difficult to fully solution this out. But the main thing you need to check is that your ranges exists or not. If they don't, then what values are you going to use, or what logic do you want to apply next.

With that in mind, here is an example with notes to allow you to play around with it... Just be mindful of the possible failure points.

Sub findCell()
    ' Not used...
    ' Dim last_row_B As Long
    ' last_row_B = Worksheets("Spreadsheet B").UsedRange.rows.Count

    Dim last_row_A As Long
    last_row_A = Worksheets("Spreadsheet A").UsedRange.rows.Count
    
    ' Loop which returns the TRN beside each column
    Dim i As Long
    For i = 2 To last_row_A
        Dim mifidCell As Range
        Set mifidCell = Worksheets("Spreadsheet A").Cells(i, 1)
        ' Need to check if this is nothing, as ETLCell relies on
        ' the cell as well.
        If mifidCell Is Nothing Then
            ' Can't get `ETLCell` as it uses the value of `mifidCell`
            ' You can skip this iteration, provide a default value, throw an error,
            ' or something like that.
        End If

        ' This will error if `mifidCell is Nothing`
        Dim ETLCell As Range
        Set ETLCell = Worksheets("Spreadsheet B").Columns("B:B").Find(What:=mifidCell.Value)

        If ETLCell Is Nothing Then
            Worksheets("Reconciliation").Cells(i, 2).Value = False
            Worksheets("Reconciliation").Cells(i, 1).Value = "BLANK"
        Else
            Worksheets("Reconciliation").Cells(i, 1).Value = ETLCell.Value
        End If
        
        ' This too will fail if `ETLCell is nothing or mifidCell Is Nothing`
        If ETLCell.Value = mifidCell.Value Then
            Worksheets("Reconciliation").Cells(i, 2).Value = True
        End If
    Next i
End Sub

Upvotes: 0

Related Questions