Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
for next coding wants me to define variable...
I have a for/next loop such as this...
On Error Resume Next i = 5 For col = 1 To 7 Step 2 AppPerWS.Range(Cells(30, col), Cells(54, col)).Copy AppendWS.Range("A" & i).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True Application.CutCopyMode = False i = i + 1 Next col The problem is that an compile error pops up that says "variable not defined" I didn't think you had to define these...do you? Any clue?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
for next coding wants me to define variable...
Check the very top of your module - it says:
Option Explicit If you delete that, you won't get the error BUT it's is veeeeery strongly recomended to define all the variables - it will help you big time. On 20 July, 16:10, gab1972 wrote: I have a for/next loop such as this... On Error Resume Next * * i = 5 * * For col = 1 To 7 Step 2 * * * * AppPerWS.Range(Cells(30, col), Cells(54, col)).Copy * * * * AppendWS.Range("A" & i).PasteSpecial Paste:=xlPasteValues, _ * * * * * * * * * * * * * * * * * * * * * * *Operation:=xlNone, _ * * * * * * * * * * * * * * * * * * * * * * *SkipBlanks:=False, _ * * * * * * * * * * * * * * * * * * * * * * *Transpose:=True * * * * Application.CutCopyMode = False * * * * i = i + 1 * * Next col The problem is that an compile error pops up that says "variable not defined" *I didn't think you had to define these...do you? Any clue?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
for next coding wants me to define variable...
BTW - did you manage to sort the .field issue?
The discussion now has been renamed to 'SQL', in case you didn't notice. On 20 July, 16:14, AB wrote: Check the very top of your module - it says: Option Explicit If you delete that, you won't get the error BUT it's is veeeeery strongly recomended to define all the variables - it will help you big time. On 20 July, 16:10, gab1972 wrote: I have a for/next loop such as this... On Error Resume Next * * i = 5 * * For col = 1 To 7 Step 2 * * * * AppPerWS.Range(Cells(30, col), Cells(54, col)).Copy * * * * AppendWS.Range("A" & i).PasteSpecial Paste:=xlPasteValues, _ * * * * * * * * * * * * * * * * * * * * * * *Operation:=xlNone, _ * * * * * * * * * * * * * * * * * * * * * * *SkipBlanks:=False, _ * * * * * * * * * * * * * * * * * * * * * * *Transpose:=True * * * * Application.CutCopyMode = False * * * * i = i + 1 * * Next col The problem is that an compile error pops up that says "variable not defined" *I didn't think you had to define these...do you? Any clue??- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
for next coding wants me to define variable...
On Jul 20, 11:16*am, AB wrote:
BTW - did you manage to sort the .field issue? The discussion now has been renamed to 'SQL', in case you didn't notice. On 20 July, 16:14, AB wrote: Check the very top of your module - it says: Option Explicit If you delete that, you won't get the error BUT it's is veeeeery strongly recomended to define all the variables - it will help you big time. On 20 July, 16:10, gab1972 wrote: I have a for/next loop such as this... On Error Resume Next * * i = 5 * * For col = 1 To 7 Step 2 * * * * AppPerWS.Range(Cells(30, col), Cells(54, col)).Copy * * * * AppendWS.Range("A" & i).PasteSpecial Paste:=xlPasteValues, _ * * * * * * * * * * * * * * * * * * * * * * *Operation:=xlNone, _ * * * * * * * * * * * * * * * * * * * * * * *SkipBlanks:=False, _ * * * * * * * * * * * * * * * * * * * * * * *Transpose:=True * * * * Application.CutCopyMode = False * * * * i = i + 1 * * Next col The problem is that an compile error pops up that says "variable not defined" *I didn't think you had to define these...do you? Any clue??- Hide quoted text - - Show quoted text - I have Option Explicit at the top of every module. Just for giggles I tried to see if that was it as you suggested, and it was. However, I can't remember where, but someone told me to put Option Explicit at the top of every module...so I do. I fixed it by defining the For/ Next variables as integers. Worked with no problems. Regarding the .fields issue...I couldn't figure it out even going through and recording a macro. I never did get an option to start at a different column. As a work around I had Excel pull the entire table into a staging sheet (which I was already doing) and then I just had Excel manipulate the data that I needed and ignore the rest. It's kind of cheating in the sense of learning...but this is for work and I need whatever works for me. =) Thanks for all your help btw. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
for next coding wants me to define variable...
DIM Source as Range
On Error Resume Next i = 5 For col = 1 To 7 Step 2 WITH AppPerWS SET source = .Range(.Cells(30, col), .Cells(54, col)) ' note the extra '.' 's !!! END WITH with source AppendWS.Range("A" & i).RESIZE(.Rows.Count, .Columns.Count).Value= ..Value i = i + 1 Next col "gab1972" wrote in message ... I have a for/next loop such as this... On Error Resume Next i = 5 For col = 1 To 7 Step 2 AppPerWS.Range(Cells(30, col), Cells(54, col)).Copy AppendWS.Range("A" & i).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True Application.CutCopyMode = False i = i + 1 Next col The problem is that an compile error pops up that says "variable not defined" I didn't think you had to define these...do you? Any clue?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define Variable Name using VBA | Excel Discussion (Misc queries) | |||
Define a Variable | Excel Programming | |||
define variable range | Excel Programming | |||
Using a Variable to Define Series | Charts and Charting in Excel | |||
How to define variable | Excel Programming |