Copying Cell-Value if target Cell is not empty - absolut beginner

[CapeTown]

Hello MrExcel Community.


I want to make a huge excel file, that so far needs multiple manual inputs of the same data, easier to use. Sadly formulae dont help anymore and I need to do it in VBA.


Although - with my inexistent knowledge of programming - google helped me to answer most of my questions and solved 90% of what I'm trying to achieve, now I got stuck. Pretty hard to train an old dog new tricks


Please do not hate me for these two simple questions. I really tried to figure it out myself....


Question 1


3 columns are of interest:
A --> contains the data I need to copy
B --> which can be an empty cell but might already have a value
C --> cell that definitely is empty


I want this script to copy the Data from A into B if the cell in B is empty, otherwise copy the data from A into C. (range 1-150)


Rather easy if you know how to tell the PC what you want, I bet...


Question 2


Same scenario. But what happens if - due to added lines - the range now is 1-165.
Don't want to change the script manually every time.


I thought of maybe putting "EOL" in the last cell in A. Then the script goes through lines 1 - x till it reads out EOL.
Is this a good idea and how would that look like in VBA? Or is there a better solution?
(The lines beneath my "EOL" might not be empty)


If someone would take the time to help me out it would be very appreciated.


Thank you and


Cheers
Kai

 

[MrTeeny]

I want this script to copy the Data from A into B if the cell in B is empty, otherwise copy the data from A into C. (range 1-150)

You could simply loop thru the cell range and and If cell B is empty fill with contents of A and else copy A to C as there can only be two options i.e. B is empty of not

So something like

Sub ColumnA_Copy()
Dim i As Long


Application.Calculation = xlCalculateManual
Application.ScreenUpdating = False
On Error GoTo xit:




For i = 1 To 150


If Range("A" & i).Value = "" Then
Range("B" & i).Value = Range("A" & i).Value
Else
Range("C" & i).Value = Range("A" & i).Value
End If


Next i


xit:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

If we don't want to hard code the 150 into code we can always find the last row in a column using some code like Cells(Rows.Count, "A").End(xlUp).Row so we'd just amend the line

For i = 1 To 150


to 

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row