vba to change column B, but only when something occur in column A

[kit99]

I'm trying to switch surename and first name in column B, and put new name into column C.
This bit of vba is working ok.

But I only want this to be done if there are 11 digits in column A in the same row. Column A holds numbers, 11 digits for persons and 9 digits for companys.
Column B holds both person names and company names, and I only want to switch surename/first name on persons.

Tried this:

'Check if cell in column A holds 11 digits or not
For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
    If Len(cell.Value) = 11 Then
    
    'Switch surname and first name in column B, and place it into column C (code is ok)
    With Range("B2", Range("B" & Rows.Count).End(xlUp))
        .Offset(, 1).Value = Evaluate(Replace("if(len(#),left(trim(right(substitute(#,"" "",rept("" "",100)),100))&"" ""&#,len(#)),"""")", "#", .Address))
    End With
                        
End If
Next cell

... but it switches names on ALL cells in B.
Any ideas on how to modify this bit of vba?

 

[baitmaster]

Re: Help on vba to change column B, but only when something occur in column A

You're running through each cell in column A one at a time, but when you find a result for that cell, you're amending every cell in column B. You need to change only the corresponding cell in column B

Try removing:
With Range("B2", Range("B" & Rows.Count).End(xlUp))
End With

 

[kit99]

Re: Help on vba to change column B, but only when something occur in column A

You're running through each cell in column A one at a time, but when you find a result for that cell, you're amending every cell in column B. You need to change only the corresponding cell in column B

Try removing:
With Range("B2", Range("B" & Rows.Count).End(xlUp))
End With

Sorry, but trying:

'Check if cell in column A holds 11 numbers or not
For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
    If Len(cell.Value) = 11 Then
    
    'Switch surname and first name in column B, and place it into column C
    'With Range("B2", Range("B" & Rows.Count).End(xlUp))
        .Offset(, 1).Value = Evaluate(Replace("if(len(#),left(trim(right(substitute(#,"" "",rept("" "",100)),100))&"" ""&#,len(#)),"""")", "#", .Address))
    'End With
                        
End If
Next cell

... ends in "Compile error: Invalid or unqualified Reference", marking out ".Address" at the end of offset-line.

 

[baitmaster]

Re: Help on vba to change column B, but only when something occur in column A

sorry, forgot you're qualifying the .offset with the WITH statement. Use cell.offset and cell.address

 

[kit99]

Re: Help on vba to change column B, but only when something occur in column A

sorry, forgot you're qualifying the .offset with the WITH statement. Use cell.offset and cell.address

This one runs - but for some odd reason it messes things up and puts numbers/names into wrong columns...
However, I've tried to split this vba up a bit. It's more code and not as professional looking, but by doing it in these 3 steps it works just the way I want:

    '1) Find len=11 in A and then copy names (persons) in B to C
    For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Len(cell.Value) = 11 Then
            cell.Offset(0, 1).Copy Destination:=cell.Offset(0, 2)
        End If
    Next cell

    '2) Switch surname/first name on names copied to C
    With Range("C2", Range("C" & Rows.Count).End(xlUp))
        .Offset(, 0).Value = Evaluate(Replace("if(len(#),left(trim(right(substitute(#,"" "",rept("" "",100)),100))&"" ""&#,len(#)),"""")", "#", .Address))
    End With
                        
    '3) When cell in C is still empty, then copy (company) name from B to C
    For Each cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
        If cell.Offset(0, 1).Value = "" Then
            cell.Offset(0, 0).Copy Destination:=cell.Offset(0, 1)
        End If
    Next cell

Thanks a lot for responding to my thread.

 

[baitmaster]

Re: Help on vba to change column B, but only when something occur in column A

Again, my bad. You were offsetting one column from column B, This needed to be 2 columns from Cell (column A)