squashsparky
01-11-2010, 12:16 PM
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
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