PDA

View Full Version : [SOLVED] working with Date&Time values / nested loops / Formatting



Tdude
08-22-2014, 05:23 AM
Hello everybody,

I created a simple loop in order to find a cell within a range and then apply some formula in the nearby cell.
the loop works perfectly.
The problem is that my "targetCell" contains date&time (e.g. "19/08/2014 10:50") which is not found within my range (full of cells with the same piece of info).
The loop works fine if I look for numbers instead of date&time but excel doesn't seem to recognize the content of the cells if they contain date&time.
I tried to force the formatting in VBA using .NumberFormat= "dd/mm/yyyy hh:mm" but did not work either.

Any suggestions on what to do, please?


Sub whatever()


Dim findRange As Range
Dim myRange As Range
Dim targetCell As Range
Dim goalCell As Range

Application.ScreenUpdating = False

Set findRange = Worksheets("intra day data").Range(Range("F5"), Range("F5").End(xlDown))
Set myRange = Range(Range("I5"), Range("I5").End(xlDown))

findRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"
myRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"

For Each targetCell In findRange

For Each goalCell In myRange

If goalCell = targetCell Then goalCell.Offset(0, 2).Value = Format(((goalCell.Offset(0, 1) - goalCell.Offset(-1, 1)) / goalCell.Offset(-1, 1)), "#,##0.00%")

Next goalCell

Next targetCell

Application.ScreenUpdating = True


End Sub

Bob Phillips
08-22-2014, 08:02 AM
Maybe try this


If Abs(goalCell - targetCell) < 1 / 86400 Then goalCell.Offset(0, 2).Value = Format(((goalCell.Offset(0, 1) - goalCell.Offset(-1, 1)) / goalCell.Offset(-1, 1)), "#,##0.00%")


and ditch the formatting

Tdude
08-22-2014, 11:30 AM
Thanks xld but unfortunately does't make any difference.

Should I perhaps try to split the content of the cell and create 2 arrays() one with date and one with time, match them and look for them both in my range? I'm afraid it is not going to work and it is just going to be a waste of time :(
I'm sure there is a simpler way to look at it.

any other suggestion?

snb
08-22-2014, 12:34 PM
x2 = Column(1).Find(CDate("19/08/2014 10:50:00"), , xlFormulas, 1)

Tdude
08-26-2014, 10:31 AM
Thanks snb but it does not seem to work on my side.
it looks like the only way is to split the content of the cell into 2 different ones (date and time).

snb
08-26-2014, 12:22 PM
Why not posting your file ?

Tdude
08-27-2014, 03:46 AM
Dear all,
after several trials (and errors) and the astonishment that it was still not working, I discovered that the problem was related to the data which was wrongly downloaded by Bloomberg, mea culpa!
As a consequence, my code was perfectly working in the first place and so are the bits that snb and xld cleverly suggested.
Thanks much for your help and apologies for the unforeseen.