CODEWITHSUNDEEP
excelvbanamed-ranges
Marta João
Marta João

Reputation: 23

How to return values to a named range instead of using a range's Offset()

Warning to the experts: I am a “pre-beginner” in vba...

I have a workbook that I am using to find attributes of a product based on its name. E.g. cell in named range “Album_Name” with value “My Favorite Things by John Coltrane” should return a value of “John Coltrane” in named range “Musician”, a value of “Jazz” in named range “Music_genre” (offset from named range “Album_Name by 78 columns), etc.

I’m currently using this:

Sub UpdateAttributes()

    Dim c As Range
    Dim i As Integer
    Dim vezesQueEncontrouNumero As Integer
    Dim posicaoSegundoNumero As Integer

    For Each c In Range("Album_Name", Range("Album_Name ").End(xlDown))

    vezesQueEncontrouNumero = 0
    posicaoSegundoNumero = -1

    For i = 1 To Len(c)

        If IsNumeric(Mid(c, i + 1, 1)) Then
            vezesQueEncontrouNumero = vezesQueEncontrouNumero + 1
            If (vezesQueEncontrouNumero) = 2 Then
                posicaoSegundoNumero = i
            End If
        End If
    Next
**If InStr(UCase(c.Value), UCase("John Coltrane")) > 0 Then c.Offset(0, 78).Value = "Jazz"**

My question: As the number of attributes is growing it gets more difficult to “c.Offset” by the correct number of columns. And as sometimes I must add columns in between, it really gets unworkable! Is there a way of putting c.Value in a named range instead of using Offset?

Thank you.

Update

After the help of @Luuklag , @wallyeye , @JvdV and - specially - @nwhaught I've rewrote my vba but still to no avail. I'm still missing something. The vba changes the value of the cell in the same column ("name") and not in the "Genre" or "Artist" column. (I did put "name" at A1, "Genre" at B1 and "Artist" at C1). Somehow the "attribution" of "colNum" isn't working.

    Sub UpdateProductAttributes()

    Dim colNum As Integer
    For colNum = 1 To 100 'or however many populated columns you end up having...
        Select Case Sheet1.Cells(1, colNum) 'Look at the column header
            Case "Genre" 'If you've found the "Genre" column
                genreColumn=colNum 'Give the genreColumn variable the correct value
            Case "Artist"
                artistColumn=colNum
        End Select
    Next

   Dim c As Range
    Dim i As Integer
    Dim vezesQueEncontrouNumero As Integer
    Dim posicaoSegundoNumero As Integer

    For Each c In Range("name", Range("name").End(xlDown))
    vezesQueEncontrouNumero = 0
    posicaoSegundoNumero = -1
        For i = 1 To Len(c)

            If IsNumeric(Mid(c, i + 1, 1)) Then
            vezesQueEncontrouNumero = vezesQueEncontrouNumero + 1

            If (vezesQueEncontrouNumero) = 2 Then
            posicaoSegundoNumero = i

            End If
            End If

    Next i

        If InStr(UCase(c.Value), UCase("Coltrane")) > 0 Then
            c.Offset(0, genreColumn).Value = "Jazz"

            ElseIf InStr(UCase(c.Value), UCase("Brad Spreadsheet")) > 0 Then
            c.Offset(0, genreColumn).Value = "Indie Folk Grunge"

        End If
        Next c
End Sub

What could be wrong?

Upvotes: 1

Views: 238

Answers (1)

nwhaught
nwhaught

Reputation: 1592

To expand on some of the comments: Index Match is a pattern that's generally used in in-cell formulas as a more-flexible companion to VLOOKUP.

It works like this: =INDEX(YourTotalRangeOfData,MATCH("YourSearchKey",TheColumnRangeOfYourSearchKey,0),TheNumberOfTheColumnInYourTotalRangeThatYouWantToReturn)

In practice, it looks like this: =INDEX(C3:E11,MATCH("Frantz",B3:B11,0),2)

In cells, Excel will keep track of the changes for you. In code, you'll continue to run into issues with changing column reference numbers.

wallyeye's comment about setting column variables is a good one, and you could do it like this: 

Dim genreColumn as Integer
genreColumn = 78

**If InStr(UCase(c.Value), UCase("John Coltrane")) > 0 Then c.Offset(0, genreColumn).Value = "Jazz"**

Even better, in my opinion, would be to have a "setup" section that ran at the beginning of your code. The purpose of the setup section is to set all of your column variables for you. Something like:

Dim colNum as Integer
For colNum = 1 to 100 'or however many populated columns you end up having...
    Select Case Sheet1.Cells(1, colNum) 'Look at the column header
        Case "Genre" 'If you've found the "Genre" column
            genreColumn = colNum 'Give the genreColumn variable the correct value
        Case "Artist"
            artistColumn = colNum
    End Select
Next

Run that at the beginning of your code, and you'll never have to worry about the changing column position again. As long as you have the title of the column correct (which is much easier to check) you'll have the correct number.

Also, to echo what others have said, you're doing great. This doesn't look like the code of a "pre" beginner. :)

Upvotes: 1

Related Questions