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

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

Loop help
 

No need for a loop. Leverage the XL object model and use something
like the untested
ws.Cells(iRow, Me.txtStart.Value + 5) _
.Resize(1,Me.txtStop.Value -Me.txtStart.Value +1).Value = _
Me.txt3.Value

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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

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

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

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

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