Data written to wrong cells

[clpow]

New to Excel Programming so bare with me if this seems dumb. I have an
excel spreadsheet used to collect data from a userform. Once the data is
entered the user clicks a button to add the record to the spreadsheet. Every
thing works fine except the data gets written to the wrong cells. For
example, the data should be written to cells a1-o1 but actually gets written
to p1-t1. Stange thing about it is that the information that belongs in the
first cell is correct it is all information after that which is incorrect. I
cannot figure out why. At one point this worked correctly but now it does
not and I do not understand what I've screwed up. Here is the code for the
add button. I appreciate any help.

Private Sub cmdAdd_Click()
Dim RowCount As Long
Dim ctl As Control

ActiveSheet.Unprotect
RowCount = Worksheets("Semap3").Range("A1").CurrentRegion.Rows.Count

'copy the data to the database
With Worksheets("Semap3").Range("A1")

..Offset(RowCount, 0).Value = Me.cboCaseworker.Value
..Offset(RowCount, 1).Value = Me.txtTenant.Value

If Me.chkEmployment.Value = True Then
..Offset(RowCount, 2).Value = "E"
ElseIf Me.chkWages.Value = True Then
..Offset(RowCount, 2).Value = "W"
ElseIf Me.chkSocialSecurity.Value = True Then
..Offset(RowCount, 2).Value = "S"
ElseIf Me.chkOther.Value = True Then
..Offset(RowCount, 2).Value = "O"
Else
..Offset(RowCount, 2).Value = ""
End If


If Me.optYes.Value = True Then
..Offset(RowCount, 3).Value = "Yes"
Else
Me.optNo.Value = True
..Offset(RowCount, 3).Value = "No"
End If

If Me.chkMedical.Value = True Then
..Offset(RowCount, 4).Value = "Yes"
Else
..Offset(RowCount, 4).Value = "No"
End If

If Me.chkChildCare.Value = True Then
..Offset(RowCount, 5).Value = "Yes"
Else
..Offset(RowCount, 5).Value = "No"
End If

If Me.chkDisability.Value = True Then
..Offset(RowCount, 6).Value = "Yes"
Else
..Offset(RowCount, 6).Value = "No"
End If

If Me.chkElderly.Value = True Then
..Offset(RowCount, 7).Value = "Yes"
Else
..Offset(RowCount, 7).Value = "No"
End If

If Me.chkStudent.Value = True Then
..Offset(RowCount, 8).Value = "Yes"
Else
..Offset(RowCount, 8).Value = "No"
End If

If Me.chkCurrent.Value = True Then
..Offset(RowCount, 9).Value = "Yes"
Else
..Offset(RowCount, 9).Value = "No"
End If


If Me.chkUnit.Value = True Then
..Offset(RowCount, 10).Value = "Yes"
Else
..Offset(RowCount, 10).Value = "No"
End If

If Me.optYes2.Value = True Then
..Offset(RowCount, 11).Value = "Yes"
Else
..Offset(RowCount, 11).Value = "No"
End If

If Me.optNo2.Value = True Then
..Offset(RowCount, 12).Value = "Yes"
Else
..Offset(RowCount, 12).Value = "No"
End If

..Offset(RowCount, 13).Value = Me.txtComments.Value
..Offset(RowCount, 14).Value = Me.txtDate.Value
End With


'clear the data
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
ElseIf TypeName(ctl) = "OptionBox" Then
ctl.Value = False
End If
Next ctl
ActiveSheet.Protect
ActiveWorkbook.Save
End Sub

 

[Dave Peterson]

I skinnied your code down to just this:

Option Explicit
Sub testme()
Dim RowCount As Long
With ActiveSheet.Range("A1")
RowCount = .CurrentRegion.Rows.Count
.Offset(RowCount, 0).Value = "aaa"
.Offset(RowCount, 1).Value = "bbb"
.Offset(RowCount, 2).Value = "Eeee"
.Offset(RowCount, 3).Value = "Yes"
.Offset(RowCount, 4).Value = "Yes"
.Offset(RowCount, 5).Value = "Yes"
.Offset(RowCount, 6).Value = "Yes"
.Offset(RowCount, 7).Value = "Yes"
.Offset(RowCount, 8).Value = "Yes"
.Offset(RowCount, 9).Value = "Yes"
End With
End Sub

And it worked ok for me. I don't see anything in your code that would make it
put the values in the wrong cells.

The only thing that scared me was that you refered to the Activesheet a couple
of times. I would have guessed that you wanted to use Semap3 for all those
references.

 

[clpow]

Thanks for your input. I am baffled as to why the data is written to the
wrong cell(s). I think I will scrap the whole thing and start over from
scratch as I can not find where the error is.