How to create a macro to display names not row numbers
Category: [Excel] Demo Available
How to create a macro to display names not row numbers
Requirement:
The user uses the below mentioned VBA on the spread sheet, but the user wants this macro to display names not row numbers, can anyone assist me. The names the user wants it to display is column A4 to A100.
Code:
Private Sub Workbook_Open() Dim i As Integer, expire As String '// Loop through each cell in D4:D65536 that contains data. For i = 4 To [D65536].End(xlUp).Row '// If the date in column D is due to pass in the next 10 days, add that row number to a list. If (Cells(i, 4).Value - Date) <= 10 And (Cells(i, 4).Value - Date) >= 0 Then expire = expire & "- " & i & vbCrLf End If Next '// Display the list of rows that have upcoming expiry dates in a message box. MsgBox "Items on the following rows are due to expire:" & vbCrLf & vbCrLf & expire & vbCrLf & "Please action.", vbInformation End Sub
Solution:
Code:
Private Sub Workbook_Open()
Dim i As Long, expire As String
'// Loop through each cell in D4:D65536 that contains data.
For i = 4 To Cells(Rows.Count, "D").End(xlUp).Row
'// If the date in column D is due to pass in the next 10 days, add that name to a list.
If (Cells(i, 4).Value - Date) <= 10 And (Cells(i, 4).Value - Date) >= 0 Then
expire = expire & "- " & Cells(i, "A").Value & vbCrLf
End If
Next
'// Display the list of rows that have upcoming expiry dates in a message box.
MsgBox "Items for the following names are due to expire:" & vbCrLf & vbCrLf & expire & vbCrLf & "Please action.", vbInformation
End Sub
The user sees that even if there are none near its expiry date it still pops up the message, is there a way that the user can stop that?
Code:
If len(expire) <> 0 then MsgBox "Items for the following names are due to expire:" & vbCrLf & vbCrLf & expire & vbCrLf & "Please action.", vbInformation
Obtained from the OzGrid Help Forum.
Solution provided by rory.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a macro to auto-scroll |
| How to loop a macro with various length columns |
| How to use Excel VBA macro to import data from multiple workbooks to main workbook |
| How to crate a macro for text copy and paste in different worksheets based on a variable in Excel |
| How to go to the next sheet using a macro |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.