Not sure if this is possible, but a Vlookup / plot

[chally3]

Hi all, I'm note sure if this at all possible in excel but I'll give it a go.
I have a table of data (Columns A-T) that updates daily and columns U-EC are all date fields (the start of each working week ie mondays)

In the spreadsheet column T has a date in each row. Each row is a different part.

What I would like to know is , can the value that appears in say "T2" be plotted in the column that that date falls into (columns U-EC )

ie cell T2 is data "20/06/19" , look across the next columns and put an "X" or copy the date into the column the working week it falls in for that row.

Appreciate there must be an easier way of doing this, but its the only way i can explain what I need.

Thank you

 

[mumps]

Try:

Sub chally()
    Application.ScreenUpdating = False
    Dim LastRow As Long, strdate As String, foundDate As Range, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("T2:T" And LastRow)
        strdate = rng
        Set foundDate = Rows(1).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
        If Not foundDate Is Nothing Then
            Cells(rng.Row, foundDate.Column) = "X"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[chally3]

Thanks, I just added this and keep getting runtime error 13 (worksheet is sheet 2 if thats any help)

 

[mumps]

Which line of code is highlighted when you click "Debug"?

 

[chally3]

I was looking for that, and strangely none!, I just get the error Run-time "13": type miss-match

 

[mumps]

Give this a try:

Sub chally()
    Application.ScreenUpdating = False
    Dim LastRow As Long, strdate As String, foundDate As Range, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("T2:T" And LastRow)
        strdate = CStr(rng)
        Set foundDate = Rows(1).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
        If Not foundDate Is Nothing Then
            Cells(rng.Row, foundDate.Column) = "X"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[chally3]

Hiya,

Just getting a "type mismatch' error warning only.


Thank you for your help by the way

kind regards
Mark

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[chally3]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

will do later, thank you for your help.

 

[chally3]

sure or PM me your email addy its only a 29k file