Transferring a "score" achieved to an excel cell using offset and count...

[DogDee]

Hi all new to this forum, I have a headache of code here I do hope someone can help me out a little with it.

Within my program I have a scoring system using a counter determined by an answer i.e. if a combobox value = yes then add one to the counter.

A percentage is then calculated and displayed on an output worksheet. The answers are displayed on the output sheet and stored in a table format, when another set of results is entered it overwrites the output sheet and stores on the next line of the table using:

Dim e As Range, el As Range                             
Set e = Worksheets("SHEETNAME").Range("A3:AM3").CurrentRegion    
Set el = e.Offset(e.Rows.Count, 0)

I am using arrays to allow the user to pick a row from results in the table and copy across and overwrite the output sheet.
I want the percentage calculated to display in the end available cell ie. AK3then AK4 coinciding with the results on the same row(at the end) this is calculated within a separate sub function which creates the problem.
I am using:

Set e = Worksheets("SHEETNAME").Range("AN3").CurrentRegion
Set el = e.Offset(e.Rows.Count, 0)
el.Cells(1).Value = Score

but this puts it on the next line i.e. A4. It must be due to there already being results on that row so its counting this then adding to the next one. I know i need to tweak the

e.Offset(e.Rows.Count, 0)

But I cannot figure out how to.
I have tried to attack the issue using

Set e = Worksheets("Letter").Range("AN3:AN600").CurrentRegion
Set el = e.Offset(e.Rows.Count, 39)
el.Cells(1).Value = Score
ActiveCell.Cut
Range("AN3:AN600").Offset(-1, 0).Select
ActiveSheet.Paste

But this also does not work can someone help me out? I know I am nearly there so frustrating!!!

Thanks

[westconn1]

i have not figured out where you are trying to put the new data

Set el = e.Offset(e.Rows.Count, 39)

this sets a range the same size as e, not a single cell
msgbox el.address

[DogDee]

i have not figured out where you are trying to put the new data

this sets a range the same size as e, not a single cell
msgbox el.address

I have used 39 as the cell number as it moves the data across that many cells but it's on the row below where i want the data to be but in the correct column. That is why i tried using the offset -1 to bring the data up a row.

Hope that makes sense.

I do not understand this coding below, can you further explain please?

msgbox el.address

Thanks

[vbfbryce]

Are you trying to put the result in the first available cell to the right of the data in a particular row?

Does each row contain the same number of filled columns, or does the number vary?

[DogDee]

Are you trying to put the result in the first available cell to the right of the data in a particular row?

Yes in the cell to the right of the row, but the row changes by adding another set of results ie. adds to the next row (results 1 in row 3, results 2 in row 4 etc)

Does each row contain the same number of filled columns, or does the number vary?

No not actual filled columns (dependent on results) but the column letter will stay the same all the time for the data to be put in.

[vbfbryce]

So when you say this:

"I am using arrays to allow the user to pick a row from results in the table and copy across and overwrite the output sheet."

you mean that by picking a row in the table they are determining which row in the spreadsheet to write to (or overwrite)?

For example, whatever they picked in the table tells your app to overwrite row 17, for example, on the output spreadsheet. So you need to go to the first empty cell in row 17 and put a calculated value in that cell.

Sound right, or did I misunderstand something?

[westconn1]

msgbox el.address

it would just demonstrate the address of the range el you set, showing the included cells

[DogDee]

So when you say this:

"I am using arrays to allow the user to pick a row from results in the table and copy across and overwrite the output sheet."

you mean that by picking a row in the table they are determining which row in the spreadsheet to write to (or overwrite)?

For example, whatever they picked in the table tells your app to overwrite row 17, for example, on the output spreadsheet. So you need to go to the first empty cell in row 17 and put a calculated value in that cell.

Sound right, or did I misunderstand something?

Yes and no,

No because by selecting a row on the table and utilising the array functions the whole of the output sheet changes in accordance with the results from that one row in the table. The table is used for storing the data and the output sheet is used to show one row of data in a user friendly format.

Yes because it's the first empty cell in that row within the table that I want the calculated value to be written to.

I think the problem arises from the fact that when results are inputted they are written to the table first, from the table the results are then written to the output sheet and the calculated is created from these results on the output sheet. I then want this to be added to the first empty cell in that corresponding row in the table (where the results have already been written) and I think this is why I am struggling to get it to write to the same row (the coding keeps adding the calculated to the next row and just puts that data in on that row nothing else will write to it, anything else skips to next row).


Hope this makes sense.

Thanks

[vbfbryce]

Ok, I think I gotcha now.

Could you attach more of your code to give us a better view of how you're trying to do it?

[DogDee]

Hi

Just an update, I solved it by using the same coding used to write to the output sheet and change it to write to the table sheet directly.