VB writing on line two when not on active sheet.

[Dsr122076]

Hi Guys I have a glitch in my program. It works fine only when i run it from the developer and the selected sheet is active. However when I launch it on the wrong sheet, it writes to the correct sheet but does not write in the correct row. Instead it writes to the second row everytime, deleting whatever was in there. I do not understand how it works correctly when the sheet it is writing to is the active sheet but incorrectly when the sheet it writes to is not the one with the focus. Below is a snippet of the code.


Info: Sheet 1 contains 2 command buttons. 1 launches userform1 and the other launches userform 2.
Sheet 2 contains info ( i have 16 pages of information to fill)

Example: When I am on sheet1 and launch userform1, which is the one that I use to enter text, The code works, and writes to sheet 2. however it will always write on row 2 regardless of if there is information there or not. ( ineed it to write to the next empty row)

However, if I am on sheet 2 and launch the userform1 it will correctly input the information on sheet 2 in the following empty row.



How do I get it to always find the next empty row on any sheet that I need it to go to? If need be, I can post the entire code. I am new to this so any help is appreciated.








FinalRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


'Writes name to last row in first column for TACG ASSembly
If TACGASS.Value = True Then Sheet2.Cells(FinalRow, 1) = UserForm1.subject.Text
'Inputs shift preference into second Column for TACG ASSembly
If TACGASS.Value = True Then Sheet2.Cells(FinalRow, 2) = UserForm1.Shift.Text
'Inputs notes into third column for TACG ASSembly
If TACGASS.Value = True Then Sheet2.Cells(FinalRow, 3) = UserForm1.Notes.Text

 

[mikerickson]

The value for FinalRow has to be derived from the same sheet that it is used on.

FinalRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

 

[Dsr122076]

The value for FinalRow has to be derived from the same sheet that it is used on.

FinalRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Ok so based on this information, I will have to set active the sheet i need to write to, then set the final row value, which then will allow it to write correctly......
Tested it works now. here is the corrected version.

'sets active sheet and sets value for final row.
If TACGASS.Value = True Then Sheet2.Activate
FinalRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Writes name to last row in first column for TACG ASSembly
If TACGASS.Value = True Then Sheet2.Cells(FinalRow, 1) = UserForm1.subject.Text
'Inputs shift preference into second Column for TACG ASSembly
If TACGASS.Value = True Then Sheet2.Cells(FinalRow, 2) = UserForm1.Shift.Text
'Inputs notes into third column for TACG ASSembly
If TACGASS.Value = True Then Sheet2.Cells(FinalRow, 3) = UserForm1.Notes.Text


Thanks.

 

[mikerickson]

No.
You do not need to activate any sheet. Adding the qualification Sheet2. to the Final Row line got that value from Sheet2 rather than from the Active Sheet.

 

[Dsr122076]

No.
You do not need to activate any sheet. Adding the qualification Sheet2. to the Final Row line got that value from Sheet2 rather than from the Active Sheet.

Ah ok. so I can do that for each page and that would work. So the correction is:
FinalRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

where sheet2 = sheet# that i am currrently trying to fill on. Thanks.