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

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
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

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
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...
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
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

Board Regular
Joined
May 1, 2019
Messages
60
VBA Code:
firstDateColumn = .Cells(1, .Range("A"& cel.Row).End(xlToRight).Column).Value
You had a comma after the "A" instead of ampersand.
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
VBA Code:
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

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365

ADVERTISEMENT

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?

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,686
Office Version
  1. 365
Platform
  1. Windows
Did you actually try what I suggested?
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,686
Office Version
  1. 365
Platform
  1. Windows
That is not the code I posted.
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
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:

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,911
Messages
5,627,594
Members
416,255
Latest member
amethystia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top