rename the strings in a particular column with fixed header

[Marco1975]

Hi all,

I would like to rename the strings in a particular column with fixed header .

I use this part of code vba to find the header:

Sub IMPORTA_COL_CU_fascicolo()
Header = "doc/storia/in_fascicolo/@codice"
With Sheets(2)
  numcols = .UsedRange.Columns.Count
  numrows = .UsedRange.rows.Count
  For c = 1 To numcols
    If .Cells(1, c) = Header Then

how do I rename strings in the column that I found?

For example if in this column there is this string: "2011-E-06/03.00004.00011" after macro must match "Ancona", and so on for all the string.
In this column there are many different string to rename; each code corresponds to a string.

Thanks.

 

[Marc L]

Hi,

maybe not very crystal clear but you can use Replace on an area range
without to find out if string exists, no needs loop …

Could be obvious with a sample xlsx workbook and more explanations.
​

 

[Marco1975]

I try to explain better...

I would find within the Sheet2 , the column with the heading " doc / history / in_fascicolo / @ code " and find and replace the codes with the names of cities . with the first macro I posted I find the header , but I need the code that finds and replaces .

attach files with :
sheet1 in the situation and in Sheet2 the situation after the command vba .

 

[Marc L]

Using Replace method on a cells range no needs to find :​

Sub Demo()
With Worksheets(1).Cells(1).CurrentRegion
                   V = Application.Match("doc/storia/in_fascicolo/@codice", .Rows(1), 0)
    If Not IsError(V) Then
          .Columns(V).Replace "2011-EQUIGIU-06/03.00004.00010", "CAGLIARI"
    End If
End With
End Sub

 

[Marco1975]

Marc thank you for the reply... now is perfect!!!!

 

[Marc L]

Better to hard code conversion is to use a worksheet

so in case of new cities to add no needs to amend the VBA code …
​

 

[Marco1975]

ok all clear... thanks Marc!!!!

 

[Marco1975]

Marc said in this way?

 

[Marc L]

My thought was by code using a conversion worksheet …

But first I wanna know when you're in VBE in your Excel italian version
what is the CodeName of Foglio1 worksheet ? (could be the same)

In the project window, between the parenthesis is the name and just before
it's the CodeName … Or when the worksheet is selected in the project window, in the properties window the fisrt item (Name) is the CodeName …

Many lines in real worksheet ?​

 

[Marco1975]

The CodeName of Foglio1 is Foglio1...
attach excel immage

 

[Marc L]

Andiamo !

In this new code, Foglio3 is the parameter worksheet for conversion,
data in columns A & B, row #1 for titles (distretto, codice) :​

Sub Demo()
VA = Foglio3.Cells(1).CurrentRegion.Value

With Foglio1.Cells(1).CurrentRegion
                   V = Application.Match("doc/storia/in_fascicolo/@codice", .Rows(1), 0)
    If Not IsError(V) Then
        Application.ScreenUpdating = False

        With .Columns(V)
            For R& = 2 To UBound(VA):  .Replace VA(R, 2), VA(R, 1):  Next
        End With
    End If
End With
End Sub

Using CodeName is safer in case of a worksheet name change
or a change in the worsheets order …
​

 

[Marc L]

In fact, better is to put the column name too in the parameters worksheet
for example in place of codice title in B1 cell :​

Sub Demo()
VA = Foglio3.Cells(1).CurrentRegion.Value

With Foglio1.Cells(1).CurrentRegion
                   V = Application.Match(VA(1, 2), .Rows(1), 0)
    If Not IsError(V) Then
        Application.ScreenUpdating = False

        With .Columns(V)
            For R& = 2 To UBound(VA):  .Replace VA(R, 2), VA(R, 1):  Next
        End With
    End If
End With
End Sub

Like this in case of a new city code or a new column title,
no need to amend the VBA code, just update the parameters worksheet …​

 

[Marco1975]

Marc perfect!!!!
Thanks for this update