PDA

View Full Version : [SOLVED] Return value in cell



goldie12
09-09-2005, 07:42 AM
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
09-09-2005, 07:50 AM
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
09-09-2005, 07:53 AM
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
09-09-2005, 07:59 AM
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
09-09-2005, 08:01 AM
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
09-09-2005, 08:03 AM
Ok Thank you very much. I have figured it out. Thanks to everyone:clap:

Killian
09-09-2005, 08:14 AM
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: