copy paste question

[nburaq]

Hi Gents,
I searched all over the internet but is there any way to paste data shown as below

so basically I would like to paste the values in column F exactly the same range in column B. However, when I copy this values in column B it overwrites the values! and all ages disappear. I tried skip blanks option in my scenario but no luck! (to be honest for small amount of data it works ) Any suggestion? Thanks for your help!

 

[ranman256]

you wouldnt paste, youd scan thru the list writing the values.

Public Sub SetNA()
Dim rng As Range
Dim r As Long

Range("A2").Select
Set rng = ActiveSheet.UsedRange
r = 2

While r <= rng.Rows.Count
     If ActiveCell.Offset(0, 1).Value <> "" Then ActiveCell.Offset(0, 5).Value = "NA"
     
     ActiveCell.Offset(1, 0).Select  'next row
     r = ActiveCell.Row
Wend
End Sub

 

[nburaq]

you wouldnt paste, youd scan thru the list writing the values.

Public Sub SetNA()
Dim rng As Range
Dim r As Long

Range("A2").Select
Set rng = ActiveSheet.UsedRange
r = 2

While r <= rng.Rows.Count
     If ActiveCell.Offset(0, 1).Value <> "" Then ActiveCell.Offset(0, 5).Value = "NA"
    
     ActiveCell.Offset(1, 0).Select  'next row
     r = ActiveCell.Row
Wend
End Sub

Thanks for the help, so if NA value is in column G instead F then should offset value be (0,6) ?
or the past value is not in column B but C then which values in formula should be changed?

 

[Fluff]

How about

Sub nburaq()
   Dim Rng As Range
   
   With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("if(@<>"""",@,if(#="""","""",#))", "@", .Address), "#", .Offset(, 4).Address))
   End With
End Sub