VBA date

[ced0802]

Hi everyone,

I would need some help for a macro please :

Here is the situation :

I have on one file : some date such as : Tue 05-Apr 22:00 (in that format) => Colum 4 Row (changing = loop)
And some Number as : B13 => Colum 19 Row (Changing = loop)

The goal of the macro would be to write B13 in that second file attached in the case which corresponds for Tues 05 Apr, at 22pm (whcih would be in X17)

Thanks in advance for your help.

[Leith Ross]

Hello ced0802, It would help to the see the data in the other workbook "Bulk Plan - W13A". Can you post it ? Without the data from that workbook, an answer to question may not be possible.

[ced0802]

Hi Leith Ross.

Thank you v much for your answer.
Find attached what it looks like.
Many Thanks

[p45cal]

What to do when there is more than 1 value in column 19 for 1 date and time?
Eg., for 20 mar at 22.00 there are 14 different codes in column 19.

[ced0802]

Hi P45,
Thanks for your message.
Sorry there was not supposed to have any value dupplicated.

It is as exemple file hereunder.

Many Thanks !

[p45cal]

Well none of the dates in those 2 workbooks match - great.
You're going to have to change something on the destination sheet, because if you ever have a time like Tue 22-Mar 00:00 where will it go? Putting it under 24 is a bit silly as that's the next day. Better to have row 8 of the destination sheet starting at 00 and fininshing at 23.
Ignoring that, try the following macro after having adjusted the code's workbook names and sheet names to match your actual ones. make sure both workbooks are open and that they have matching dates in them, and place this code to replace your existing code:

Sub blah()
Dim sourceCells As Range
Set destnSht = ThisWorkbook.Sheets("OB4")
Set sourceWkSht = Workbooks("example for macro.xlsx").Sheets("BULK PLAN")
Set sourceCells = sourceWkSht.Columns("S:S").SpecialCells(xlCellTypeConstants, 3)
If Not sourceCells Is Nothing Then
  With destnSht
    dlr = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set DestcolmBDates = .Range("B1:B" & dlr)
    For Each cll In sourceCells.Cells
      mydate = Int(sourceWkSht.Cells(cll.Row, "D").Value)
      mytime = Round((sourceWkSht.Cells(cll.Row, "D").Value - mydate) * 24)
      DestnRow = Application.Match(CLng(mydate), DestcolmBDates, 0)
      DestnColm = Application.Match(Format(mytime, "00"), .Rows(8), 0)
      If Not IsError(DestnRow) And Not IsError(DestnColm) Then
        .Cells(DestnRow, DestnColm).Value = cll.Value
      End If
    Next cll
  End With
End If
End Sub

[ced0802]

Thank you so much P45cal for taking the time to write this code for me.

However it doesn't work (I think in step : "DestnRow".
Basically the goal would be to have for instance in Cell(67,11), A85 (matching file example for macro 2)

Thanks again for your help !!

[p45cal]

If you will move stuff about:
DestnColm = Application.Match(Format(mytime, "00"), .Rows(33), 0)

Hiding rows doesn't stop them being processed; we can change this if you want.
You have text in BULK PLAN cell D15 instead of a true date.

[ced0802]

Hi P45cal,

Thank you so much for your help.
It is working perfectly when using the file I have attached (that is sourcesheet "example macro");
However when I use my original file as the source sheet it doesn't work and I don't see the reason/ I have the dates on the same row, and column S the same ...
When I run the macro with F8 I see it doesn't attibute anything for sourcecell.

Set sourceCells = sourceWkSht.Columns("S:S").SpecialCells(xlCellTypeConstants, 3)

Do you know by any chance what could be the reason?

Thank you !

[p45cal]

