| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data written to wrong cells
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.Row s.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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data written to wrong cells
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.Row s.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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data written to wrong cells
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. "Dave Peterson" wrote: 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.Row s.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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I input these formulas into cells that I want written in? | Excel Worksheet Functions | |||
| how to account the number of cells which is bank and written? | Excel Discussion (Misc queries) | |||
| Making Cells Automatically Add #'s Instead of being written over | Excel Programming | |||
| How to protect single cell or cells range from being over-written? | Excel Programming | |||
| Formula produces wrong result when data cells filled programmatically | Excel Programming | |||
Linear Mode
