1

I created a code so that each time a record was inserted into a line the program would detect the last line with values (assigned to the variable "k") and select a specific interval for the graph to update, but an error occurs, could someone help me ?

VBA Code:

  Public Sub Chart_Update()

Dim k As Long

k = Cells(Rows.Count, "B").End(xlUp).Row

Range ("B2:B" & k & ",E2:E" & k & ",I2:K" & k & ",J2:J" & k & "K2:K & k")

'Error there!!
----------------------
ActiveChart.SetSourceData Source:=Range( _
"AC_Offset_Registers!"$B$2:$B$" & k ,AC_Offset_Registers!"$E$2:$E$" & k ,AC_Offset_Registers!"$I$2:$I$" & k ,AC_Offset_Registers!"$J$2:$J$" & k,AC_Offset_Registers!"$K$2:$K$" & k" _
)
-----------------
End Sub

I know the variable "k" is in a comment but I don't know where I should put the quotes so the syntax is correct

KhallP
  • 23
  • 6
  • First quote starts a string and the following quote ends a string. So your first string is `"AC_Offset_Registers!"` but what you actually want is `"AC_Offset_Registers!"B$2:$B$"` beeing your first string and that should be concatenated with `&` and the variable and another `&` and the next string. – Pᴇʜ Jun 08 '21 at 08:31
  • please redo the code as you are saying, I can't understand – KhallP Jun 08 '21 at 09:04

1 Answers1

0

This

Range("B2:B&k,E2:E&k,I2:I&k,J2:J&k,K2:K&k")

is no valid syntax and needs to be changed to

Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",J2:J" & k & ",K2:K" & k)

or shorter

Range("B2:B" & k & ",E2:E" & k & ",I2:K" & k)

The same technique needs to be applied to your other range.

Finally you might benefit from reading How to avoid using Select in Excel VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • could you tell me what this line would look like then? ActiveChart.SetSourceData Source:=Range( _ "AC_Offset_Registers!$B$2:$B$&k,AC_Offset_Registers!$E$2:$E$&k,AC_Offset_Registers!$I$2:$I$&k,AC_Offset_Registers!$J$2:$J$&k,AC_Offset_Registers!$K$2 :$K$&k" _ ) – KhallP Jun 08 '21 at 08:06
  • @KhallP Give it a try yourself first please. Try to adapt what I showed above, it is not that difficult. If you get stuck or errors please edit your original question and show your attempt. – Pᴇʜ Jun 08 '21 at 08:11
  • I don't really know how to do the last part, the syntax is wrong :( Public Sub Chart_Update() Dim k As Long k = Cells(Rows.Count, "B").End(xlUp).Row Range ("B2:B" & k & ",E2:E" & k & ",I2:K" & k & ",J2:J" & k & "K2:K & k") error there -------------------------------------------- ActiveChart.SetSourceData Source:=Range( _ "AC_Offset_Registers!"$B$2:$B$" & k ,AC_Offset_Registers!"$E$2:$E$" & k ,AC_Offset_Registers!"$I$2:$I$" & k ,AC_Offset_Registers!"$J$2: $J$" & k,AC_Offset_Registers!"$K$2:$K$" & k" _ ) ------------------------- End Sub – KhallP Jun 08 '21 at 08:20
  • have a look, after every `k` you need an `& "` to start the next string. The `&` concatenates a string with a variable. Please note that code in comments gets unreadable that is why I asked you to edit your original question to add code. – Pᴇʜ Jun 08 '21 at 08:22