VBA look for the average for each row with columns with specific headers.

**dimer** · 12-07-2011, 03:44 PM

Company A Company B Company C
Price Descript Price Descript Price Descript
Product A 2 Desc prod A 2 Desc prod A 4 Desc prod A
Product B 1 Desc prod B Desc prod B 2 Desc prod B
Product C Desc prod C 3.5 Desc prod C Desc prod C
Product D 3.5 Desc prod D Desc prod D 3 Desc prod D
Product E 4 Desc prod E 4 Desc prod E Desc prod E

Please see the attached file, as it will explain the situation better.
I have a table, with a unknown amount rows and columns, columns have the same repeating headers - specifically the header "Price" which I am interested in.
Sometime it happens that the field in the price column is empty.
I am trying to create a macro that will go through the table and fill the blanks with the highest "Price" of this row.
The problem for me that I have to look for specific header each time to compare values.. I do not know how to do that

Please, help if you can.. or help me to look for the right direction..

**shaukat** · 12-07-2011, 04:09 PM

hello
price depends on number of companies? mean can have many price

**dimer** · 12-07-2011, 04:18 PM

It must be a Max, of all available prices for specific row(which is a specific product). Just a penalty if the company did not post its price for each particular product.
Later that data is used to rank companies; therefore missing fields are not a good thing.

**shaukat** · 12-08-2011, 05:06 AM

hello, try this code.mark it as solved if satisfied and add to my reputation

Sub test()
Dim rang1 As Range, RangEmpty As Range, rngLast As Range, i As Long, j As Long, rang2 As Range
Set rngLast = Range("A4").SpecialCells(xlCellTypeLastCell)
lLastRow = rngLast.Row
lLastCol = rngLast.Column
For i = 4 To lLastRow
        Set rang1 = Cells(i, 2)
        For j = 2 To lLastCol Step 2
             Set rang2 = Cells(i, j + 2)
             If j + 2 > lLastCol Then Exit For
             Set rang1 = Union(rang1, rang2)
        Next
     x = Application.WorksheetFunction.Max(rang1)
        For k = 2 To lLastCol Step 2
             If Cells(i, k).Value = "" Then
                Cells(i, k).Value = x
             End If
        Next
 Next
End Sub

**dimer** · 12-08-2011, 11:31 AM

Wow this is great, nice shorcut just calculating every other column then actually searching for the "Price" tag in the header.. which works..

I added some formating like Cells(i, k).Interior.ColorIndex = 15 to highlight the eddited cells.

Great Thanks!!, the Case is solved