-1

I am attempting to create a user form that will propagate the first available row of the excel sheet with information. I am struggling to get the range correct, and the form is currently showing 'Subscript out of range' ; 'Runtime error 9'. There also seems to be a problem with the 'if' statement, but following the standard advice online doesn't seem to have helped solve the problem.

If anyone has any idea where I'm going wrong (I'm very new to this) that would be great. 

Private Sub UserForm_Initialize()  
BusinessAreaBox.List = Array("option one", "option two")  
End Sub  

Private Sub CommandButton1_Click()  
Dim RowCount As Long  
RowCount = Sheets("Sheet1").Range.Sheets("Sheet1").Cells(2, "A")  
With ThisWorkbook.Sheets("Sheet1").Range("A2")  
.Offset(RowCount, 0).Value = BusinessArea1.Value  
.Offset(RowCount, 1).Value = BusinessContact1.Value  
.Offset(RowCount, 2).Value = LPSContact1.Value  
.Offset(RowCount, 4).Value = ProjectedFTE1.Value  
.Offset(RowCount, 5).Value = DateOfMostRecentMeeting1.Value  
.Offset(RowCount, 6).Value = FTEComment1.Value  
.Offset(RowCount, 7).Value = ProposedMove1.Value  
.Offset(RowCount, 8).Value = DeskUtilisation1.Value  
.Offset(RowCount, 9).Value = OtherComment1.Value  
.Offset(RowCount, 10).Value = Actions1.Value  
If RegularMeeting1.Value = True Then  
.Offset(RowCount, 3).Value = "Yes"  
Else  
.Offset(RowCount, 3).Value = "No"  
End If  
RegularMeeting1.Value = True Or False  
End With  
End Sub  

Private Sub CommandButton2_Click()  
  Unload Me  
End Sub
JessicaLucy
  • 1
  • 2
    What are you trying to do with this line : `RowCount = Sheets("Sheet1").Range.Sheets("Sheet1").Cells(2, "A")`? I'm guessing that this is the one that is in yellow? – R3uK Jan 31 '17 at 16:00
  • That is the source of the error. I'm trying to get it to populate the first set of information onto "sheet 1", row 2, and from that point onwards to the first available line. I must admit that I took that line from an example online but I can't see what the line should read - any ideas would be much appreciated. – JessicaLucy Jan 31 '17 at 16:07
  • [Run-time error '9': Subscript out of range](http://stackoverflow.com/documentation/vba/8917/vba-run-time-errors/27748/run-time-error-9-subscript-out-of-range#t=201701311609360342424) – Mathieu Guindon Jan 31 '17 at 16:09
  • 1
    Wait a sec, you're trying to *write* to `Sheet1!A2`? Then why are you *reading* from it (in a nonsensical contorted can't-possibly-work-way)? If you want to *write* to a cell, then the cell's value needs to be on the *left-hand-side* of the assignment; it's no different than reading/writing from/to a variable, really. – Mathieu Guindon Jan 31 '17 at 16:16
  • @Mat'sMug; if `RowCount = 0` then she's writing to `Sheet1!A2` at `.Offset(RowCount, 0).Value` if `RowCount <> 0` she's not. – simpLE MAn Jan 31 '17 at 16:47
  • @simpLEMAn yeah, well, *one* problem is that `RowCount` isn't being assigned with a legal statement in the first place. – Mathieu Guindon Jan 31 '17 at 16:52
  • @Mat'sMug; Oh, didn't see the `Sheets("Sheet1").Range.Sheets("Sheet1")`. This should be better - `RowCount = Sheets("Sheet1").Cells(2, "A").Value2` – simpLE MAn Jan 31 '17 at 16:55

2 Answers2

0

I guess you may be after this

Private Sub CommandButton1_Click()
    With ThisWorkbook.Sheets("Sheet1")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 11).Value = Array(BusinessArea1.Value, _
                                                                              BusinessContact1.Value, _
                                                                              LPSContact1.Value, _
                                                                              IIf(RegularMeeting1.Value, "Yes", "No"), _
                                                                              ProjectedFTE1.Value, _
                                                                              DateOfMostRecentMeeting1.Value, _
                                                                              FTEComment1.Value, _
                                                                              ProposedMove1.Value, _
                                                                              DeskUtilisation1.Value, _
                                                                              OtherComment1.Value, _
                                                                              Actions1.Value)

    End With
    RegularMeeting1.Value = True Or False '<--| what is this supposed to do?
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
0

Try writing to the cells this way. You need to active the sheet and cell first.

Dim myArr As Variant

myArr = Array(BusinessArea1.Value, _
              LPSContact1.Value, _
              ProjectedFTE1.Value, _
              DateOfMostRecentMeeting1.Value, _
              FTEComment1.Value, _
              ProposedMove1.Value, _
              DeskUtilisation1.Value, _
              OtherComment1.Value, _
              Actions1.Value)

sheet1.activate
sheet1.range("A2").activate
for I = 0 to ubound(myArr)
    activecell.value = myArr(I)
    activecell.Offset(1,0).activate
Next I
R3uK
  • 14,417
  • 7
  • 43
  • 77
John Muggins
  • 1,198
  • 1
  • 6
  • 12
  • 3
    It would be nice to see SO answers that stop spreading the idea that using `.Activate` and relying on `ActiveCell` (and `Selection`) when you don't *have to* is anywhere near a good idea. – Mathieu Guindon Jan 31 '17 at 16:42
  • 3
    I second Mat's Mug on that - see **[How to avoid using Select in Excel VBA macros](http://stackoverflow.com/a/10717999/2687063)** – simpLE MAn Jan 31 '17 at 16:52