INDEX MATCH to return column header based on empty value in row

[NikToo]

I feel I'm almost there with this but not quite getting it right. I have a list of product numbers, dates across the columns, and a number in the data. What I want to do is find the product number, find the first non-blank cell in the data and return the date in the column header. I've been staring at Index/Match and just going round in a circle now.

Basically it'll give me the date the product was first sold.

Thanks in advance

 

[NikToo]

It just found the first date on row 4. I should be able to do with the line which has the error in it. I don't see why it won't...

 

[NikToo]

This gives an error 1004, Application defined or Object defined error:

firstDateColumn = .Cells(1, .Range("A", cel.Row).End(xlToRight).Column).Value

I tried selecting the cell in A[cel.Row] first but same thing.

 

[RicoS]

firstDateColumn = .Cells(1, .Range("A"& cel.Row).End(xlToRight).Column).Value

You had a comma after the "A" instead of ampersand.

 

[NikToo]

firstDateColumn = .Cells(1, .Range("A"& cel.Row).End(xlToRight).Column).Value

You had a comma after the "A" instead of ampersand.

Thanks. Bonehead mistake on my part... I actually got it running. But it won't count the columns... FirstDateColumn is 0.

 

[NikToo]

This is where it's at. It's still not counting the columns after the End(xlToRight). I know it gets a value of 0 because if I add + 1 it gives me the first date in the header row. Is there another way of it just going to the right and giving the column of the first non-blank cell?

Sub WriteNewProductsInfo()
    Dim rng As Range
    Dim cel As Range
    Dim lastRow As Long
    Dim writeRow As Long
    Dim firstDateColumn As Long
    Dim firstDate As Date
    
lastRow = Sheets("Products By Qty Pivot").Cells(Rows.Count, "AG").End(xlUp).Row
writeRow = Sheets("NewProducts").Cells(Rows.Count, "A").End(xlUp).Row + 1


With Sheets("Products By Qty Pivot")
   Set rng = .Range("AG5:AG" & lastRow)
   For Each cel In rng
       If cel.Value = 1 Then
           
           'Write New Product Code from Pivot to NewProducts tab
           Sheets("NewProducts").Range("A" & writeRow).Value = cel.Offset(0, -32).Value
           
           'Count columnsuntil first non-blank cell
           firstDateColumn = .Cells(1, .Range("A" & cel.Row).End(xlToRight).Columns).Count
              
           'Find the date in the header row 4 based on the column number
           firstDate = .Cells(4, firstDateColumn).Value
           
           'Write date based on column number
           Sheets("NewProducts").Range("B" & writeRow).Value = firstDate
           
           
           
           writeRow = writeRow + 1
       End If
   Next cel
End With

 

[Fluff]

Did you actually try what I suggested?

 

[NikToo]

Yes. I've been working off that.

This works, somewhat: firstDateColumn = .Cells(1, .Range("A" & cel.Row).End(xlToRight).Column).Column

But, it doesn't pick up if it's the first column but looks for the next blank... So halfway there.

 

[Fluff]

That is not the code I posted.

 

[NikToo]

Like I said, it wouldn't pick up the first one. It uses your loop and the basis for it is your formula. This works, for some reason if I did IsEmpty it checks the sheet I'm on, even though it's a With/End With. Go figure. But this actually works:

Sub WriteNewProductsInfo()
        Dim rng As Range
    Dim cel As Range
    Dim lastRow As Long
    Dim writeRow As Long
    Dim firstDateColumn As Long
    Dim firstDate As Date
    
lastRow = Sheets("Products By Qty Pivot").Cells(Rows.Count, "AG").End(xlUp).Row
writeRow = Sheets("NewProducts").Cells(Rows.Count, "A").End(xlUp).Row + 1


With Sheets("Products By Qty Pivot")
   Set rng = .Range("AG5:AG" & lastRow)
   For Each cel In rng
       If cel.Value = 1 Then
           
           'Write New Product Code from Pivot to NewProducts tab
           Sheets("NewProducts").Range("A" & writeRow).Value = cel.Offset(0, -32).Value
           
           'Count columns until first non-blank cell. If the first column isn't empty, it's the first date.
           
            If IsEmpty(Sheets("Products By Qty Pivot").Range("B" & cel.Row)) = False Then
                firstDateColumn = 2
            Else
                firstDateColumn = .Cells(1, .Range("A" & cel.Row).End(xlToRight).Column).Column
            End If
              
           'Find the date in the header row 4 based on the column number
           firstDate = .Cells(4, firstDateColumn).Value
           
           'Write date based on column number
           Sheets("NewProducts").Range("B" & writeRow).Value = firstDate
           
           writeRow = writeRow + 1
       End If
   Next cel
End With
            
End Sub