Match list name of header and find blanks cell and take all blanks column header name in remarks (vb code)

[shrinivasmj]

hi,

i have list of header name,were i need to Match list name of header and find blanks cell and take all blanks column header name in remarks
and 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 .

[TABLE="width: 147"]
<tbody>[TR]
[TD]Service Point No[/TD]
[/TR]
[TR]
[TD]Source No. (11 Digit )[/TD]
[/TR]
[TR]
[TD]Mobile / Landline No[/TD]
[/TR]
[TR]
[TD]Phase (R/Y/B)[/TD]
[/TR]
[TR]
[TD]Meter Make[/TD]
[/TR]
[TR]
[TD]Meter Sl. No[/TD]
[/TR]
[TR]
[TD]Metre Type[/TD]
[/TR]
[TR]
[TD]Meter Model[/TD]
[/TR]
[TR]
[TD]Meter Mfg. Year[/TD]
[/TR]
[TR]
[TD]Current Rating ()Amp)
[/TD]
[/TR]
[TR]
[TD]No of Floors[/TD]
[/TR]
[TR]
[TD]Meter Floor No[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


[TABLE="width: 1540"]
<tbody>[TR]
[TD]Service Point No[/TD]
[TD]Source No. (11 Digit Pole No.)[/TD]
[TD]Mobile / Landline No[/TD]
[TD]Phase (R/Y/B)[/TD]
[TD]Meter Make[/TD]
[TD]Meter Sl. No[/TD]
[TD]Metre Type[/TD]
[TD]Meter Model[/TD]
[TD]Meter Mfg. Year[/TD]
[TD]Current Rating ()Amp)[/TD]
[TD]No of Floors[/TD]
[TD]Meter Floor No[/TD]
[TD]REMARKS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10000160985[/TD]
[TD] [/TD]
[TD]RYB[/TD]
[TD]LT-LTD[/TD]
[TD] [/TD]
[TD]EM[/TD]
[TD] [/TD]
[TD]1997[/TD]
[TD] [/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]MOBILE / LANDLINE NO, METER SL. NO, METER MODEL, CURRENT RATING ()AMP) ARE NOT AVAILABLE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]+910005855555[/TD]
[TD="align: center"]########[/TD]
[TD]LT-LTD[/TD]
[TD]JQ21510[/TD]
[TD]EM[/TD]
[TD]EM101[/TD]
[TD]1997[/TD]
[TD]5-20[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD]SOURCE NO. (11 DIGIT POLE NO.), METER FLOOR NO ARE NOT AVAILABLE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10000256656[/TD]
[TD] [/TD]
[TD]5855555[/TD]
[TD]LT-LTD[/TD]
[TD]JQ22391[/TD]
[TD]EM[/TD]
[TD]EM101[/TD]
[TD]1997[/TD]
[TD]5-20[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]MOBILE / LANDLINE NO IS NOT AVAILABLE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10000000464[/TD]
[TD]+9188654646435451[/TD]
[TD] [/TD]
[TD]LT-LTD[/TD]
[TD]JQ22387[/TD]
[TD]ELECTRO MECH[/TD]
[TD] [/TD]
[TD]1997[/TD]
[TD]5-20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PHASE (R/Y/B), METER MODEL, NO OF FLOORS, METER FLOOR NO ARE NOT AVAILABLE[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col><col><col span="2"><col></colgroup>[/TABLE]

 

[JLGWhiz]

This code does not use a separate list, but does utilize the header row, which is assumed to be row 1.

Sub orbital()
Dim sh As Worksheet, lr As Long, lc As Long, rng As Range, c As Range, i As Long
Dim strVal As String, ph As String
Set sh = Sheets(1)  'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
    Set rng = sh.Range("A" & i, "L" & i)
    X = 0
    For Each c In rng
        If c = "" Then
        strVal = sh.Cells(1, c.Column).Value & ", " & strVal
        X = X + 1
        End If
            Next
    If X = 1 Then
        ph = " is not available."
    ElseIf X > 1 Then
        ph = " are not available."
    Else
        GoTo SKIP:
    End If
    sh.Range("M" & i) = strVal & ph
    strVal = Empty
    ph = Empty
SKIP:
Next
End Sub

 

[shrinivasmj]

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 <acronym title="vBulletin">VB</acronym> OR MACRO .
all in upper case .


remarks cell ends of words. NEED <acronym title="vBulletin">VB</acronym> OR MACRO .

[TABLE="class: cms_table, width: 147"]
<tbody>[TR]
[TD]Service Point No
[/TD]
[/TR]
[TR]
[TD]Source No. (11 Digit )
[/TD]
[/TR]
[TR]
[TD]Mobile / Landline No
[/TD]
[/TR]
[TR]
[TD]Phase (R/Y/B)
[/TD]
[/TR]
[TR]
[TD]Meter Make
[/TD]
[/TR]
[TR]
[TD]Meter Sl. No
[/TD]
[/TR]
[TR]
[TD]Metre Type
[/TD]
[/TR]
[TR]
[TD]Meter Model
[/TD]
[/TR]
[TR]
[TD]Meter Mfg. Year
[/TD]
[/TR]
[TR]
[TD]Current Rating ()Amp)
[/TD]
[/TR]
[TR]
[TD]No of Floors
[/TD]
[/TR]
[TR]
[TD]Meter Floor No
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table, width: 1540"]
<tbody>[TR]
[TD]Service Point No
[/TD]
[TD]Source No. (11 Digit Pole No.)
[/TD]
[TD]Mobile / Landline No
[/TD]
[TD]Phase (R/Y/B)
[/TD]
[TD]Meter Make
[/TD]
[TD]Meter Sl. No
[/TD]
[TD]Metre Type
[/TD]
[TD]Meter Model
[/TD]
[TD]Meter Mfg. Year
[/TD]
[TD]Current Rating ()Amp)
[/TD]
[TD]No of Floors
[/TD]
[TD]Meter Floor No
[/TD]
[TD]REMARKS
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]10000160985
[/TD]
[TD][/TD]
[TD]RYB
[/TD]
[TD]LT-LTD
[/TD]
[TD][/TD]
[TD]EM
[/TD]
[TD][/TD]
[TD]1997
[/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]4
[/TD]
[TD]MOBILE / LANDLINE NO, METER SL. NO, METER MODEL, CURRENT RATING ()AMP) ARE NOT AVAILABLE
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]+910005855555
[/TD]
[TD="align: center"]########
[/TD]
[TD]LT-LTD
[/TD]
[TD]JQ21510
[/TD]
[TD]EM
[/TD]
[TD]EM101
[/TD]
[TD]1997
[/TD]
[TD]5-20
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]SOURCE NO. (11 DIGIT POLE NO.), METER FLOOR NO ARE NOT AVAILABLE
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10000256656
[/TD]
[TD][/TD]
[TD]5855555
[/TD]
[TD]LT-LTD
[/TD]
[TD]JQ22391
[/TD]
[TD]EM
[/TD]
[TD]EM101
[/TD]
[TD]1997
[/TD]
[TD]5-20
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]MOBILE / LANDLINE NO IS NOT AVAILABLE
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10000000464
[/TD]
[TD]+9188654646435451
[/TD]
[TD][/TD]
[TD]LT-LTD
[/TD]
[TD]JQ22387
[/TD]
[TD]ELECTRO MECH
[/TD]
[TD][/TD]
[TD]1997
[/TD]
[TD]5-20
[/TD]
[TD][/TD]
[TD][/TD]
[TD]PHASE (R/Y/B), METER MODEL, NO OF FLOORS, METER FLOOR NO ARE NOT AVAILABLE
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shrinivasmj]

hi,

need to search the header name for A1 TO AL1 and look for blanks cell