IF statement VBA, (If cell A empty replace with cell B)

[jufglanville]

Hi all,

I'm looking for what I presume is an If statement where I have two columns formed from another macro. These columns contain dates, but quite often one of the columns will be empty and the other will have a date. I'm looking to merge the two columns into one and have thought the easiest way would be to do an If statement where the 'Parent' column is 'A' and if the 'Parent' column is empty then the macro will place the data from column 'B' into it.

I know this can be done with a simple formula as shown below but want this to be vba code and run automatically with other processes.

=IF(A4<>"",A4,B4)

I imagine there may need to be a loop function as well? though not too sure as the number of rows will every vary every time I come to run the process.

Thanks

 

[jufglanville]

So I've also realised that the number of columns may vary as well, so the formula would look something like this:

=IF(A4<>"",A4,IF(B4<>"",B4,IF(C4<>"",C4,IF(D4<>"",D4,IF(E4<>"",E4,"")))))

I attempted to just run multiple Private Subs but I'm not getting the right results. This is where I'm at at the moment:

Private Sub MergeDatesAll()


Call MergeDates1
Call MergeDates2
Call MergeDates3
Call MergeDates4


End Sub

Private Sub MergeDates1()


Call copyHeadedDataReliefs
Dim i As Long

i = 1
Do While Cells(i, "Z").Value <> ""
If IsEmpty(Cells(i, "A")) = True Then
Cells(i, "A").Value = Cells(i, "B")

End If
i = i + 1
Loop

End Sub

Private Sub MergeDates2()


Call copyHeadedDataReliefs
Dim i As Long

i = 1
Do While Cells(i, "Z").Value <> ""
If IsEmpty(Cells(i, "A")) = True Then
Cells(i, "A").Value = Cells(i, "C")

End If
i = i + 1
Loop

End Sub


Private Sub MergeDates3()


Call copyHeadedDataReliefs
Dim i As Long

i = 1
Do While Cells(i, "Z").Value <> ""
If IsEmpty(Cells(i, "A")) = True Then
Cells(i, "A").Value = Cells(i, "D")
Else
Cells(i, "A").Value = Cells(i, "A")
End If
i = i + 1
Loop

End Sub

Problem 1
I've had to manually place a number down column z to the end of the data table as sometime there may be no date in any of the columns and so if I did a Do While on the cells it will stop as soon as it first hits a blank record.

Problem 2
When I run the first loop I get the correct results but when I run the second it replaces all the data in column "A" regardless of whether there is data there or not.

I ultimately would like this to be written into one Private Sub if possible to help compact the code.

Thanks

 

[jufglanville]

Found the problem, I was stupidly calling the function copyHeadedDataRelief which essentially started the process from the beginning each time, duh!!!

So removing that has sorted that problem.

If anyone has a way of condensing this and also if anyone knows how I can get the system to check all of the used range. Maybe I could use UsedRange in some way?