Error when substituting a variable

[mike101]

In my script, I'm trying to have data grabbed and placed in excel as a list.

Range("A1&quot.Select
^ This works perfectly fine, but I want it to be a list, so I've substituted 1 with a variable, and the variable is the number I want it to be in. The variable is already declared, and I've made the following line, but for some reason it gives me an error.

Range("A" & i).Select

Any ideas as to why this is doing that? "i" is declared as the number I want it to be. Thanks.

 

[ETID]

Be careful not concatenate a string and an integer...

you must convert the integer...


Range("A" & trim(cstr(i))).Select

 

[mike101]

I'm still getting a

Run-time error "1004": Method 'Range' of object "_Global" failed

using that conversion.

 

[ETID]

make sure that the value of i is at least one and that i is delcared as an integer...no decimals



dim i as integer
i = 1
Range("A" & Trim(CStr(i))).Select

 

[DrBowes]

You should use the Cells method.

e.g.

Cells(i, 1).select

It is better practice to use this in all cases and it doesn't take much getting used to.

 

[mike101]

Hmm... I'm wondering if perhaps the problem is connected to something else in my code then. Here is the entire code for the sub to open up the files, cut info from one file, and paste into the next file

Sub Generate_files()
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users.WINDOWS\Documents\sciencefair\Processed\6-1-3.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Dim i As Long
For i = 0 To 30 Step 5
Workbooks.Open Filename:= _
"C:\Documents and Settings\All Users.WINDOWS\Documents\sciencefair\Processed\6-1-3\0-" & i & "-0.csv"
Range("A11&quot.Select
Selection.Cut
Application.CutCopyMode = False
Selection.Cut
Windows("6-1-3&quot.Activate
Range("A" & Trim(CStr(i))).Select
ActiveSheet.Paste
Next
Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "Field Mill"
Range("B1&quot.Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1&quot.Select
ActiveCell.FormulaR1C1 = "Rain Gauge"
Range("D1&quot.Select
ActiveCell.FormulaR1C1 = "Potential Gradient"
Range("E1&quot.Select
ActiveCell.FormulaR1C1 = "Time Stamp"
Range("C2&quot.Select
End Sub

I dont think I can use the "Cells(i, 1).select" method, because I am cutting and pasting 4 cells, and i is declared.

 

[ETID]

there is no cell A0 change this line

For i = 0 To 30 Step 5

to this

For i = 1 To 30 Step 5

 

[ETID]

You might (but maybe not) have to convert the "i" in this line to a string as well

from:
"C:\Documents and Settings\All Users.WINDOWS\Documents\sciencefair\Processed\6-1-3\0-" & i & "-0.csv"
to:
"C:\Documents and Settings\All Users.WINDOWS\Documents\sciencefair\Processed\6-1-3\0-" & trim(cstr(i)) & "-0.csv"