wie kann ich in VBA ermitteln welche die erste freie Zelle in Spalte B ist unterhalb von B13?
Gruß
Martin
Sub CBErzeugen()
Dim Zei As Long, CB As Object, L As Single, T As Single
Dim W As Single, H As Single
Const Anzahl As Integer = 200
Application.ScreenUpdating = False
With Worksheets("Projekte")
For Each CB In .Shapes
If CB.Name Like "Check*" Then CB.Delete
Next CB
.Range("C2:C2000").ClearContents
.Range("C2:C" & Anzahl + 1).Value = "nicht bearbeitet"
For Zei = 2 To Anzahl + 1
H = .Cells(Zei, 3).Height
W = 25
L = .Cells(Zei, 4).Left - W
T = .Cells(Zei, 3).Top
Set CB = .CheckBoxes.Add(L, T, W, H)
With CB
.Name = "Check" & Zei - 1
.Caption = ""
.Placement = xlMove ' xlFreeFloating , xlMoveAndSize(wird zu xlMove?)
.OnAction = "Status"
End With
Next Zei
End With
Application.ScreenUpdating = True
End Sub
Sub Status()
With Worksheets("Projekte").Shapes(Application.Caller).TopLeftCell
.Value = IIf(.Value = "bearbeitet", "nicht bearbeitet", "bearbeitet")
End With
End Sub
Sub Test()
Dim lngNextFree As Long
On Error Resume Next
lngNextFree = Range("B14").Resize(Rows.Count - 13, 1).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row
On Error GoTo 0
MsgBox lngNextFree
End Sub
Option Explicit
Sub MyEach()
Dim rng As Range
For Each rng In Range("B14:B65536")
If IsEmpty(rng) Then MsgBox "erste freie Zelle in B = B" & rng.Row: Exit Sub
Next
MsgBox "keine leere Zelle gefunden"
End Sub
Das ist eben der Unterschied zwischen dem Profi (Sepp)