Error, what is the reason?

[GN0001]

Option Explicit


Sub PutTextInCells()


Dim x As String

Dim i As String

x = 1


For i = 1 To 100

x = x + 1


Next i


End Sub


Thanks

Guity

 

[VaraK]

Hi GuityN,


x and i are declared as strings. But they are used as numerics. That might be the error. Change x and i as integers.


Hope this helps.

 

[Hui]

ie:

Dim x As Long

Dim i As Long

 

[GN0001]

Dear Hui,

I did and it worked but nothing took place in my worksheet.

Regards,

Guity

 

[Hui]

You haven't told it to and didn't ask us to


The following makes a list of x and X^2

[pre]

Sub PutTextInCells()
Dim i As Long
For i = 1 To 100
Cells(i, 1).Value = i
Cells(i, 2).Value = i * i
Next i
End Sub

[/pre]

 

[GN0001]

Hui & Varak,

I didn't know what to do with it.


Cells(i, 1): what is i? what is 1? What is the difference between this and a(10,10)?


Sorry for these basic questions.


Regards,

Guity

 

[Hui]

a(10,10) refers to the array called "a" and location Row No. 10 and Column No. 10


Cells(i,1) is a command that refers to the spreadsheet location i, 1 (Rows, Columns)

ie if i=10 then cells(i,1) refers to A10


so Cells(i, 1).Value = i

says make the value of cell(i,1) = i

so if i=10

cell(i,1).value = A10 = 10

 

[GN0001]

Hui,

This is very hard for me to figure out.

Cells(i,1) is a command that refers to the spreadsheet location i, 1(Rows, Columns)


What does i stand for? Is this a column, Is this a row? is this a value in the cell?

What does 1 stand for? Is this a column, Is this a row? is this a value in the cell?


I am very, very grateful to your help.


By the way my assignment is not started yet since my background check is not completed yet.


Guity

 

[Hui]

i is a variable

And in the example

  For i = 1 To 100

Cells(i, 1).Value = i

Cells(i, 2).Value = i * i

Next i

i goes from a value of 1 up to a value of 100 in steps of 1

IE i = 1 then 2 then 3 upto 100

For each loop the value of i changes

And so the location and value put in the cells changes

 

[GN0001]

Hui,

I deleted 1 from Cells(i,1).Value= i

I deleted 2 from Cells(i,2).Value= i * i


Then the values ran across first row. Can you please explain why?


In a command like Cells(i,1) what element indicates that this Macro run in column 1 and column 2?


When we select a row or column or a range? Does the computer assign a memory location for our selection? Or memory location is only for variables?


I try to understand this and thank you for your patience.


Guity

 

[Hui]

Whoops

Where i said

Cells(i,1) is a command that refers to the spreadsheet location i, 1 (Rows, Columns)


it should have said

Cells(i,1) is a command that refers to the spreadsheet location i, 1 (Columns,Rows)

 

[Hui]

Cells(i,1) is a command that refers to the spreadsheet location i, 1 (Rows, Columns)


However when Cells only has 1 parameter it appears to be starting at A1 and counting to the right. It then wraps back to row 2 when it gets to the end


eg:

Cells(5) is E1

Cells(16384) is XFD1

Cells(16385) is A2

Cells(16389) is E2


etc

 

[Hui]

Guity

Rick Rothstein (MVP - Excel) posted this link as a response to one of Chandoo's post questions today

http://www.jlathamsite.com/Teach/VBA/ProgrammingInExcelVBA_AnIntroduction.pdf


It might be worth having a look at.