conditional merging of two columns?

[wrethams]

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.

 

[Mike H]

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

 

[Mark Ivey]

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

 

[wrethams]

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?

 

[Mike H]

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

 

[Mark Ivey]

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

 

[wrethams]

Fantastic - it works!!

Thanks a bunch.

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

 

[Mark Ivey]

No problem...

Glad to help...

Mark

Fantastic - it works!!

Thanks a bunch.