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.
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.
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
Source: Stackoverflow.com