PDA

View Full Version : [SOLVED] Problem with For and Cells



centinela90
12-24-2015, 12:36 PM
Hi, my name is Arturo. Thanks for reading.

I am a novice using VBA in Excel. I have this code that is useful:




Sub Arreglo()


Dim Paises(1 To 5) As String

Paises(1) = Range("C45").Value
Paises(2) = Range("C46").Value
Paises(3) = Range("C47").Value
Paises(4) = Range("C48").Value
Paises(5) = Range("C49").Value

For i = 1 To 5
MsgBox Paises(i)
Next i

End Sub

It shows a message box with the value of the cells specified. For learning reasons, I wanted to test making the code shorter by using a for to capture the cells value in the array instead of typing each one.

So I changed it to this:



Sub Arreglo()

Dim Paises(1 To 5) As String

'What I changed:

For j = 1 To 5
Paises(j) = Cells(3, 44+j).Value
Next j

For i = 1 To 5
MsgBox Paises(i)
Next i

End Sub



When I run the macro, an Message Box appears but with blank content. What am I doing wrong?

Thank you!

mikerickson
12-24-2015, 03:08 PM
The order of the arguments for Cells (and just about everything similar) is Row, Column

Try

For j = 1 To 5
Paises(j) = Cells(44+j, 3).Value
Next j

centinela90
12-24-2015, 06:56 PM
OMG. How did you know I had these values reversed? It works! I was considering taking the entire VBA course again because I believed I was missing something more complex but it resulted in being something simple. Thank you very much! :)

Happy holidays ;)