lundi 14 septembre 2020

resolve type mismatch error when filling all empty cells within range with hyphen

I am currently working on a Macro that loops through all cells in a certain range and fills all empty cells with a hyphen. The cells that are empty can vary, which is why I decided to use a loop in the first place. I first tried it with a if cell.value = "" but that returned an error. I found an instance online where someone used this method with rng and then looped accordingly (with for each rng in the range). However, I now get a type mismatch error for the line "For each rng in crange". Can someone explain to me why, and how I can resolve this? The macro still seems to work but obviously I do not want a macro that displays an error message every time it is run.

Thanks in advance! I copied the code I have below.

Dim Rng As Range
Dim wsDestination As Worksheet, wsSource As Worksheet
    
    'set worksheets
With ThisWorkbook
        Set wsSource = .Worksheets(Sheets.Count)
        Set wsDestination = .Worksheets("Overview")
End With
    

LastRow1 = wsDestination.Cells(Rows.Count, "A").End(xlUp).Row
LastColumn1 = wsDestination.Cells(5, wsDestination.Columns.Count).End(xlToLeft).Column
Set cRange = wsDestination.Range(wsDestination.Cells(4, 1), wsDestination.Cells(LastRow1, LastColumn1))


    For Each Rng In cRange
    If Rng.Value = "" Then
        Rng.Value = "-"
    End If
    Rng.HorizontalAlignment = xlRight
   
   
    
    
    Next Rng
   
   
    

End Sub

Aucun commentaire:

Enregistrer un commentaire