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 wrote:
>
> 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
|
Similar Threads
