TextBox1.Text = rng(1, "Start_1").Text

Start_1 is a range of cells ( AU3 :AU36) defined on the active worksheet. I
want the text from TextBox1 to be placed into the Start_1 of the current
row.

I want to use Defined Names, so that over time if I have to add or subtract
columns I will not have to go back and re-point all my references. The code
I used before was TextBox1.Text = rng(1, 47).Text (this is just one of
dozens of lines code), but if I added or removed columns the references all
had to be readjusted.

It kind of sounds like you want the first empty cell in that range to get the
value from your textbox.

If that's true, then you could do something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim myStart_1_Rng As Range

Set myStart_1_Rng = Worksheets("sheet1").Range("Start_1")

If Application.CountA(myStart_1_Rng) = myStart_1_Rng.Cells.Count Then
MsgBox "No empty cells in the range!"
Exit Sub
End If

For iRow = 1 To myStart_1_Rng.Rows.Count
If IsEmpty(myStart_1_Rng.Cells(iRow, 1)) Then
myStart_1_Rng.Cells(iRow, 1).Value = Me.TextBox1.Text
Exit For
End If
Next iRow

End Sub



Patrick Simonds wrote:

TextBox1.Text = rng(1, "Start_1").Text

Start_1 is a range of cells ( AU3 :AU36) defined on the active worksheet. I
want the text from TextBox1 to be placed into the Start_1 of the current
row.

I want to use Defined Names, so that over time if I have to add or subtract
columns I will not have to go back and re-point all my references. The code
I used before was TextBox1.Text = rng(1, 47).Text (this is just one of
dozens of lines code), but if I added or removed columns the references all
had to be readjusted.

--

Dave Peterson

No, what I want to have happen is, if I am on row 12 I want the text to go
into the Start_1 on row 12. In this example start_1 would be cell AU12.

The dialog box which contains the text for Start_1 is displayed when I click
on a cell in column A. This worked well when I was using TextBox1.Text =
rng(1, 47), but now I have to have the ability to add or remove columns.


"Dave Peterson" wrote in message
...
It kind of sounds like you want the first empty cell in that range to get
the
value from your textbox.

If that's true, then you could do something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim myStart_1_Rng As Range

Set myStart_1_Rng = Worksheets("sheet1").Range("Start_1")

If Application.CountA(myStart_1_Rng) = myStart_1_Rng.Cells.Count Then
MsgBox "No empty cells in the range!"
Exit Sub
End If

For iRow = 1 To myStart_1_Rng.Rows.Count
If IsEmpty(myStart_1_Rng.Cells(iRow, 1)) Then
myStart_1_Rng.Cells(iRow, 1).Value = Me.TextBox1.Text
Exit For
End If
Next iRow

End Sub



Patrick Simonds wrote:

TextBox1.Text = rng(1, "Start_1").Text

Start_1 is a range of cells ( AU3 :AU36) defined on the active worksheet.
I
want the text from TextBox1 to be placed into the Start_1 of the current
row.

I want to use Defined Names, so that over time if I have to add or
subtract
columns I will not have to go back and re-point all my references. The
code
I used before was TextBox1.Text = rng(1, 47).Text (this is just one of
dozens of lines code), but if I added or removed columns the references
all
had to be readjusted.

--

Dave Peterson

What does it mean that you're on row 12?

Does that mean the activecell is in row 12?

dim myCell as range
on error resume next
set mycell = intersect(activecell, range("start_1"))
on error goto 0
if mycell is nothing then
'do nothing
else
mycell.value = textbox1.value
end if

maybe????????



Patrick Simonds wrote:

No, what I want to have happen is, if I am on row 12 I want the text to go
into the Start_1 on row 12. In this example start_1 would be cell AU12.

The dialog box which contains the text for Start_1 is displayed when I click
on a cell in column A. This worked well when I was using TextBox1.Text =
rng(1, 47), but now I have to have the ability to add or remove columns.

"Dave Peterson" wrote in message
...
It kind of sounds like you want the first empty cell in that range to get
the
value from your textbox.

If that's true, then you could do something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim myStart_1_Rng As Range

Set myStart_1_Rng = Worksheets("sheet1").Range("Start_1")

If Application.CountA(myStart_1_Rng) = myStart_1_Rng.Cells.Count Then
MsgBox "No empty cells in the range!"
Exit Sub
End If

For iRow = 1 To myStart_1_Rng.Rows.Count
If IsEmpty(myStart_1_Rng.Cells(iRow, 1)) Then
myStart_1_Rng.Cells(iRow, 1).Value = Me.TextBox1.Text
Exit For
End If
Next iRow

End Sub



Patrick Simonds wrote:

TextBox1.Text = rng(1, "Start_1").Text

Start_1 is a range of cells ( AU3 :AU36) defined on the active worksheet.
I
want the text from TextBox1 to be placed into the Start_1 of the current
row.

I want to use Defined Names, so that over time if I have to add or
subtract
columns I will not have to go back and re-point all my references. The
code
I used before was TextBox1.Text = rng(1, 47).Text (this is just one of
dozens of lines code), but if I added or removed columns the references
all
had to be readjusted.

