match header name and find blank cel and paste header name in remarks
hi,
i need to match Column 1 by the list of name in header, as it is not fixed header names every page ,so need to find by Selected name and need to search for blanks cell in row and paste the blanks header name in remarks row for each column till the end of page
need to put comma after each header name and remove last comma . if find single blank cell add ( is Not Available ),if more add ( are Not Available ) in remarks cell ends of words. NEED VB OR MACRO .
all in upper case .
NEED VB OR MACRO .
Service Point No
Source No. (11 Digit )
Mobile / Landline No
Phase (R/Y/B)
Meter Make
Meter Sl. No
Metre Type
Meter Model
Meter Mfg. Year
Current Rating ()Amp)
No of Floors
Meter Floor No
| Attachment | Size |
|---|---|
| CI_DataEntry_Brings- S1_8.xlsx | 175.09 KB |
Get header name for record's empty fields
Hi,
Supposing the table always starts at first row, you could make formulas using the user-defined function below.
Function MissingFields(rg As Range, Optional CommentIt As Boolean = False)
Application.Volatile
Dim rgTemp As Range
Dim cl As Range
For Each cl In rg
If cl.Text = vbNullString Then
Debug.Print cl.Address
i = i + 1
MissingFields = MissingFields & IIf(i = 1, "", ", ") & Cells(1, cl.Column).Value
End If
Next cl
If CommentIt = True Then MissingFields = UCase(MissingFields & IIf(i = 0, "All fields are available.", IIf(i = 1, " is not available.", " are not available.")))
End Function
Specialcells(xlcelltypeblanks) yielded weird behaviour, then i couldn't use it !
HTH