Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
looping through an giving values to cells in vba
i am trying to write values from an array into a row of cells, ive written a
sub which i pass a parameter array to which should loop through a row of cells and put the next value in the array into the cell, here is what i think should work (but doesnt) Public Sub GenerateHeadings(headerNo As Integer, lastRow As Integer, ParamArray ParameterArray() As Variant) 'pass in the headings that you want to have in an array and then out them into 'each cell formating as required Dim counter As Integer Dim result As Integer For counter = 0 To UBound(ParameterArray, 1) Worksheets("Campaigns by Channel").Cells(counter, 3).Value = ParameterArray(counter) Next counter end sub this doesnt work, infact the stumbling block is when i use a variable as the cells index number. why is that not working when Dim Counter As Integer For Counter = 1 To 20 Worksheets("Sheet1").Cells(Counter, 3).Value = Counter Next Counter (from the help file) does? i know that my parameter array is workign fine, its just that index variable. Has been frustrating me since yesterday, am going to change tact and do some SQL! any help will be gratfully received Amit |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
looping through an giving values to cells in vba
counter starting at zero is O.K. for the array, but not for the range
reference. You need something like: Worksheets("Campaigns by Channel").Cells(counter+1, 3).Value = because the lowest cells reference is Cells(1, 1) -- Gary''s Student "DowningDevelopments" wrote: i am trying to write values from an array into a row of cells, ive written a sub which i pass a parameter array to which should loop through a row of cells and put the next value in the array into the cell, here is what i think should work (but doesnt) Public Sub GenerateHeadings(headerNo As Integer, lastRow As Integer, ParamArray ParameterArray() As Variant) 'pass in the headings that you want to have in an array and then out them into 'each cell formating as required Dim counter As Integer Dim result As Integer For counter = 0 To UBound(ParameterArray, 1) Worksheets("Campaigns by Channel").Cells(counter, 3).Value = ParameterArray(counter) Next counter end sub this doesnt work, infact the stumbling block is when i use a variable as the cells index number. why is that not working when Dim Counter As Integer For Counter = 1 To 20 Worksheets("Sheet1").Cells(Counter, 3).Value = Counter Next Counter (from the help file) does? i know that my parameter array is workign fine, its just that index variable. Has been frustrating me since yesterday, am going to change tact and do some SQL! any help will be gratfully received Amit |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
looping through an giving values to cells in vba
so simple but so frustrating!!!!
thanks gary's student! "Gary''s Student" wrote: counter starting at zero is O.K. for the array, but not for the range reference. You need something like: Worksheets("Campaigns by Channel").Cells(counter+1, 3).Value = because the lowest cells reference is Cells(1, 1) -- Gary''s Student "DowningDevelopments" wrote: i am trying to write values from an array into a row of cells, ive written a sub which i pass a parameter array to which should loop through a row of cells and put the next value in the array into the cell, here is what i think should work (but doesnt) Public Sub GenerateHeadings(headerNo As Integer, lastRow As Integer, ParamArray ParameterArray() As Variant) 'pass in the headings that you want to have in an array and then out them into 'each cell formating as required Dim counter As Integer Dim result As Integer For counter = 0 To UBound(ParameterArray, 1) Worksheets("Campaigns by Channel").Cells(counter, 3).Value = ParameterArray(counter) Next counter end sub this doesnt work, infact the stumbling block is when i use a variable as the cells index number. why is that not working when Dim Counter As Integer For Counter = 1 To 20 Worksheets("Sheet1").Cells(Counter, 3).Value = Counter Next Counter (from the help file) does? i know that my parameter array is workign fine, its just that index variable. Has been frustrating me since yesterday, am going to change tact and do some SQL! any help will be gratfully received Amit |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
looping through an giving values to cells in vba
Don't beat your self up over this one.
It's the developers of VBA that were inconsistent. Why did they start arrays at zero and rows & columns at one?? -- Gary's Student "DowningDevelopments" wrote: so simple but so frustrating!!!! thanks gary's student! "Gary''s Student" wrote: counter starting at zero is O.K. for the array, but not for the range reference. You need something like: Worksheets("Campaigns by Channel").Cells(counter+1, 3).Value = because the lowest cells reference is Cells(1, 1) -- Gary''s Student "DowningDevelopments" wrote: i am trying to write values from an array into a row of cells, ive written a sub which i pass a parameter array to which should loop through a row of cells and put the next value in the array into the cell, here is what i think should work (but doesnt) Public Sub GenerateHeadings(headerNo As Integer, lastRow As Integer, ParamArray ParameterArray() As Variant) 'pass in the headings that you want to have in an array and then out them into 'each cell formating as required Dim counter As Integer Dim result As Integer For counter = 0 To UBound(ParameterArray, 1) Worksheets("Campaigns by Channel").Cells(counter, 3).Value = ParameterArray(counter) Next counter end sub this doesnt work, infact the stumbling block is when i use a variable as the cells index number. why is that not working when Dim Counter As Integer For Counter = 1 To 20 Worksheets("Sheet1").Cells(Counter, 3).Value = Counter Next Counter (from the help file) does? i know that my parameter array is workign fine, its just that index variable. Has been frustrating me since yesterday, am going to change tact and do some SQL! any help will be gratfully received Amit |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum up values only in cells that are color filled? | Excel Worksheet Functions | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Looping between sheets picking up values on the way | Excel Worksheet Functions | |||
Is there way to enter multiple values into excel cells w/ a form? | Excel Discussion (Misc queries) | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions |