putting value in first nonempty column using VBA

[yummy]

Is it possible to check a column e.g. column H starting from H2 and put a value (lets say "I found it") in the first nonempty cell found in this column using VBA?

 

[Trebor76]

Hi yummy,

Are you sure you mean nonempty in that you want to over-write the first entry (nonempty) row in Column H starting at Row 2 or do you mean put the text into the the first blank (null) cell?

Robert

 

[yummy]

Sorry for the confusion. My bad
I want to put the text in first empty(blank) cell found in column H starting from range H2.
Dont wanna overwrite

THx for the reply.

 

[Trebor76]

No problem, try this:

Sub Macro1()
    
    Dim lngRowEnd As Long
    Dim rngCell As Range, _
        rngMyDataSet As Range
    
    lngRowEnd = Cells(Rows.Count, "H").End(xlUp).Row
    Set rngMyDataSet = Range("H2:H" & lngRowEnd)
    
    Application.ScreenUpdating = False
    
    For Each rngCell In rngMyDataSet
    
        If Len(rngCell.Value) = 0 Then
        
            rngCell.Value = "I found it"
        
            Exit For
            
        End If
        
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

 

[Rasm]

You can search for 'vbNullstring' - that would be an empty cell

 

[mirabeau]

Hey Yummy,
Maybe you'd also like to try

Sub trythis()
Dim stg As String, e As Range
stg = "I found it"
Set e = Range("H2")
If e(2) = vbNullString Then e(2) = stg Else e.End(4)(2) = stg
End Sub