--

Dave Peterson

--

Dave Peterson

Okay Start_1 is defined as AU3:AU36, so in my spreadsheet I enter data on
rows 3 through 36, A1 A2 A3 ect.

If I click on cell A12 the data I enter into TextBox1 of the dialogbox is to
be placed in Start_1 which for this row would be AU12. So the data in
TextBox1 will always go to the Start_1 of the current row.

I appreciate your time and I hope that I am explaining this more clearly,
but if not please let me know.



"Dave Peterson" wrote in message
...
What does it mean that you're on row 12?

Does that mean the activecell is in row 12?

dim myCell as range
on error resume next
set mycell = intersect(activecell, range("start_1"))
on error goto 0
if mycell is nothing then
'do nothing
else
mycell.value = textbox1.value
end if

maybe????????



Patrick Simonds wrote:

No, what I want to have happen is, if I am on row 12 I want the text to
go
into the Start_1 on row 12. In this example start_1 would be cell AU12.

The dialog box which contains the text for Start_1 is displayed when I
click
on a cell in column A. This worked well when I was using TextBox1.Text =
rng(1, 47), but now I have to have the ability to add or remove columns.

"Dave Peterson" wrote in message
...
It kind of sounds like you want the first empty cell in that range to
get
the
value from your textbox.

If that's true, then you could do something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim myStart_1_Rng As Range

Set myStart_1_Rng = Worksheets("sheet1").Range("Start_1")

If Application.CountA(myStart_1_Rng) = myStart_1_Rng.Cells.Count
Then
MsgBox "No empty cells in the range!"
Exit Sub
End If

For iRow = 1 To myStart_1_Rng.Rows.Count
If IsEmpty(myStart_1_Rng.Cells(iRow, 1)) Then
myStart_1_Rng.Cells(iRow, 1).Value = Me.TextBox1.Text
Exit For
End If
Next iRow

End Sub



Patrick Simonds wrote:

TextBox1.Text = rng(1, "Start_1").Text

Start_1 is a range of cells ( AU3 :AU36) defined on the active
worksheet.
I
want the text from TextBox1 to be placed into the Start_1 of the
current
row.

I want to use Defined Names, so that over time if I have to add or
subtract
columns I will not have to go back and re-point all my references. The
code
I used before was TextBox1.Text = rng(1, 47).Text (this is just one of
dozens of lines code), but if I added or removed columns the
references
all
had to be readjusted.

--

Dave Peterson

--

Dave Peterson

I think my last suggestion would work for this situation.

Didn't it?

Patrick Simonds wrote:

Okay Start_1 is defined as AU3:AU36, so in my spreadsheet I enter data on
rows 3 through 36, A1 A2 A3 ect.

If I click on cell A12 the data I enter into TextBox1 of the dialogbox is to
be placed in Start_1 which for this row would be AU12. So the data in
TextBox1 will always go to the Start_1 of the current row.

I appreciate your time and I hope that I am explaining this more clearly,
but if not please let me know.

"Dave Peterson" wrote in message
...
What does it mean that you're on row 12?

Does that mean the activecell is in row 12?

dim myCell as range
on error resume next
set mycell = intersect(activecell, range("start_1"))
on error goto 0
if mycell is nothing then
'do nothing
else
mycell.value = textbox1.value
end if

maybe????????



Patrick Simonds wrote:

No, what I want to have happen is, if I am on row 12 I want the text to
go
into the Start_1 on row 12. In this example start_1 would be cell AU12.

The dialog box which contains the text for Start_1 is displayed when I
click
on a cell in column A. This worked well when I was using TextBox1.Text =
rng(1, 47), but now I have to have the ability to add or remove columns.

"Dave Peterson" wrote in message
...
It kind of sounds like you want the first empty cell in that range to
get
the
value from your textbox.

If that's true, then you could do something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim myStart_1_Rng As Range

Set myStart_1_Rng = Worksheets("sheet1").Range("Start_1")

If Application.CountA(myStart_1_Rng) = myStart_1_Rng.Cells.Count
Then
MsgBox "No empty cells in the range!"
Exit Sub
End If

For iRow = 1 To myStart_1_Rng.Rows.Count
If IsEmpty(myStart_1_Rng.Cells(iRow, 1)) Then
myStart_1_Rng.Cells(iRow, 1).Value = Me.TextBox1.Text
Exit For
End If
Next iRow

End Sub



Patrick Simonds wrote:

TextBox1.Text = rng(1, "Start_1").Text

Start_1 is a range of cells ( AU3 :AU36) defined on the active
worksheet.
I
want the text from TextBox1 to be placed into the Start_1 of the
current
row.

I want to use Defined Names, so that over time if I have to add or
subtract
columns I will not have to go back and re-point all my references. The
code
I used before was TextBox1.Text = rng(1, 47).Text (this is just one of
dozens of lines code), but if I added or removed columns the
references
all
had to be readjusted.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson