copying cell data and updating other cells

[fishead123]

Is it possible to cut/paste partial data?

cell A1 = 126.1.FB.NAS
cell B1 = =QUOTE("126.1.FB.NAS","Symbol")

All my cells in column A have the same data EXCEPT for the FB portion.This portion that i will manually update (eg change FB to something else that can be 2-4 digits such as 126.1.AABC.NAS or 126.1.WDS.NAS
All my cells in column B have the same data and i want to copy the portion between the 126.1. (and) .NAS and insert the new portion (above) in it's place .

so if cell A1 = 126.1.FB.NAS and cell B1 = QUOTE("126.1.FB.NAS","Symbol")
and cell A2 = 126.1.AA.NAS then cell B2 = QUOTE("126.1.AA.NAS","Symbol")

Again i will manually change the cells in column A but would like the cell's in column B to update accordingly.

can anyone suggest how to do this?

thanks in advance.

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click on a cell in column A, make the changes and press the RETURN key.

Please Login or Register  to view this content.

[fishead123]

thanks Mumps, I tried what you asked but didn't get it to work.
Here's a copy of the file

Cell A2 shows FB after 126.1.
the FB is the area of the cell that i wish to change.

Cell A3 shows FB replaced with AMZN
this is providing the wrong result.

I'm hoping for a way to change all the FB's in row 3 to AMZN without having to do this manually.

Can you suggest any way to do this?

thanks,
Steve

[fishead123]

hopefully my attachment work this time...

[Mumps1]

You said:

so if cell A1 = 126.1.FB.NAS and cell B1 = QUOTE("126.1.FB.NAS","Symbol")
and cell A2 = 126.1.AA.NAS then cell B2 = QUOTE("126.1.AA.NAS","Symbol")

The macro I suggested does exactly what you described above. In the file you posted, cell A2 contains 126.1.FB.NAS. According to what you described in the above quote, if you change the FB to AMZN, then B2 should read QUOTE("126.1.AMZN.NAS","Symbol"). This is what the macro does. If this is not what you want, please attach a revised file that contains your actual data in the "Misc Stock" sheet and add another sheet that shows your desired results.

[fishead123]

AH... i got this to work for the SYMBOL column - THANKS!

When i paste the code under the 1st code and change the "Symbol" field to "Name" i'm hoping to update column C which is Name but I'm getting an error (ambiguous name detected: Worksheet_Change)
Can you show me what to do to get the other columns to update too please?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Target.Offset(, 1) = "=QUOTE(" & """" & Target & """" & "," & """" & "Symbol" & """" & ")"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Target.Offset(, 1) = "=QUOTE(" & """" & Target & """" & "," & """" & "NAME" & """" & ")"
End Sub

[Mumps1]

You can use only one Worksheet_Change event macro. You had two of them which generated the error. Try:

Please Login or Register  to view this content.

[fishead123]

Perfect! thanks Mumps!

[Mumps1]

You are very welcome.

[fishead123]

I just noticed as i add more fields it stops updating for some reason. Am i supposed to change the Target.Offset (, 1) to (, 2) etc for each additional field?


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Target.Offset(, 1) = "=QUOTE(" & """" & Target & """" & "," & """" & "Symbol" & """" & ")"
Target.Offset(, 2) = "=QUOTE(" & """" & Target & """" & "," & """" & "NAME" & """" & ")"
Target.Offset(, 2) = "=QUOTE(" & """" & Target & """" & "," & """" & "CURRENCY" & """" & ")"
Target.Offset(, 2) = "=QUOTE(" & """" & Target & """" & "," & """" & "LAST" & """" & ")"

[Mumps1]

The answer is "yes" if the columns that need to be change are consecutive so the offset would be 1, 2, 3, 4.

[fishead123]

OK got it working now - thanks again Mumps1

[Mumps1]

My pleasure.