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 · Accepted 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.

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

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

Examples related to excel

Examples related to vba