I want to merge the two columns, sample data below:


CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A


What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

Hi,

Try this, right click your sheet tab, view code and paste this in

Sub stance()
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & lastrow)
For Each c In myrange
If Not Application.WorksheetFunction.IsNA(c.Value) Then
c.Offset(0, -1).Value = c.Value
End If
Next
End Sub


Mike

" wrote:

I want to merge the two columns, sample data below:


CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A


What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

Here is a quick one to suffice...

You will probably want to edit the FOR loop to accommodate the number of
rows needed. And maybe even the column references if your data is not in
column A and B. Please let me know if you need any help with this.


Mark

Sub test()

Dim row As Long

For row = 1 To 5
If Cells(row, 2).Text = "#N/A" Then
' Do nothing
Else: Cells(row, 1).Value = Cells(row, 2).Value
End If
Next

End Sub




wrote in message
...
I want to merge the two columns, sample data below:


CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A


What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

Thanks - the data isn't in columns A and B - how do I amend to reflect
this? Does A=1 and B=2 in your script?

On Mar 20, 11:35 am, "Mark Ivey" wrote:
Here is a quick one to suffice...

You will probably want to edit the FOR loop to accommodate the number of
rows needed. And maybe even the column references if your data is not in
column A and B. Please let me know if you need any help with this.

Mark

Sub test()

Dim row As Long

For row = 1 To 5
If Cells(row, 2).Text = "#N/A" Then
' Do nothing
Else: Cells(row, 1).Value = Cells(row, 2).Value
End If
Next

End Sub

wrote in message

...

I want to merge the two columns, sample data below:

CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A

What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

Hi,

For my code change the B in these 2 lines to the column where is #NA! is
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & lastrow)


This line represnts an offset form that column
c.Offset(0, -1).Value = c.Value
-1 is one to the left -2 is 2 etc and plus values are to the right

Mike


" wrote:

Thanks - the data isn't in columns A and B - how do I amend to reflect
this? Does A=1 and B=2 in your script?

On Mar 20, 11:35 am, "Mark Ivey" wrote:
Here is a quick one to suffice...

You will probably want to edit the FOR loop to accommodate the number of
rows needed. And maybe even the column references if your data is not in
column A and B. Please let me know if you need any help with this.

Mark

Sub test()

Dim row As Long

For row = 1 To 5
If Cells(row, 2).Text = "#N/A" Then
' Do nothing
Else: Cells(row, 1).Value = Cells(row, 2).Value
End If
Next

End Sub

wrote in message

...

I want to merge the two columns, sample data below:

CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A

What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

Exactly...

With the following line:
If Cells(row, 2).Text = "#N/A" Then

row = the current FOR loop and will perform an action on that particular row

and the "2" represents the column

Each time I used Cells(row, SOMENUMBER).Value or Text... Just change the
SOMENUMBER to reflect the column you need.

Mark

wrote in message
...
Thanks - the data isn't in columns A and B - how do I amend to reflect
this? Does A=1 and B=2 in your script?

On Mar 20, 11:35 am, "Mark Ivey" wrote:
Here is a quick one to suffice...

You will probably want to edit the FOR loop to accommodate the number of
rows needed. And maybe even the column references if your data is not in
column A and B. Please let me know if you need any help with this.

Mark

Sub test()

Dim row As Long

For row = 1 To 5
If Cells(row, 2).Text = "#N/A" Then
' Do nothing
Else: Cells(row, 1).Value = Cells(row, 2).Value
End If
Next

End Sub

wrote in message

...

I want to merge the two columns, sample data below:

CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A

What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

Fantastic - it works!!

Thanks a bunch.

On Mar 20, 12:36 pm, "Mark Ivey" wrote:
Exactly...

With the following line:
If Cells(row, 2).Text = "#N/A" Then

row = the current FOR loop and will perform an action on that particular row

and the "2" represents the column

Each time I used Cells(row, SOMENUMBER).Value or Text... Just change the
SOMENUMBER to reflect the column you need.

Mark

wrote in message

...

Thanks - the data isn't in columns A and B - how do I amend to reflect
this? Does A=1 and B=2 in your script?

On Mar 20, 11:35 am, "Mark Ivey" wrote:
Here is a quick one to suffice...

You will probably want to edit the FOR loop to accommodate the number of
rows needed. And maybe even the column references if your data is not in
column A and B. Please let me know if you need any help with this.

Mark

Sub test()

Dim row As Long

For row = 1 To 5
If Cells(row, 2).Text = "#N/A" Then
' Do nothing
Else: Cells(row, 1).Value = Cells(row, 2).Value
End If
Next

End Sub

wrote in message

...

I want to merge the two columns, sample data below:

CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A

What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.

No problem...

Glad to help...

Mark

wrote in message
...
Fantastic - it works!!

Thanks a bunch.

On Mar 20, 12:36 pm, "Mark Ivey" wrote:
Exactly...

With the following line:
If Cells(row, 2).Text = "#N/A" Then

row = the current FOR loop and will perform an action on that particular
row

and the "2" represents the column

Each time I used Cells(row, SOMENUMBER).Value or Text... Just change the
SOMENUMBER to reflect the column you need.

Mark

wrote in message

...

Thanks - the data isn't in columns A and B - how do I amend to reflect
this? Does A=1 and B=2 in your script?

On Mar 20, 11:35 am, "Mark Ivey" wrote:
Here is a quick one to suffice...

You will probably want to edit the FOR loop to accommodate the number
of
rows needed. And maybe even the column references if your data is not
in
column A and B. Please let me know if you need any help with this.

Mark

Sub test()

Dim row As Long

For row = 1 To 5
If Cells(row, 2).Text = "#N/A" Then
' Do nothing
Else: Cells(row, 1).Value = Cells(row, 2).Value
End If
Next

End Sub

wrote in message

...

I want to merge the two columns, sample data below:

CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A

What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.