Apr 19 2021 06:17 AM
I am a newbie with macros and VBA.
I want to set up an excel sheet to import data from a website once a day, calculate the average of once specific row and transfer that value from the sheet to another sheet together with the current date. The next time the macro is run, I want the average and date to not be overwritten but the macro to continute in the next row.
I started the whole thing as a recorded macro but have had to modify ita bit, and the code currently looks like this:
Sub UtrR()
'5
' UtrR Makro
'
'
Range("AA110").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-25]:RC[-2])"
Sheets("Sammanställning").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow > 1 Then lastrow = lastrow + 1
Range("A" & lastrow).Select
ActiveCell.Value = Date
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
If lastrow > 1 Then lastrow = lastrow + 1
Range("B" & lastrow).Select
ActiveCell.Value = "=Blad1!AA110"
End Sub
So my issues:
1. Since I link AA110 to the target sheet, the value changes every time I import new data to the sheet. How do I get it to copy only the static value and not link to AA110? I've tried some variants of PasteSpecial without success.
2. I am not getting the lastrow solution to work. I want the next time the macro is run the data is being written into the next empty row in the columns A and B. How do I get it to work?
Grateful for all help!
Apr 19 2021 07:11 AM
Try this version:
Sub UtrR()
Dim lastrow As Long
Worksheets("Blad1").Range("AA110").FormulaR1C1 = "=AVERAGE(RC[-25]:RC[-2])"
With Worksheets("Sammanställning")
lastrow = .Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lastrow > 1 Then lastrow = lastrow + 1
.Range("A" & lastrow).Value = Date
.Range("B" & lastrow).Value = Worksheets("Blad1").Range("AA110").Value
End With
End Sub