Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
I must set up an extra column that only contains values--values that
are equal to calculated values from another column. This has to be done in VBA and not a regular formula (the column must contain values--not formulas). I've tried... Range("NameValues").Value = Range("NamesConcatenated").Value The problem is that the NameValues must be updated after worksheet change. However, this is creating a never-ending 'filling cells' process. When forced quit (Error 1004) the data values, however, are in the NameValues column--so it's partially working--can this work? Any help would be incredibly appreciated. Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Eric,
Try code like the following in the worksheet's code module. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("NamesConcatenated")) Is Nothing Then Application.EnableEvents = False Range("NamesConcatenated").Copy Destination:=Range("NameValues") Application.EnableEvents = True End If End Sub The line Application.EnableEvents = False prevents the change from triggering the Change event, which would put you in a loop. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Arnold" wrote in message oups.com... I must set up an extra column that only contains values--values that are equal to calculated values from another column. This has to be done in VBA and not a regular formula (the column must contain values--not formulas). I've tried... Range("NameValues").Value = Range("NamesConcatenated").Value The problem is that the NameValues must be updated after worksheet change. However, this is creating a never-ending 'filling cells' process. When forced quit (Error 1004) the data values, however, are in the NameValues column--so it's partially working--can this work? Any help would be incredibly appreciated. Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Hi Chip,
Now the column/range "NameValues" does not contain anything--it won't fill with the values of "NamesContatenated" on worksheet change. No error though. The column NameValues should always equal NamesContatenated--when a user happens to modify a name, the worksheet change event should fire and the values should be updated. Possible? Thanks, Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Did you step through the code in the Change event? Are the ranges on the
same worksheet? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Arnold" wrote in message oups.com... Hi Chip, Now the column/range "NameValues" does not contain anything--it won't fill with the values of "NamesContatenated" on worksheet change. No error though. The column NameValues should always equal NamesContatenated--when a user happens to modify a name, the worksheet change event should fire and the values should be updated. Possible? Thanks, Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
The ranges are on the same worksheet.
Right now, if NamesContatenated changes, NamesValues does not change at all. Note that I've pasted special--values (names) into the NamesValues column/range, as this is what the sheet would look like if the code did work. The code is: If Not Application.Intersect(Target, Range("NamesContatenated")) Is Nothing Then Application.EnableEvents = False Range("NamesContatenated").Copy Destination:=Range("NamesValues") Application.EnableEvents = True End If Also note that "NamesContatenated" is a calculated column based on columns A and B, LastName and Firstname. NamesContatenated contains a simple formula that combines LastName and FirstName and inserts a comma in between. So, NamesContatenated only changes when a Firstname or Lastname is changed. If a firstname or lastname remains the same on worksheet change, NamesContatenated, and subsequently, NamesValues, should also remain the same. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Make sure events are turned on
manually run a macro like this Sub TurnOnEvents() Application.EnableEvents = True End sub that will enable events. Since you have a name like NamesConcatentated (and say "calculated values"), perhaps this range contains formulas and changes are made in another cell. If so, try this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, rng1 As Range If Target.Count 1 Then Exit Sub On Error Resume Next Set rng = Target.Dependents On Error GoTo 0 If Not rng Is Nothing Then Set rng1 = Union(rng, Target) Else Set rng1 = Target End If On Error GoTo ErrHandler If Not Application.Intersect(rng1, _ Application.Range("NamesConcatenated")) Is Nothing Then Application.EnableEvents = False Application.Range("NameValues").Value = _ Application.Range("NamesConcatenated").Value End If ErrHandler: Application.EnableEvents = True If Err.Number < 0 Then _ MsgBox "Error occured " & Err.Number & vbNewLine & _ Err.Description End Sub -- Regards, Tom Ogilvy "Arnold" wrote in message oups.com... Hi Chip, Now the column/range "NameValues" does not contain anything--it won't fill with the values of "NamesContatenated" on worksheet change. No error though. The column NameValues should always equal NamesContatenated--when a user happens to modify a name, the worksheet change event should fire and the values should be updated. Possible? Thanks, Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Tom and Chip,
You guys are great. That appeared to do the trick. Happy New Year. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Thanks Tom and Chip,
It appeared that application events was part of the problem. The code appears to work now. However, is it possible to use the column "NameValues", which is now set with code, as a source for a data validation list--basically create a named range "NameValues" and then set the range as the source for drop-down lists in another sheet. I just tried, and Excel stalled out, but 30 sec, showed the drop-down arrow and no data in the list. When selected, the first row (name) appeared and could be entered into the cell, but no other names could be seen or selected. This is my very last issue to resolve for hopefully a long while. Thanks, Eric |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a way!!!--set cells' values equal to calculated values in another range
Insert=Name=Define
Name: NameValues Refersto:=Sheets1!$A$1:$A$10 in Data validation (equal sign is necessary) =NameValues So using a defined name works for getting values from other sheets. It is unclear what you are doing incorrectly. -- Regards, Tom Ogilvy "Arnold" wrote in message oups.com... Thanks Tom and Chip, It appeared that application events was part of the problem. The code appears to work now. However, is it possible to use the column "NameValues", which is now set with code, as a source for a data validation list--basically create a named range "NameValues" and then set the range as the source for drop-down lists in another sheet. I just tried, and Excel stalled out, but 30 sec, showed the drop-down arrow and no data in the list. When selected, the first row (name) appeared and could be entered into the cell, but no other names could be seen or selected. This is my very last issue to resolve for hopefully a long while. Thanks, Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
Selecting values from a range that equal a specific total | Excel Worksheet Functions | |||
PIVOT TABLE - hiding records with CALCULATED item values equal to | Excel Discussion (Misc queries) | |||
SumIf(values of non calculated cells in a row...) | Excel Worksheet Functions |