OK, the subject should say a lot about my Excel programming experience.
I'm quite the flaming novice. :)

I'm trying to change the text in a sheet named "Cert" on a cell
starting in column C to the text in a cell in another sheet in the same
workbook named "Info" when the cells in the A column change. I was
thinking this should be really easy and doable in 2 or 3 lines.

Here's what I came up with:

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C:" & Target.Row).Text = sheets("Info") ... and then the
cell, based on target cell's value.

It's the part after the "=" that I think is really getting me. It
can't be that hard... but I'm managing to make it so. Any advice would
be greatly appreciated.

Max.

Text is a read only property

Also, you cleverly left out any explanation of how to use the value of
Target to determine the appropriate cell in Info. I will assume it is a
named range.

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C" & Target.Row).Value = sheets("Info").Range(Target.Value).Text
end if

Note several changes to your original code.

--
Regards,
Tom Ogilvy


"Max C" wrote in message
ups.com...
OK, the subject should say a lot about my Excel programming experience.
I'm quite the flaming novice. :)

I'm trying to change the text in a sheet named "Cert" on a cell
starting in column C to the text in a cell in another sheet in the same
workbook named "Info" when the cells in the A column change. I was
thinking this should be really easy and doable in 2 or 3 lines.

Here's what I came up with:

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C:" & Target.Row).Text = sheets("Info") ... and then the
cell, based on target cell's value.

It's the part after the "=" that I think is really getting me. It
can't be that hard... but I'm managing to make it so. Any advice would
be greatly appreciated.

Max.

Hey Tom. Thanks for the reply. I tried something nearly exactly like
what you wrote below, only I was using the actual code to select the
range I want. Basically, when the user types in a number on the Cert
page in Column A, I use it to grab the description from a corresponding
line on the Info page from Range DXX:JXX. Every time I'd enter
something on the info page, though, I'd get the error "the method of
range class failed."

I have a new problem, though. I saved my workbook and closed Excel
last night. This morning when I went in to try your suggestion, the
workbook won't run the Macro I wrote when I change a value on the A
column on the Cert sheet. I'll keep plugging away at it. Believe it
or not, 10 years ago I was pretty good at writing Excel Macros. I was
even MS certified in VB. Just goes to show... if you don't use it,
you'll loose it.

Thanks for your help.
Max.

Tom Ogilvy wrote:
Text is a read only property

Also, you cleverly left out any explanation of how to use the value of
Target to determine the appropriate cell in Info. I will assume it is a
named range.

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C" & Target.Row).Value = sheets("Info").Range(Target.Value).Text
end if

Note several changes to your original code.

--
Regards,
Tom Ogilvy

Tom, thanks for the reply

OK, getting back in to this. I can't believe this is turning out to be
so hard for me. It's more than a little embarassing. Here's what I
have so far:
__________________________________________________ __________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedText As String

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
SelectedText = Sheets("Info").Range("D" & (Target.Value + 24) &
":J" & (Target.Value + 24)).Text
Range("C" & Target.Row & ":I" & Target.Row).Text = SelectedText
End If

End Sub
__________________________________________________ __________

I realize the string variable is unnecessary, but it helps me see it
better. The thing is, the variable assignment line doesn't work. I
get Error 94: Invalid use of Null. Is there no such thing as a Text
property for a range of cells?

By the way, I'm using ranges of cells because in this workbook the 2
ranges holding text are merged cells. When I ran the macro recorder
and selected one of those merged cell blocks, the recorder showed I
selected a range of cells. Maybe that's my problem?

Thanks for looking again.
Max.

And dang it! Every time I save and close a workbook with this code in
it, when I reopen the workbook, the change event stops firing. Once
again I don't even get an error message when I change something in
column A. What's the deal?!

Max.


Max C wrote:
OK, getting back in to this. I can't believe this is turning out to be
so hard for me. It's more than a little embarassing. Here's what I
have so far:
__________________________________________________ __________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedText As String

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
SelectedText = Sheets("Info").Range("D" & (Target.Value + 24) &
":J" & (Target.Value + 24)).Text
Range("C" & Target.Row & ":I" & Target.Row).Text = SelectedText
End If

End Sub
__________________________________________________ __________

I realize the string variable is unnecessary, but it helps me see it
better. The thing is, the variable assignment line doesn't work. I
get Error 94: Invalid use of Null. Is there no such thing as a Text
property for a range of cells?

By the way, I'm using ranges of cells because in this workbook the 2
ranges holding text are merged cells. When I ran the macro recorder
and selected one of those merged cell blocks, the recorder showed I
selected a range of cells. Maybe that's my problem?

Thanks for looking again.
Max.