Type mismatch error when converting Excel function with named ranges to VBA

Question

I am trying to replicate a simple excel function into VBA but I get Type mismatch error. Basically, I have two named ranges rDate and rYear and the following Excel function that works perfectly giving the following output:

=IF(YEAR(rDate)=rYear,rYear,"")

https://www.screencast.com/t/rPpsVjudqrI

However, as I mentionned, I get a Type mismatch error when trying to convert it to VBA. Here is my code:

Public ws As Worksheet
Sub Test()
Dim rDate, rYear, rResult, cell As Range

Set ws = Worksheets("Sheet1")
Set rDate = ws.Range("rDate")
Set rYear = ws.Range("rYear")
Set rResult = ws.Range("rResult")

For Each cell In rResult
    If Year(rDate) = rYear Then 'Problematic line
        cell = rYear
    Else
        cell = ""
    End If
Next
End Sub

Please let me know what I am doing wrong. Thank you

you need to specify the variable ranges, dim rDate as range, rYear as range,cell as range or you need to say rDate=ws.Range("rDate").value, you would not require the word set — Davesexcel
– Davesexcel, Commented Apr 2, 2018 at 16:08

Scott Craner · Accepted Answer · 2018-04-02 16:34:27Z

vba reacts differently to ranges than the worksheet. In vba one needs to compare the value of one cell to one value not a multi-cell range to a multi-cell range.

instead of trying to equate the whole range pick the one cell in each range based on where cell is located.

To get the correct reference on the vertical range we want to find the relative row:

rDate.Cells(cell.Row - rResult.Row + 1, 1)

and the relative column:

rYear.Cells(1, cell.Column - rResult.Column + 1)

Then:

Dim rDate, rYear, rResult, cell As Range

only will declare cell as a range and the others as Variant.

So use:

Dim rDate As Range, rYear As Range, rResult As Range, cell As Range

The worksheet function is making assumptions on row and column based on the relative location of the formula, that vba does not.

Public ws As Worksheet
Sub Test()
Dim rDate As Range, rYear As Range, rResult As Range, cell As Range

Set ws = Worksheets("Sheet1")
Set rDate = ws.Range("rDate")
Set rYear = ws.Range("rYear")
Set rResult = ws.Range("rResult")

For Each cell In rResult
    If Year(rDate.Cells(cell.Row - rResult.Row + 1, 1)) = rYear.Cells(1, cell.Column - rResult.Column + 1) Then 'Problematic line
        cell = rYear.Cells(1, cell.Column - rResult.Column + 1)
    Else
        cell = ""
    End If
Next
End Sub

A quicker method would be to load the data into memory arrays and loop through those.

Public ws As Worksheet
Sub Test()
Dim rDate, rYear, rResult
Dim i As Long, j As Long

Set ws = Worksheets("Sheet1")
rDate = ws.Range("rDate").Value
rYear = ws.Range("rYear").Value
ReDim rResult(1 To UBound(rDate, 1), 1 To UBound(rYear, 2))

For i = LBound(rDate, 1) To UBound(rDate, 1)
    For j = LBound(rYear, 2) To UBound(rYear, 2)
        If rYear(1, j) = Year(rDate(i, 1)) Then
            rResult(i, j) = rYear(1, j)
            Exit For
        End If
    Next j
Next i
ws.Range("rResult").Value = rResult

End Sub

Every time one accesses the worksheet from vba it slows down the code. With this method we only access the worksheet 3 times, regardless of how large the ranges are.

Collectives™ on Stack Overflow

Type mismatch error when converting Excel function with named ranges to VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related