VBA - Find first empty cell in a row

[Mplz]

I am making a KPI tracker for our maintenance department and have an out of service report that the end users will fill out each week. I have made a basic VBA script that works for what I want it to do, but I'm trying to think of edge cases that could break it and troubleshoot beforehand. The issue I am trying to solve is this:

The user will input their number of out of service equipment, and a formula referencing their input compared to the equipment count (raw data on another sheet) gives them a percentage. Equipment doesn't change often but when it does it's in big swathes, and since this report will be for the full year, if the equipment changes then their previous percentages will change as well. I am making this macro, assigning it to a button so when they input their equipment numbers they click this macro and it navigates to the first empty cell (the week after their inputted data), offsets to the percentages formulas and copies/pastes as values to overwrite the formula and hard code the data. I've tested and it works as intended, but as a relative VBA newb I'm not sure what could break it. Also I'm sure the code is ugly and there's a better way to format it. Would appreciate any tips or advice!

Option Explicit
Sub FindFirstEmptyCell()

    Dim c
    Dim d
    Application.ScreenUpdating = False
    Application.AlertBeforeOverwriting = False
        
    'Find first empty cell then select percentages below
    For Each c In Range("C4:X7").Cells
        If c = "" Then
            c.Select
            ActiveCell.Offset(5, -1).Select
 
            Exit For
        End If
        
    Next
    
    'Copy data in formulas and paste as values to overwrite
    Range(ActiveCell, ActiveCell.Offset(3, 0)).Copy
    Range(ActiveCell, ActiveCell).PasteSpecial xlPasteValues
    
    
    For Each d In Range("C15:X18").Cells
        If d = "" Then
            d.Select
            ActiveCell.Offset(5, -1).Select
            
            Exit For
        End If
        
    Next
    
    Range(ActiveCell, ActiveCell.Offset(3, 0)).Copy
    Range(ActiveCell, ActiveCell).PasteSpecial xlPasteValues

    Range("A1").Select
    Range("A1").ClearContents
    
    Application.ScreenUpdating = True
    Application.AlertBeforeOverwriting = True
    
    
    
    
End Sub

 

[Jeffrey Mahoney]

This is the only improvements I can make since I don't understand why you are copying those values

Sub FindFirstEmptyCell()

    Dim c As Range
    Dim d As Range
    
    Application.ScreenUpdating = False
    Application.AlertBeforeOverwriting = False
        
    'Find first empty cell then select percentages below
    For Each c In Range("C4:X7").Cells
        If c = "" Then
            Set d = c.Offset(5, -1)
            d.Value = d.Offset(3, 0).Value
            Exit For
        End If
    Next
    
    For Each c In Range("C15:X18").Cells
        If c = "" Then
            Set d = c.Offset(5, -1)
            d.Value = d.Offset(3, 0).Value
            Exit For
        End If
    Next
    
    Range("A1").ClearContents
    
    Application.ScreenUpdating = True
    Application.AlertBeforeOverwriting = True
    
End Sub

 

[Mplz]

Thanks for the reply, definitely looks cleaner. I'm using this to overwrite formulas with hard coded data.

Let's say I have 150 pieces of equipment, the target goal is to have < 5% out of service, and this week 6 are down. So this week I have 4% down and have met my goal. Now say 3 months from now I retire 50 pieces of equipment. When the equipment count is updated to 100 (which the percentage formula is referencing) my percentage for August 1st goes up to 6%, even though I'm now in November. So this macro is to take the percentage that's calculated by the formula, copy the data then paste it so it's hard coded so as time goes on the previous values don't change.

I looked and couldn't find a way to lock data from updating so this is what I came up with.