Update specific value in named range?

Question

I have the following function which retrieves a value from a named range:

Function getSetting(settingName As String)
    getSetting = Application.VLookup(settingName, Range("rSettings"), 2, True)
End Function

Now I can easily say myColor = getSetting("myColor") and it will return "blue" or whatever.

What I want to do now is update that value.

Function updateSetting(settingName As String, newValue As String)
    ?????
End Function

Ideas?

Hey! Where do you get the color from? Or rather the color of what? — Kathara, Jan 28 '16 at 15:23

score 2 · Accepted Answer · answered Jan 28 '16 at 15:25

2

One you will need to use a sub not a function.

Two you can use the Range.Find method to find the cell. Then use offset to change the value.

Sub updateSetting(settingName As String, newValue As String)
    Dim c As Range
    Set c = Range("rSettings").Find(settingName)
    c.Offset(, 1).Value = newValue
End Sub

Also as to your first function, personal preference is not to use worksheet function where vba function can be used. It is slower.

Function getSetting(settingName As String)
     Dim c As Range
    Set c = Range("rSettings").Find(settingName)
    getSetting = c.Offset(, 1).Value 
End Function

answered Jan 28 '16 at 15:25

Scott Craner

148,073
10
49
81

1

Yeah I actually had a sub in my code but for the question I copied and pasted the getSetting function :) – Jan 28 '16 at 15:32
1

Also this worked like a charm, and thanks for the tip on the getSetting function! – Jan 28 '16 at 15:34

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You can determine the location of each settingName in the range using the Match function:

WorksheetFunction.Match method

Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

dim index
index = Match(settingName,Range("settings"),0)

`Match` is what I was working on but I kept getting `Error 2042` or something. Thanks for the answer, but @Scott Craner's works great and makes the most sense to me. — , Jan 28 '16 at 15:38

Update specific value in named range?

2 Answers2