PDA

View Full Version : VBA date



ced0802
03-31-2016, 07:53 AM
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
03-31-2016, 05:39 PM
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
03-31-2016, 11:07 PM
Hi Leith Ross.

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

p45cal
04-01-2016, 05:14 AM
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
04-01-2016, 06:31 AM
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
04-01-2016, 03:27 PM
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
04-01-2016, 04:38 PM
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
04-01-2016, 05:38 PM
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
04-02-2016, 12:27 AM
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
04-02-2016, 05:38 AM
SpecialCells(xlCellTypeConstants, 3)
is the same as doing this after selecting the entire S column:
15801
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
04-02-2016, 07:21 AM
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
04-02-2016, 08:50 AM
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
04-02-2016, 10:20 AM
Works here. have you got "00" in column C of the yellow headers (row 33?) of sheet OB4?

p45cal
04-02-2016, 10:26 AM
what does this two lines of codes mean basically:
If Not sourceCells Is Nothing ThenIn 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
04-02-2016, 11:28 AM
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
04-02-2016, 03:31 PM
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
04-03-2016, 04:02 AM
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
04-03-2016, 06:45 AM
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,A K: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,A K: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
04-03-2016, 07:13 AM
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
04-03-2016, 08:30 AM
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,A K: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

ced0802
04-06-2016, 06:28 AM
Hi p45cal,

Thank you, it is all working perfectly now !
There's a last thing I would like to do please.
I have this code hereunder which moves from one cell to another on the right.
I would like to count the number of blank cells it goes through, and when it runs into a cell with a value, the counter goes back to 0.


Sub split()

'execute les soustractions a partir du stock initial
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,A K: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

End If
cll.Select




Next cll
End If
Next rw

End Sub



Many thanks for all your help !

p45cal
04-06-2016, 08:23 AM
I don't know whether you're referring to cll or cll.offset(,-1).
In any event you can do something along the lines of:

If Application.CountBlank(theCell) = 1 Then
blankcount = blankcount + 1
Else
blankcount = 0
End If
but the:
Application.CountBlank(theCell) = 1
can be replaced with:
Application.Count(theCell) = 0
for a non-number
or:
Application.CountA(theCell) = 0
for a non-empty cell.

Play around with the various options here to see what works for you.

ced0802
04-07-2016, 02:35 AM
Thanks, working :)

What I am trying to get now with my module 15 :
When the selected cell comes accross a "0" (for instance on cell M24), it copies paste it on cell+2 (to the right) in O24 => Which is working with code I have.
And when it comes across a red interior cell (G26 for instance) it copies it on cell+2 (to the right), in I26
Not sure if my explanations are clear..

Thanks again for your help