Return value in cell

[goldie12]

Hello

I wanted to have a msg box appear with the value of cell A5. Basically I wanted the code to check the values in column A starting on row 5 and if there is value then show it in a msgbox.

[Jacob Hilderbrand]

Do you want to loop through all the cells with values starting at A5?



Option Explicit

Sub Macro1()
Dim i As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 5 To LastRow
If Range("A" & i).Text <> "" Then
MsgBox "A" & i & " = " & Range("A" & i).Text
End If
Next i
End Sub

[Killian]

This should do it


Dim c As Range
For Each c In ActiveSheet.Range("A5").End(xlDown)
If Not IsEmpty(c) Then MsgBox c.Text
Next

[MWE]

DRJ's method will display the value for each cell one at a time. If you want to see all the values at once (to the max amt MsgBox will display), you might consider:


Option Explicit

Sub Macro2()
Dim i As Long
Dim LastRow As Long
Dim strBuffer As String
LastRow = Range("A65536").End(xlUp).Row
For i = 5 To LastRow
If Range("A" & i).Text <> "" Then
strBuffer = strBuffer & "A" & i & vbTab & Range("A" & i).Text & vbCrLf
End If
Next i
MsgBox strBuffer
End Sub

[MWE]

This should do it


Dim c As Range
For Each c In ActiveSheet.Range("A5").End(xlDown)
If Not IsEmpty(c) Then MsgBox c.Text
Next


nice compact code :thumb

[goldie12]

Ok Thank you very much. I have figured it out. Thanks to everyone:clap:

[Killian]

nice compact code :thumb
yes it is - unfortunately it doesn't work :doh: for which I apologise unreservedly :blush
I will now withdraw from this thread in disgrace :omg2: