help with editing code

[chammy88]

Hi guys,

I'm trying to go through a column (column A) and if the date in that column is todays date, I want it to activate the cell in the corresponding row and column C.

Below is what I've done. The line that says ActiveCell.Activate is wrong and I would like to know what to put in there.

Any help is greatly appreciated.

Dim row As Single
Dim startrow As Single
startrow = 10
row = startrow
Worksheets("Tonnes").Range("A:A").Select
date_row = "A"
Do Until Cells(row, date_row) = ""
If Cells(row, date_row) = Date Then
ActiveCell.Activate
End If
row = row + 1
Loop

cheers

 

[chammy88]

Just one other little query,

Once the cell is activated I want the code to F2 the cells and then press enter, so the cell updates. I recorded and macro by starting on cell E245, pressing F2 and then pressing enter and it came up like this:
Range("E245").Select
ActiveCell.FormulaR1C1 = _
"=PIAdvCalcDat(Tonnes!R5C3,Tonnes!RC[-3],Tonnes!RC[-2],""12h"",""range"",""time-weighted"",0,1,0,""sbsbrpi01"")"
Range("E246").Select

The bit in bold is the PI add-in formula which uses a tag in cell R5C3, a start time in cell RC[-3], an end time in cell RC[-2], a time interval of 12 hours, and the data comes off the sbsbrpi01 server.

I manipulated your code ever so slightly and added in the recorded macro above to get the code below. I keep getting an error with the line highlighted in blue. Do you have any idea why that would be?

Dim Fines_DS As Long, FDS As Long
Fines_DS = Worksheets("Tonnes").Cells(Rows.Count, "A").End(xlUp).row
For FDS = 10 To Fines_DS Step 1
If Range("A" & FDS).Value = Date - 1 Then
AFDS = Range("A" & FDS).Offset(0, 3).Select
AFDS.FormulaR1C1 = _
"=PIAdvCalcDat(Tonnes!R5C3,Tonnes!RC[-3],Tonnes!RC[-2],""12h"",""range"",""time-weighted"",0,1,0,""sbsbrpi01"")"
Range("A" & FDS).Offset(1, 3).Select
End If
Next FDS

Thanks in advance.

 

[Michael M]

Try this

Sub test()
Dim Fines_DS As Long, FDS As Long, AFDS As Range
Fines_DS = Worksheets("Tonnes").Cells(Rows.Count, "A").End(xlUp).Row
For FDS = 10 To Fines_DS Step 1
    If Range("A" & FDS).Value = Date - 1 Then
        Set AFDS = Range("A" & FDS).Offset(0, 3)
        AFDS.Formula = "=PIAdvCalcDat(Tonnes!R5C3,Tonnes!RC[-3],Tonnes!RC[-2],""12h"",""range"",""time-weighted"",0,1,0,""sbsbrpi01"")"
        Range("A" & FDS).Offset(1, 3).Select
    End If
Next FDS
End Sub

 

[Michael M]

And I don't think you need this line

Sub test()
Dim Fines_DS As Long, FDS As Long, AFDS As Range
Fines_DS = Worksheets("Tonnes").Cells(Rows.Count, "A").End(xlUp).Row
For FDS = 10 To Fines_DS Step 1
    If Range("A" & FDS).Value = Date - 1 Then
        Set AFDS = Range("A" & FDS).Offset(0, 3)
        AFDS.Formula = "=PIAdvCalcDat(Tonnes!R5C3,Tonnes!RC[-3],Tonnes!RC[-2],""12h"",""range"",""time-weighted"",0,1,0,""sbsbrpi01"")"
        Range("A" & FDS).Offset(1, 3).Select
    End If
Next FDS
End Sub