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

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

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

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