CODEWITHSUNDEEP
excelvba
Nicolas
Nicolas

Reputation: 63

Use a cell value in VBA function with a variable

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.

Upvotes: 1

Views: 172107

Answers (2)

Peter Albert
Peter Albert

Reputation: 17505

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.

Upvotes: 4

Joe
Joe

Reputation: 63434

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

Upvotes: -1

Related Questions