Quick Question

[Damil4real]

I have data in columns A, B, C, D, and E.

Sample

A B C D E
1 2 3 4 1245
2 5 4 1 4156
1 4 8 0
M1 D5 D-F 1 2154
1 2 3 4 1245
2 5 4 1
1 4 8 0
M1 D5 D-F 1 2154

I want a VBA macro to run searching column E and a message box should
pop up at the end of the macro saying something like: "There are 5
(the number of found data that are not blank) values associated with
your list. The five values are 1245 located in row E2, 4156 located in
row E3, 2154 located in cell E5, and 1245 located in cell E8."

Thanks!

 

[OssieMac]

Hi,

I have edited your message a bit and tabulated the results. Feel free to get
back to me if not in the format you want.

Sub CountAndDisplay()

Dim rngE As Range
Dim cel As Range
Dim strMsge As String
Dim lngCount As Long

With Sheets("Sheet1")
Set rngE = .Range(.Cells(2, "E"), _
.Cells(.Rows.Count, "E").End(xlUp))
End With

lngCount = WorksheetFunction.Count(rngE)

strMsge = "There are " & lngCount & _
" values associated with your list'" _
& vbCrLf & "The values are:-"

For Each cel In rngE
If cel <> "" Then
strMsge = strMsge & vbCrLf & cel.Value _
& " located in " & cel.Address(0, 0)
End If
Next cel

MsgBox strMsge

End Sub

 

[Mike]

Sub Testing()
Dim rng As Range
Dim cell As Range
Dim sMsg As String
Dim i As Long

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
i = 0
For Each cell In rng
If cell.Offset(0, 4).Value <> "" Then
sMsg = sMsg & vbCrLf & "Row :" & cell.Row & " " _
& cell.Offset(0, 4).Value
i = i + 1
End If
Next cell
MsgBox "There are " & i & _
" (the number of found data that are not blank) values associated with
your list." _
& sMsg
End Sub

 

[OssieMac]

Forgot to say that I assumed that you have column headers and I started at
row 2 in the following code.
..Range(.Cells(2, "E")