Concatenate if equal too..

Re: Concatenate if equal too..

Change to

Sub concatenate() 
     
Dim Cell As Range
 
For Each Cell In Selection
    If Cell.Value = "-" Then
        Cell.Value = [h1].Value & [i1].Value & [j1].Value
    End If
Next Cell
     
End Sub

Re: Concatenate if equal too..

Sub concatenate()
     
Dim Cell As Range
 
For Each Cell In Selection
    If Cell.Value = "-" Then
        Cell.Value = Cell.Offset(, -1).Value & Cell.Value & Cell.Offset(, 1).Value
    End If
Next Cell
     
End Sub

Re: Concatenate if equal too..

Try this

Sub concatenateandclear() 
     
    Dim c As Range 
     
    For Each c In Selection 
        If c.Value = "-" Then 
            c.Value = c.Offset(, -1).Value & c.Value & c.Offset(, 1).Value 
            c.Offset(,-1).ClearContents
            c.Offset(,1).ClearContents            
        End If 
    Next c     
End Sub

Re: Concatenate if equal too..

Newt,

Put the code

Range(Cell, Cell.Offset(, 1)).ClearContents

before "End If".

Hope this helps.

Regards,
Batman.

Re: Concatenate if equal too..

Quote from norie

Try this........

Holy Smokes Norie, you're everywhere!

Re: Concatenate if equal too..

So are you!

Re: Concatenate if equal too..

Newt,

Let's go back to the start on this.

When you run the macro, is there a requirement to select a certain range? Will the program do odd things if the wrong selection is made?

As an object, Selection can have many different collections, e.g. Areas, Rows, Columns, Cells. I always find it useful to declare to VBA exactly which collection you want to use, i.e.

For Each c In Selection.Cells

Within your loop through (presumably) each cell in Selection (which from this snippet of code we can't tell if it's several cells in a row, several cells in a column, or what), every time you go through the loop you are checking (or appear to be attempting to check) the same thing - is the whole of column H empty. Are you trying to check the whole of column H for being empty (in which case you would need different code, and it seems a bit pointless doing the same thing many times over) or a specific cell in column H?

Similarly, you then appear to be attempting to copy the whole of column C, column B, then column C again into column B (I assume one of the two is a misprint and should be (, -1) instead of (, 1).

It would make things much easier if we could have sight of a bit of your data to see what you're trying to achieve, but let me assume that you have a range of data, over a varying number of rows, and you want to concatenate 3 cells iinto column B if the cell in column H is empty, and that you have selected a range of cells covering column A only:

Dim rngSelection As Range
Dim rngCell As Range


Set rngSelection = Selection
For Each rngCell in rngSelection.Cells
    If Len(rngCell.Offset(0, 7).Value) > 0 Then
        rngCell.Offset(0, 1) = rngCell.Offset(0, 1).Value & rngCell.Offset(0, 2).Value & rngCell.Offset(0, 3).Value
        Range(rngCell.Offset(0, 2), rngCell.Offset(0, 3)).ClearContents
    End If
Next rngCell

I have avoided deleting cells and shifting to the left, as it tends to corrupt the structure of the rest of your data.

I may well be extremely wide of the mark here, and if I am perhaps you could post up a sample workbook so that we can get a bit better idea of your process.

Regards,
Batman.

Re: Concatenate if equal too..

Newt,

Now I'm really confused!

Up to now your posts have indicated that you want to check the contents of column H for being empty then, if not, concatenate the contents of 3 unspecified columns. What you have attached is already concatenated, there is no data in any column other than H and you now seem to want to split a single column (H) into sections.

Have we moved to a different part of your overall process? If so, do you still need the original request answering?

With regard to the set of data that you have posted, how easy it will be to break it down in a consistent manner will depend on the structure of the data in the cells.

My first thought is to loop backwards through the cell. As there appears to be a postcode (or whatever you call it) at the end of each record, and that includes a single space, you should be able to separate out the postcode by searching for the second space from the right.

After that you seem to have a province, containing only a single word, so you could separate that out by searching for the third space from the right.

Before that you have a city; if that is always a single word searching for the fourth space from the right will find that.

That should then only leave the house number and street as the remainder of the field to go into a separate cell.

If you think the logic above will work, let me know and I will add some code to do it.

Regards,
Batman.

Re: Concatenate if equal too..

Newt,

Try the attached. It seems to work for the range of data in your sample list of addresses.

Hope this helps.

Regards,
Batman.

Re: Concatenate if equal too..

Glad to have been of help.

Regards,
Batman.