Loading data in tables - Again :-(

  • Thread starter Thread starter coleenholley
  • Start date Start date
C

coleenholley

EEEK! I have eight columns and 18 rows in a table. I need to populate the table and run calculations on four of the columns andfor totals in a footer. Here is some of the code I am using to populate my table
Dim ls_county, ls_gas, ls_gasohol, ls_tot_gal, ls_cty_rate, ls_st_rate As Strin
Dim ls_sum_gas, ls_sum_gasohol, ls_sum_tot_gas, ls_sum_cty_tax, ls_sum_st_tax As Strin

Function calc_gallons(
I have to do this set of code for 17 rows! Any suggestion on how to do it easier? Can I do a for/Next loop? I need to make sure that the variables go into the correct column/row(cell) so I don't know that there is any way around doing code of this sort for all 8 columns and all 17 rows..
ls_gas = CInt(tbl_worksheet1.Rows(1).Cells(1).Text
ls_gasohol = CInt(tbl_worksheet1.Rows(1).Cells(2).Text
ls_tot_gal = CInt(ls_gas) + CInt(ls_gasohol
tbl_worksheet1.Rows(1).Cells(3).Text = ls_tot_ga

This set of code is only set to return the total for the first row into row 18 - I need to SUM all 17 rows for each column in the last row as overall totals... I'm terrible at For/Next - any suggestions
ls_sum_gas = CInt(ls_gas
tbl_worksheet1.Rows(18).Cells(1).Text() = ls_sum_ga

End Functio

Thanks - I need all the help I can get :-

Coleen
 
Hi Coleen,

Change the function; pass in the column name or ordinal number and then make
the function more generic, doing the calc for each of the passed in cols.

Summing the rows should be trivial - pass that back also. BTW, if you pass
variables into the function byref, they will return the changed value.

HTH,

Bernie Yaeger

EEEK! I have eight columns and 18 rows in a table. I need to populate
Click to expand...
the table and run calculations on four of the columns andfor totals in a
footer. Here is some of the code I am using to populate my table:
Dim ls_county, ls_gas, ls_gasohol, ls_tot_gal, ls_cty_rate, ls_st_rate As String
Dim ls_sum_gas, ls_sum_gasohol, ls_sum_tot_gas, ls_sum_cty_tax, ls_sum_st_tax As String

Function calc_gallons()
I have to do this set of code for 17 rows! Any suggestion on how to do it
Click to expand...
easier? Can I do a for/Next loop? I need to make sure that the variables
go into the correct column/row(cell) so I don't know that there is any way
around doing code of this sort for all 8 columns and all 17 rows...
ls_gas = CInt(tbl_worksheet1.Rows(1).Cells(1).Text)
ls_gasohol = CInt(tbl_worksheet1.Rows(1).Cells(2).Text)
ls_tot_gal = CInt(ls_gas) + CInt(ls_gasohol)
tbl_worksheet1.Rows(1).Cells(3).Text = ls_tot_gal

This set of code is only set to return the total for the first row into
Click to expand...
row 18 - I need to SUM all 17 rows for each column in the last row as
overall totals... I'm terrible at For/Next - any suggestions?
ls_sum_gas = CInt(ls_gas)
tbl_worksheet1.Rows(18).Cells(1).Text() = ls_sum_gas

End Function

Thanks - I need all the help I can get :-)

Coleen
Click to expand...
Community Website: http://www.dotnetjunkies.com/newsgroups/
 
Hi Coleen,

You mean something as?
\\\
dim i as integer
dim y as integer
For i = 0 to 17
for y = 0 to 7
tbl_worksheet.rows(17)(y)=tbl_worksheet.rows(i)(y)
next
next
///
the rowindex starts at 0 so row 18 is 17
It has all to be numeric of course otherwise you have to test that

I hope this helps,

Cor
EEEK! I have eight columns and 18 rows in a table. I need to populate
Click to expand...
the table and run calculations on four of the columns andfor totals in a
footer. Here is some of the code I am using to populate my table:
Dim ls_county, ls_gas, ls_gasohol, ls_tot_gal, ls_cty_rate, ls_st_rate As String
Dim ls_sum_gas, ls_sum_gasohol, ls_sum_tot_gas, ls_sum_cty_tax, ls_sum_st_tax As String

Function calc_gallons()
I have to do this set of code for 17 rows! Any suggestion on how to do it
Click to expand...
easier? Can I do a for/Next loop? I need to make sure that the variables
go into the correct column/row(cell) so I don't know that there is any way
around doing code of this sort for all 8 columns and all 17 rows...
ls_gas = CInt(tbl_worksheet1.Rows(1).Cells(1).Text)
ls_gasohol = CInt(tbl_worksheet1.Rows(1).Cells(2).Text)
ls_tot_gal = CInt(ls_gas) + CInt(ls_gasohol)
tbl_worksheet1.Rows(1).Cells(3).Text = ls_tot_gal

This set of code is only set to return the total for the first row into
Click to expand...
row 18 - I need to SUM all 17 rows for each column in the last row as
overall totals... I'm terrible at For/Next - any suggestions?
 
Thanks - actually, I got that far:

For i = 1 To 17
ls_tot_gal = CInt(ls_gas) + CInt(ls_gasohol)
ls_sum_tot_gas = ls_sum_tot_gas + CInt(ls_tot_gal)

tbl_worksheet1.Rows(i).Cells(3).Text() = ls_tot_gal
Next i
tbl_worksheet1.Rows(i).Cells(3).Text() = ls_sum_tot_gas

The problem I am having is that I need my calculations to go both across and down. The calculations do run for both, but the calculations across which is

ls_sum_tot_gas = ls_sum_tot_gas + CInt(ls_tot_gal)

don't add up each individual line...it adds the amount from tbl_worksheet1.Rows(1).Cells(1) +
tbl_worksheet1.Rows(1).Cells(2) and puts that value in EVERY row total. Each row has a different value and needs to be calculated. The sum total at the bottom is working great- it takes ALL the values (which in this case is 14500) and adds them up to grand total of 246500. Any suggestions on how to get the totals for each row to calculate properly? TIA - Coleen
 
You mean something like this?

ls_gas = CInt(tbl_worksheet1.Rows(1).Cells(1).Text)
ls_gasohol = CInt(tbl_worksheet1.Rows(1).Cells(2).Text)

For i = 1 To 17
ls_tot_gal = CInt(ls_gas) + CInt(ls_gasohol)
ls_sum_tot_gas = ls_sum_tot_gas + CInt(ls_tot_gal)

tbl_worksheet1.Rows(i).Cells(3).Text() = ls_tot_gal
Next j
tbl_worksheet1.Rows(i).Cells(3).Text() = ls_sum_tot_gas

only using cell names instead of integers? I guess I'm not quite sure what you mean - could you please give me an example? Thanks very much :-) Coleen
 
Hi Colleen,

Although there was a time that I was also working in oil, I dont have to do
with that, so I keep it with indexes.

I take my original sample and add one row.

\\\
dim i as integer
dim y as integer
For i = 0 to 17
for y = 1 to 6
tbl_worksheet.rows(17)(y)=tbl_worksheet.rows(i)(y)
tb_workstheer.rows(i)(0)=tbl_worksheet.rows(i)(y)
next
next
///
This adds all items horizontaly into the first column
This adds all items inclusieve the first verticaly into the last row

(I did not check it)

I think you should give it a try.

Cor
 
Back
Top