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
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