0

Hi I hope someone can shed some light. I have been designing a user form in Excel. I now want the value to transfer to Sheet3 in column 9 of the next available empty space instead of the active sheet. Can someone tell me how can I change my code below to go to Sheet 3? Many thanks for your help

Private Sub Command2_Click()
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 9).Value = Explanation2.Value
Unload Me
End Sub
SL22122406
  • 35
  • 4
  • Fully qualify your Range and Cells references. Note that `CountA` won't take into account intermediate empty rows, so that you risk to overwrite existing values instead to address the first empty cell after the column's `lastRow` - there are numerous examples at SO, see e.g. [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – T.M. May 27 '22 at 18:11

0 Answers0