Copy and Past to Active Cell

[Zacariah171]

I have the range A1:A31 (for the days of the month). I have equipment that I rent out and the unit number will be entered in one of those cells, whichever day it was sent out. I'd like to be able to select another cell in that range and copy that unit number to the cell I selected. For example, if "302" was entered in cell A6 and I knew to expect that unit back by the 29th, I'd normally just copy and paste 302 to cell A29 and reformat A29 with a border and a fill color. I've got the border and fill part down but how would I select a cell (in this case, A29), find the first cell containing a value that is to the left of my selected cell (in this case, A6), and then return that value to my selected cell (A29)? Any help is much appreciated. I'm very new to all of this.

 

[DataBlake]

I have the range A1:A31 (for the days of the month). I have equipment that I rent out and the unit number will be entered in one of those cells, whichever day it was sent out. I'd like to be able to select another cell in that range and copy that unit number to the cell I selected. For example, if "302" was entered in cell A6 and I knew to expect that unit back by the 29th, I'd normally just copy and paste 302 to cell A29 and reformat A29 with a border and a fill color. I've got the border and fill part down but how would I select a cell (in this case, A29), find the first cell containing a value that is to the left of my selected cell (in this case, A6), and then return that value to my selected cell (A29)? Any help is much appreciated. I'm very new to all of this.

So from what i can gather from your post you're trying to find a unit number in a different workbook/sheet and return the value next to it?
Cuz all i see are ranges of A and no mentions of other sheets.
I think a vlookup would solve your problem here, but im a little confused on what i'm looking for.

Edit: yeah i'm also not sure what you mean by to the left of your selected cell (A29) as that is the starting column so there can't be another column to the left? Is there multiple values in one cell?

 

[Zacariah171]

Sorry...not A1:A31... I meant A1:AE1...

 

[Zacariah171]

So, now that it's later in the day and I can think a little better, let me re-write my original post correctly...

I have the range A1:AE1 (31 cells for the days of the month). I have equipment that I rent out and the unit number will be entered in one of those cells, whichever day it was sent out. I'd like to be able to select another cell in that range and copy that unit number to the cell I selected. For example, if "302" was entered in cell F1 (day 6) and I knew to expect that unit back by the 29th, I'd normally just copy and paste 302 to cell AC1 (29th day) and reformat AC1 with a border and a fill color. I've got the VBA code for the border and fill part down, but how would I write the VBA code to start with any cell I select (in this case, AC1), find the first cell containing a value that is to the left of my selected cell (in this case, F1), and then return that value to my selected cell (AC1)? Any help is much appreciated. I'm very new to all of this.

 

[Zacariah171]

I think I figured it out. This is what I used and it's working out for me. I select a cell in my range and then click a command button to run the following macro. It finds the first value to the left of my selected cell and enters that value into my selected cell. I'm sure there's a better way to write it but it's working for me for now.

Range(ActiveCell, ActiveCell).Select
Range(ActiveCell, ActiveCell).Value = Selection.End(xlToLeft).Value

 

[DataBlake]

So, now that it's later in the day and I can think a little better, let me re-write my original post correctly...

I have the range A1:AE1 (31 cells for the days of the month). I have equipment that I rent out and the unit number will be entered in one of those cells, whichever day it was sent out. I'd like to be able to select another cell in that range and copy that unit number to the cell I selected. For example, if "302" was entered in cell F1 (day 6) and I knew to expect that unit back by the 29th, I'd normally just copy and paste 302 to cell AC1 (29th day) and reformat AC1 with a border and a fill color. I've got the VBA code for the border and fill part down, but how would I write the VBA code to start with any cell I select (in this case, AC1), find the first cell containing a value that is to the left of my selected cell (in this case, F1), and then return that value to my selected cell (AC1)? Any help is much appreciated. I'm very new to all of this.

I think I figured it out. This is what I used and it's working out for me. I select a cell in my range and then click a command button to run the following macro. It finds the first value to the left of my selected cell and enters that value into my selected cell. I'm sure there's a better way to write it but it's working for me for now.

Range(ActiveCell, ActiveCell).Select
Range(ActiveCell, ActiveCell).Value = Selection.End(xlToLeft).Value

sorry for the late response
to my knowledge all that is doing is ctrl + left from your active cell (which if thats all you need just press ctrl and left at the same time)
but what you're describing is the find function so maybe this will work for you as far as VBA goes?

Sub Macro1()

Dim myValue As Variant
Dim SelRange As Range

Set SelRange = Selection

myValue = InputBox("Enter Value")
    ActiveCell.entirerow.Select
    Selection.Find(What:=myValue,  After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False).Select
        
        
        Selection.Copy
        SelRange.Select
        ActiveCell.PasteSpecial Paste:=xlPasteValues

        
End Sub

So select a cell and then run the macro
it will prompt you for a value (such as "302")
and it will find the first instance of 302 in that row and copy and paste it to your current cell
hopefully thats what you mean because without seeing it, its a bit confusing.