Find Next Empty Cell

[temerson]

Hello,

I have the following code:

Sub EmptyRow()




On Error Resume Next


Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False


Dim wbCurrent As Workbook
Dim wsCHINO As Worksheet
Dim wsSTOCKTON As Worksheet
Dim wsFLM As Worksheet
Dim wsDGV As Worksheet
Dim wsAURORA As Worksheet


Dim nLastCol, i As Integer


'KEEP CHINO COLUMNS
Set wbCurrent = ActiveWorkbook
Set wsCHINO = wbCurrent.Worksheets("CHINO")


nLastCol = wsCHINO.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column




For i = nLastCol To 22 Step -1
If InStr(1, wsCHINO.Cells(12, i).Value, "", vbTextCompare) > 0 Then
Else
Value = "TOTAL CASES"
End If
Next i




End Sub

The end goal is:

1. loop through all the tabs and find the next empty cell in row 12.
2. Then add "Total Cases" in that empty cell.
3. put in a sum formula and autofill down.

 

[Rick Rothstein]

Does this macro do what you want...

Sub TotalCases()
  Dim LastCol As Range, WS As Worksheet
  For Each WS In Worksheets
    Set LastCol = WS.Rows(12).Find("*", , xlValues, , xlByColumns, xlPrevious)
    If Not LastCol Is Nothing Then
      If LastCol.Column > 22 Then Cells(12, LastCol.Column + 1).Value = "Total Cases"
    End If
  Next
End Sub