Insert Fomula while Cell is not empty

[thrj]

Hello Folks,

I have a spreadsheet which is refreshed every week. There is a field with the date, that is used to fill a weeknum formula. I need a macro that after the data is refreshed, check all the rows in column A until the last row filled, and I need it to get these dates from each field in column A and use the weeknum formula in column G. So the column G would use the date from column A to show the week number.

Could anyone give a hand?


Thanks

 

[GlennUK]

What method are you using to refresh the data? If it's a data connection you should be able to set the options to have formulas populate automatically.

 

[thrj]

I am refreshing the data from an HTML page. Tried to use a loop to count column A until cell.value was <> from "" and fill the column G with weeknum related to the A column.. but did not manage to make it work. Does anyone have any idea of a macro to do that?

 

[GlennUK]

It's be like:

Sub Marine()
   Range("A1",Range("A65536").End(xlUp)).Formula = "=myweeknumformula"
End Sub

... using your formula ( as per row 1 ) in there instead of the example text I used.

 

[thrj]

Thanks! I manged to do what I was trying with the following formula:

Sub mapped()

Range("E2").AutoFill Destination:=Range("E2:E" & Cells(Rows.Count, "B").End(xlUp).Row)

End Sub