Use a cell value in VBA function with a variable

Question

I m new to VBA and I can t manage to do what I want although it s very simple   I need to automatically modify cells of a big  333x333  empty  full of zeros  spreadsheet   In a separate spreadsheet I have the row and column of all the cells to modify   5000 of them   A for loop seems to be suited for this purpose   Here is the code of my macro  The problem appears on the line before the last one   Dim val1 As String  val2 As String  i As Integer  For i   1 To 333    Sheets  Feuil2   Activate   ActiveSheet Cells i  1  Select      val1   Cells i  1  Value     val2   Cells i  2  Value  Sheets  Classeur2 csv   Select Cells val1  val2  Select  ActiveCell FormulaR1C1    1   Next i   The line that causes a problem is this one   Cells val1  val2  Select  I believe my error is a syntax error  But I can t find out what I should add before  after or around my two variables  val1  and  val2   What do you think     Thanks a lot for your help  Nicolas   Edit  My problem is now solved    The first answer is exactly what I needed to male my macro work  The second answer is the proper and faster way to do it

User · Answer

VAL1 and VAL2 need to be dimmed as integer  not as string  to be used as an argument for Cells  which takes integers  not strings  as arguments   Dim val1 As Integer  val2 As Integer  i As Integer  For i   1 To 333    Sheets  Feuil2   Activate   ActiveSheet Cells i  1  Select      val1   Cells i  1  Value     val2   Cells i  2  Value  Sheets  Classeur2 csv   Select Cells val1  val2  Select  ActiveCell FormulaR1C1    1   Next i

User · Answer

No need to activate or selection sheets or cells if you re using VBA  You can access it all directly  The code   Dim rng As Range For Each rng In Sheets  Feuil2   Range  A1 A333       Sheets  Classeur2 csv   Cells rng Value  rng Offset   1  Value     1  Next rng   is producing the same result as Joe s code   If you need to switch sheets for some reasons  use Application ScreenUpdating   False at the beginning of your macro  and Application ScreenUpdating True at the end   This will remove the screenflickering - and speed up the execution

[excel] Use a cell value in VBA function with a variable

Examples related to excel

Examples related to vba