| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop help
Hi
I am new to VBA and am trying to learn by disecting existing code and appying to my projects. Part of the code that I have written so far is: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Some Code 'copy the data to the database ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value ws.Cells(iRow, Me.txtStart.Value + 5).Value = Me.txt3.Value 'clear the data Me.txt1.Value = "" Me.txt2.Value = "" Me.txtStart.Value = "" Me.txt3.Value = "" End Sub This copies the txt3 value to the correct cell. What I would like to do is add a txtStop and have the code loop through and copy to all of the cells in irow in the columns from txtStart +5 to txtStop+5. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop help
You seem to have dropped some very critical code in your Some Code section.
What is Me referring to? A form? Are txtStart and txtStop and txt... text boxes on the form or ??? txtStart + 5 and txtStop + 5 refer to cell addresses or ??? "Henry" wrote: Hi I am new to VBA and am trying to learn by disecting existing code and appying to my projects. Part of the code that I have written so far is: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Some Code 'copy the data to the database ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value ws.Cells(iRow, Me.txtStart.Value + 5).Value = Me.txt3.Value 'clear the data Me.txt1.Value = "" Me.txt2.Value = "" Me.txtStart.Value = "" Me.txt3.Value = "" End Sub This copies the txt3 value to the correct cell. What I would like to do is add a txtStop and have the code loop through and copy to all of the cells in irow in the columns from txtStart +5 to txtStop+5. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop help
ws.Cells(iRow, 1).Value = Me.tx1t.Value
ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value for k = clng(Me.txtStart.Value) +5 to clng(me.txtStop.Value) + 5 ws.Cells(iRow, k).Value = Me.txt3.Value Next or if you mean txt3 and beyond ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value kk = 3 for k = clng(Me.txtStart.Value) +5 to clng(me.txtStop.Value) + 5 ws.Cells(iRow, k).Value = Me.controls("txt" & kk).Value kk = kk + 1 Next -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi I am new to VBA and am trying to learn by disecting existing code and appying to my projects. Part of the code that I have written so far is: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Some Code 'copy the data to the database ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value ws.Cells(iRow, Me.txtStart.Value + 5).Value = Me.txt3.Value 'clear the data Me.txt1.Value = "" Me.txt2.Value = "" Me.txtStart.Value = "" Me.txt3.Value = "" End Sub This copies the txt3 value to the correct cell. What I would like to do is add a txtStop and have the code loop through and copy to all of the cells in irow in the columns from txtStart +5 to txtStop+5. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop help
thanks for the replies---I went with Toms first suggestion and its working
fine. I get a pop up at the end that is a OK click----can you tell me where that is coming from? Thanks! "Tom Ogilvy" wrote: ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value for k = clng(Me.txtStart.Value) +5 to clng(me.txtStop.Value) + 5 ws.Cells(iRow, k).Value = Me.txt3.Value Next or if you mean txt3 and beyond ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value kk = 3 for k = clng(Me.txtStart.Value) +5 to clng(me.txtStop.Value) + 5 ws.Cells(iRow, k).Value = Me.controls("txt" & kk).Value kk = kk + 1 Next -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi I am new to VBA and am trying to learn by disecting existing code and appying to my projects. Part of the code that I have written so far is: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Some Code 'copy the data to the database ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value ws.Cells(iRow, Me.txtStart.Value + 5).Value = Me.txt3.Value 'clear the data Me.txt1.Value = "" Me.txt2.Value = "" Me.txtStart.Value = "" Me.txt3.Value = "" End Sub This copies the txt3 value to the correct cell. What I would like to do is add a txtStop and have the code loop through and copy to all of the cells in irow in the columns from txtStart +5 to txtStop+5. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop help
I found it nvermind
Thanks for the help "Henry" wrote: thanks for the replies---I went with Toms first suggestion and its working fine. I get a pop up at the end that is a OK click----can you tell me where that is coming from? Thanks! "Tom Ogilvy" wrote: ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value for k = clng(Me.txtStart.Value) +5 to clng(me.txtStop.Value) + 5 ws.Cells(iRow, k).Value = Me.txt3.Value Next or if you mean txt3 and beyond ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value kk = 3 for k = clng(Me.txtStart.Value) +5 to clng(me.txtStop.Value) + 5 ws.Cells(iRow, k).Value = Me.controls("txt" & kk).Value kk = kk + 1 Next -- Regards, Tom Ogilvy "Henry" wrote in message ... Hi I am new to VBA and am trying to learn by disecting existing code and appying to my projects. Part of the code that I have written so far is: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Some Code 'copy the data to the database ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value ws.Cells(iRow, Me.txtStart.Value + 5).Value = Me.txt3.Value 'clear the data Me.txt1.Value = "" Me.txt2.Value = "" Me.txtStart.Value = "" Me.txt3.Value = "" End Sub This copies the txt3 value to the correct cell. What I would like to do is add a txtStop and have the code loop through and copy to all of the cells in irow in the columns from txtStart +5 to txtStop+5. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop help
Maybe....
'copy the data to the database With ws .Cells(iRow, 1).Value = Me.txt1.Value .Cells(iRow, 2).Value = Me.txt2.Value .Cells(iRow, 3).Value = Me.txtStart.Value .Range(.Cells(iRow, Me.txtStart.Value + 5), _ .Cells(iRow, Me.txtStop.Value + 5)).Value = Me.txt3.Value End With Is it me.txt1 or me.tx1t??? Henry wrote: Hi I am new to VBA and am trying to learn by disecting existing code and appying to my projects. Part of the code that I have written so far is: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Some Code 'copy the data to the database ws.Cells(iRow, 1).Value = Me.tx1t.Value ws.Cells(iRow, 2).Value = Me.txt2.Value ws.Cells(iRow, 3).Value = Me.txtStart.Value ws.Cells(iRow, Me.txtStart.Value + 5).Value = Me.txt3.Value 'clear the data Me.txt1.Value = "" Me.txt2.Value = "" Me.txtStart.Value = "" Me.txt3.Value = "" End Sub This copies the txt3 value to the correct cell. What I would like to do is add a txtStop and have the code loop through and copy to all of the cells in irow in the columns from txtStart +5 to txtStop+5. Thanks -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find loop doesn't loop | Excel Discussion (Misc queries) | |||
| How do I create a For loop within a For loop? | Excel Programming | |||
| For/Loop skipping one value in loop only | Excel Programming | |||
| Worksheet_Change - loop within a loop | Excel Programming | |||
| HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
Hybrid Mode
