2

I would like to loop through cells in column A and if a cell is blank perform the CONCATENATE function in column C from column B. I have this code but it is not working. Any help would be greatly appreciated.

Sub ConcateBlank()

For i = 3 To 400
    If Cells(i, 1) = "" Then
    Cells(i, 3) = CONCATENATE(Cells(i, 2), " ", Cells(i + 1, 2))
    End If
Next i
End Sub
Máté Juhász
  • 21,451
matrix23
  • 23
  • What exactly "not working"? "ONCATENATE function in column C from column B" - how exactly you want to do that? It's a bit mismatching CONCATENATE(Cells(i, 2), " ", Cells(i + 1, 2)) – Máté Juhász Nov 14 '16 at 12:27

1 Answers1

2

This one works without CONCATENATE (maybe faster):

Sub ConcateBlank()

For i = 3 To 400
    If Cells(i, 1) = "" Then
        Cells(i, 3) = Cells(i, 2) & " " & Cells(i + 1, 2)
    End If
Next i
End Sub
Leo Chapiro
  • 15,599
  • What is even the difference between Cells(i + 1, 2) and Cells(i, 2)? – Christofer Weber Nov 14 '16 at 12:44
  • If i = 1 then Cells(i + 1, 2) is Cells(2, 2) and Cells(i, 2) is Cells(1,2) or have I misunderstood your question? – Leo Chapiro Nov 14 '16 at 12:45
  • 1
    Yeah, no you haven't. I just misunderstood what he was looking for. I blame lack of nutrition. Thanks for the clarification. – Christofer Weber Nov 14 '16 at 12:50
  • 1
    Hi Dude, yes this is what I was looking however the script only works this way round :
    Cells(i, 3) = Cells(i + 1, 2) & " " & Cells(i, 2) as opposed to Cells(i, 3) = Cells(i, 2) & " " & Cells(i + 1, 2) any ideas why it won't this way? Cheers     – matrix23 Nov 14 '16 at 13:04
  • 1
    It's Ok I've sorted out the problem. Thank you very much for your help. I am fairly new to VBA so am still finding my way. Thanks again! – matrix23 Nov 14 '16 at 13:11