Copy only cell value to 1st empty cell in range

[nokia447]

In cell C1 I have formula like =CONCATENATE(F1;G1;H1)
I want to have a VBA script that takes the value from C1 and paste the value in A5. Next time I use the macro I want it to paste the value of C1 to the first empty cell in column A.

I have found a script that works for copying if the C1 is a value already, but that script does not work if C1 is a formula reffering to values in other cells....

Current VBA is:
-------------
Sub test1()
Range("C1").copy Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub
-------------

 

[My Aswer Is This]

Try this:

Sub My_Answer()
'Modified  11/10/2021  9:42:11 PM  EST
Range("A" & Rows.Count).End(xlUp).Offset(1) = Range("C1").Value
End Sub

 

[sxhall]

May have pipped to the post on this however the below will work only if there is a value in cell C1 and will populate cell A5 if it is blank otherwise it will add it to the cell below.

Sub test1()

If Range("C1").Value = ""  Then

Else
    If Range("A5").Value = "" Then
    
    Range("A5").Value = Range("C1").Value
    
    Else
    
    Range("A" & Rows.Count).End(xlUp).Offset(1) = Range("C1").Value
    
    End If
    
End If

End Sub

Steven

 

[nokia447]

Try this:

Sub My_Answer()
'Modified  11/10/2021  9:42:11 PM  EST
Range("A" & Rows.Count).End(xlUp).Offset(1) = Range("C1").Value
End Sub

Thanks alot, works good.

 

[nokia447]

May have pipped to the post on this however the below will work only if there is a value in cell C1 and will populate cell A5 if it is blank otherwise it will add it to the cell below.

Sub test1()

If Range("C1").Value = ""  Then

Else
    If Range("A5").Value = "" Then
   
    Range("A5").Value = Range("C1").Value
   
    Else
   
    Range("A" & Rows.Count).End(xlUp).Offset(1) = Range("C1").Value
   
    End If
   
End If

End Sub

Steven

Thank you so much this works good so I used this.

 

[My Aswer Is This]

Thanks alot, works good.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.