Changing data refered to by varying named ranges though a macro

[squashsparky]

I have a large spreadsheet which is changing through the addition of Rows and Columns, so some data is adressed using named ranges..
The number of these is now around 115, different names.

However these ranges represent DEFAULT values for a multi - user form spreadsheet.

I have a CommandButton, with a Macro, which reset all the values back to defaults - but the values were hard coded in the marco... several users have asked to be able to alter the defaults.

I thought, I would add a worksheet..list the names on this worksheet, (Named ranges I set in RED, so I could add tesxt to help explain to the user what each reffered to..) and entered the defaults next to them in a sheet (thus enabling users to edit the defaults), and write a litle bit of code so when the user clicks the RESET button.. it would loop through the Named ranges, picking out only the RED ones (so note reading the comments), and apply the looked up value from the adjacent column...back to the main spreadsheet where the data had been changed...

i.e. 115 values, like...
Name ...........Value
Plate_1 .........505
Plate_3 .........577
P1_w .............45
Cost_ft2 .........56

So, I wrote this code.. to look up the Name I had given the range in column 1 (as long as it was red text), and revert the named range back to the value shown in column 2.. simple I thought...



Dim x As Integer, C As Integer, cell As Range, Rngname As String, myvalue As String
C = 1 ' Where C= column that the item to be reset is
' number of defaults to reset
For x = 2 To 115
'values of interest are colour coded in red, so only select these
If Worksheets("Defaults").Cells(x, C).Font.ColorIndex = 3 Then
'look up NAMED cell to reset vaule to default
Rngname = Worksheets("Defaults").Cells(x, C).value
myvalue = Worksheets("Defaults").Cells(x, C + 1).Value

'Just a quick check to ensure vaules are being read correctly
Worksheets("Defaults").Cells(x, C + 5) = Rngname
Worksheets("Defaults").Cells(x, C + 6) = myvalue
'Now reset the NAMED range to the required value
Range(Rngname) = myvalue x
End If
Next x
End Sub


But it doesn't work.
it looks up the name and the value no problem, and ignores the comments I placed for the users... BUT;
I can not seem to reference, the Range unless there is quotation marks around the string, and then it is not a variant..

How can I look up my list of names and revert the data back to the defaults (which a user can still view and adjust).

Thanks
Squashsparky

[Bob Phillips]

Dim x As Integer, C As Integer, cell As Range, Rngname As String, myvalue As String
C = 1 ' Where C= column that the item to be reset is
With Worksheets("Defaults")
For x = 2 To 115
If .Cells(x, C).Font.ColorIndex = 3 Then
Rngname = .Cells(x, C).Value
myvalue = .Cells(x, C + 1).Value
.Cells(x, C + 5) = Rngname
.Cells(x, C + 6) = myvalue
ThisWorkbook.Names.Add Name:=.Cells(x, C).Value, RefersTo:="=" & .Cells(x, C + 1).Value
End If
Next x
End With

[squashsparky]

Thanks for a fast response XLD,

I can see when I apply the code, and then look in the Excel Name manager, that the named range value is set to what I want.

BUT, previously in the Name manager, it references the Cell that the named range reffered to, now there is no sheet or cell reference... just the number.... AND as a result any formulae in the spreadsheet that also used the original named reference don't work any more.
(They don't error with NAME#), they just don't caluclate correctly.. them seem to be looking still for the original cells that the named range reffered to.

How, can I reset the cells pointed to originally by the named range?

:banghead::banghead:

Can I look up the original names list WITH addresses in the sheet, the cross reference this with the name being read from my spreadsheet list the write this back with the vaule AND the original Refers to address+

If so, how do I do this?... Sorry to ask what may seem like a simple question, but this is really got me going round in circles..

Any other ideas would be appreciated.

[Bob Phillips]

I don't understand any of that. If it is set to what you want, what is the problem?

Give me an example of what say the name Plate_1 should refer to after this code, and what else you might want.

[squashsparky]

I don't understand any of that. If it is set to what you want, what is the problem?

Give me an example of what say the name Plate_1 should refer to after this code, and what else you might want.

Plate1 = "=Data!$H$5" 200

The sheet Data contains values, which are looked up by User forms, and displayed to the as these are changed it writes the value back to the NamedCell (Plate1) or Data$H$5". This data is also used by other cells.

But, if the user doesn't want to use the USERFORMS, which are more of a WIZARD to help them through the table of data, then the user can key directly into the cell H5 (which has the named range Plate1).. Doing this, means the WIZARD type userforms I have works.

IF I emply the route you have helped me with so far, I re-write the data to the NAME "Plate1", but I loose the refers to cell it is linked to. As a result, IF the user doesn't use the Userforms to set the data.. the cell is empty.

The sheets are a RETURN ON INVESTMENT wizards for a large peice of machinery with multiple cost applications and uses... So the values may change significantly. Every now and again the customer, wants to reset all the vaules he has been keying in to our defaults... BUT in some situations where this is used.. the defaults change (i.e. from country to country).

The values were reset using hard code stating:-

Worksheets("Data").Range("P1_V") = 100

(But the users couldn't edit the default reset values then).

So, the idea was to creat a table with the cell "NAME", and a quick explaination of what the value related to...then when the REST button on the sheet was pressed, the name would be looked up and the value reset.

The code you gave me, resets the value..to the name.. BUT looses the REFERS TO= cell address, so that the main spreadsheet no longer sees the values... fine for users using the wizard .. (userforms), but any users using the spreadsheet directly loose the connection to the "NAME" in the cell that it DID refer to..

Sorry, as I type this.. it sounds confussing.. but does that help ?
Power Users could edit the default value.. then hide the sheet to stop normal users fidiling...