CODEWITHSUNDEEP
vbaexcelnamed-ranges
Dozens
Dozens

Reputation: 145

Replace cell values in specific sheets with defined name

I am trying to run some code that replaces the cell values in a specific column with a defined name. In addition, I have a condition that the replacement should only take place if the first 9 characters of the values are xxxxxxxxx.

More precisely, it should change the values in C:C in 2 specific worksheets (I don't want to loop through the whole workbook).

I am not sure why nothing happens in the code (no error messages, nothing).

I presume, however, that I should not use With if I want the code to work in these 2 specific worksheets. I am also aware that my use of Range is probably not totally correct.

Sub ChangeMe()

  Dim cl As Range

  For Each cl In Worksheets("Sheet1").Range("C:C").End(xlUp)
    With Worksheets("Sheet2").Range("C:C").End(xlUp)
      If Left(cl.Value, 9) = "XXXXXXXXX" Then
        cl.Value = ThisWorkbook.Names("MyDefinedName").RefersToRange
      End If
    End With
  Next cl

End Sub

Upvotes: 1

Views: 282

Answers (3)

robinCTS
robinCTS

Reputation: 5886

In answer your original questions:

I am not sure why nothing happens in the code (no error messages, nothing).

Nothing happens because your worksheet values are lowercase xxxxxxxxx, whilst your code checks for uppercase XXXXXXXXX.

I presume, however, that I should not use With if I want the code to work in these 2 specific worksheets.

Actually, you can use With with multiple sheets, as I will demonstrate below.

I am also aware that my use of Range is probably not totally correct.

That is true. If you were to fix the uppercase issue, only C1 would be changed. This is because .End() works on a single cell. If you supply a multi-cell range, it uses the top left most cell. So .Range("C:C").End(xlUp) is equivalent to .Range("C1").End(xlUp) which evaluates to just C1.

The following will answer your updated question:

Option Explicit

Public Sub ChangeMe()

  Const l_xxxxxxxxx      As String = "xxxxxxxxx"
  Const l_MyDefinedName  As String = "MyDefinedName"
  Const s_Delimiter      As String = ","
  Const s_WorkSheetNames As String = "Sheet1,Sheet2"
  Const s_ColumnToChange As String = "C:C"

  Dim varWorkSheetName As Variant
  For Each varWorkSheetName In Split(s_WorkSheetNames, s_Delimiter)
    With Worksheets(varWorkSheetName).Range(s_ColumnToChange)
      Dim rngCell As Range
      For Each rngCell In .Resize(.Cells(Rows.Count).End(xlUp).Row)
        With rngCell
          Dim strCellValue As String: strCellValue = .Value2
          If Left(strCellValue, Len(l_xxxxxxxxx)) = l_xxxxxxxxx Then
            .Value2 _
            = Names(l_MyDefinedName).RefersToRange.Value2 _
            & Right$(strCellValue, Len(strCellValue) - Len(l_xxxxxxxxx))
          End If
        End With
      Next rngCell
    End With
  Next varWorkSheetName

End Sub


Notes:

  • It is a good idea to use constants so all literal values are typed once only and kept grouped together.

  • Using .Value2, instead of .Value, is the recommended way to access a cell's value as it avoids implicit casting and is therefore faster. (Using .Value can also sometimes cause issues.)

  • Surprisingly, in VBA there are good reasons to put a variable declaration as close as possible to the first use of the variable. Two such reasons are 1) it improves readability, and 2) it simplifies future refactoring. Just remember that the variable is not reinitialised every time the Dim is encountered. Initialisation only occurs the first time.

Upvotes: 1

Vityata
Vityata

Reputation: 43585

Let's imagine that this is your input:

enter image description here

In this case, you want to change the values in range A1:A2 to the value in C1 (named range xxxx123), because it starts with xxxx123. This is the code to achieve it:

Public Sub TestMe()

    Dim myCell          As Range
    Dim myNamedRange    As String

    myNamedRange = "xxxx123"

    For Each myCell In Range("A1:A2")
        If Left(myCell, Len(myNamedRange)) = myNamedRange Then
            myCell.Value = Range(myNamedRange)
        End If
    Next myCell

End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

If I understood your post correctly (which I doubt it), I think you want to loop through column "C" in both "Sheet1" and "Sheet2". Every cell that starts with 9 "XXXXXXXXX", should be replaced with the value in "MyDefinedName" Named Range.

Code

Option Explicit

Sub ChangeMe()

Dim cl As Range
Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
    With sht
        If .Name = "Sheet1" Or .Name = "Sheet2" Then
            For Each cl In .Range("C1:C" & .Cells(.rows.Count, "C").End(xlUp).Row)
                If Left(cl.Value, 9) = "XXXXXXXXX" Then
                    cl.Value = ThisWorkbook.Names("MyDefinedName").RefersToRange
                End If
            Next cl
        End If
    End With
Next sht

End Sub

Upvotes: 0

Related Questions