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 .

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

<tbody>
</tbody><colgroup><col></colgroup>

Service Point No

Source No. (11 Digit Pole No.)

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

REMARKS

1

10000160985

RYB

LT-LTD

EM

1997

7

4

MOBILE / LANDLINE NO, METER SL. NO, METER MODEL, CURRENT RATING ()AMP) ARE NOT AVAILABLE

2

+910005855555

########

LT-LTD

JQ21510

EM

EM101

1997

5-20

5

SOURCE NO. (11 DIGIT POLE NO.), METER FLOOR NO ARE NOT AVAILABLE

2

10000256656

5855555

LT-LTD

JQ22391

EM

EM101

1997

5-20

8

8

MOBILE / LANDLINE NO IS NOT AVAILABLE

2

10000000464

+9188654646435451

LT-LTD

JQ22387

ELECTRO MECH

1997

5-20

PHASE (R/Y/B), METER MODEL, NO OF FLOORS, METER FLOOR NO ARE NOT AVAILABLE

<tbody>
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col><col><col span="2"><col></colgroup>

 

[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 .

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

<tbody>
</tbody>

Service Point No

Source No. (11 Digit Pole No.)

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

REMARKS

1

10000160985

RYB

LT-LTD

EM

1997

7

4

MOBILE / LANDLINE NO, METER SL. NO, METER MODEL, CURRENT RATING ()AMP) ARE NOT AVAILABLE

2

+910005855555

########

LT-LTD

JQ21510

EM

EM101

1997

5-20

5

SOURCE NO. (11 DIGIT POLE NO.), METER FLOOR NO ARE NOT AVAILABLE

2

10000256656

5855555

LT-LTD

JQ22391

EM

EM101

1997

5-20

8

8

MOBILE / LANDLINE NO IS NOT AVAILABLE

2

10000000464

+9188654646435451

LT-LTD

JQ22387

ELECTRO MECH

1997

5-20

PHASE (R/Y/B), METER MODEL, NO OF FLOORS, METER FLOOR NO ARE NOT AVAILABLE

<tbody>
</tbody>

 

[shrinivasmj]

hi,

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