SpecialCells(xlCellTypeConstants, 3)
is the same as doing this after selecting the entire S column:
Capture3.JPG
Try different options in the Go To dialogue box and tell me which one selects the right cells (it doesn't matter if it selects more than the right cells; the right cells must be included among the cells finally selected by the Go To dialogue box.
(Do you have formulae giving those values in column S?)

[ced0802]

Ok, I got it why it didn't work. Contrary to the example file I attached, for the first cell in columns S, a text was associated.
I therefore changed it to (to start from row 2:

Set sourceCells = sourceWkSht.Range("S2:S1000").SpecialCells(xlCellTypeConstants, 3)

A huge thank you to you, working very well.
If I may ask (as I'd like to understand how it is working), what does this two lines of codes mean basically :

If Not sourceCells Is Nothing Then

Set DestcolmBDates = .Range("B1:B" & dlr)

I understand that dlr defines the last row of my file, but .Range("B1:B"..??

Thanks again !!

[ced0802]

I have just realised of a slight issue
If I have the date Sat at 00:00, it doesn't work (doesn't put any value on chart).

Don't see what I could change to fix it..

[p45cal]

Works here. have you got "00" in column C of the yellow headers (row 33?) of sheet OB4?

[p45cal]

what does this two lines of codes mean basically:

If Not sourceCells Is Nothing Then

In case the SpecialCells line found no cells at all (although I should have put an On Error Resume Next before, and an On Error Goto 0 after the SpecialCells line)

Set DestcolmBDates = .Range("B1:B" & dlr)

I understand that dlr defines the last row of my file, but .Range("B1:B"..??

Say dlr was 99, then the line would be:
Set DestcolmBDates = .Range("B1:B99")

[ced0802]

hmm strange thing happening with midnight.
When I have for instance the date : Tues 5 apr 00:00...when I click on the cell, I see on formula bar : 04/04/2016 00:00:00 (and not 05/04...)
Therefore when I run my macro :

mytime = Round((sourceWkSht.Cells(cll.Row, "D").Value - mydate) * 24)

with my time = 24 (24 is not in the time, as I start day with 00:00 in column C as you mentioned).

Might be a setting issue on my excel..?

Thank again for all your help, and for the explanation. Learning a lot, much appreciated.

[p45cal]

When I have for instance the date : Tues 5 apr 00:00...when I click on the cell, I see on formula bar : 04/04/2016 00:00:00 (and not 05/04...)

I don't get this at all, all works as you might expect.

Might be a setting issue on my excel..?

perhaps?
Attach a single worksheet where what you describe about the 5 Apr is happening. No code in workbook.

How is the date data getting into Excel?

[ced0802]

Hi,
It is all working fine, sorry for the confusion.
Thank you for all your help !

I would have a last question please.
In the file attached, I would like to know if the following task would be possible to code :
In cell AH20 I have the number 39. I would like this to be subtracted everytime it comes across another a red number (demand).
=> So after the 6 (in cell F21), I would have 33 (39-6), then after the 1 I would have 37 (in AL22) and so on..

Thanks again !

[p45cal]

Try this on your latest attached file while OB4 is the active sheet:

Intersect(Range("B13").CurrentRegion, Range("E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY")).SpecialCells(xlCellTypeConstants, 1).Offset(, 1).FormulaR1C1 = "=R20C34-RC[-1]"

If you want plain values instead of formulae it's more long winded:

For Each cll In Intersect(Range("B13").CurrentRegion, Range("E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY")).SpecialCells(xlCellTypeConstants, 1).Offset(, 1).Cells
  cll.FormulaR1C1 = "=R20C34-RC[-1]"
  cll.Value = cll.Value
Next cll

(Doesn't look at the colour of the cells, merely looks for a number.)

[ced0802]

Thanks to you again

Here is attached the result I have.

The substractions should start from the 39. The 39 will be placed on today's date (but can vary in time) => so the 39 can be changing of columns.
After the first number it encounters (that is 6) the result should be : 33 (that is 39-6) (not-6 on current file)
Then it encounter the number 1 : so the macro should put : 32 (33 - 1) and so on.

Don't know if it is clear.

Many thanks for your precious help !

[p45cal]

2 points:
1. Values in cells AO22 and AP22 and AO38 and AP38 seem to be in the wrong place; was it my code which put it there?
2. You're using the same cells to hold values like 'CIP' in green and just plain numbers;what happens when there are 2 green values in adjacent hours?

That said, have a try stepping through the following code, bearing in mind it will go wrong at cells AO22 and AP22 and AO38 and AP38:

Sub blah()
For Each rw In Range("D14:AZ76").Rows
  If Cells(rw.Row, "B").Value >= Date Then
    For Each cll In Intersect(rw, Range("E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY")).Cells
      If Application.Count(cll.Offset(, -1)) = 1 Then
        cll.Select
        mystock = cll.Offset(, -1).Value
Stop
      End If
      cll.Select
      If Application.Count(cll) = 1 Then
      mystock = mystock - cll.Value
      cll.Offset(, 1).Value = mystock
      End If
    Next cll
  End If
Next rw
End Sub