Why does this not work?
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
|