Copy column & "paste special as values" into first blank cell in r

[dhamDIT]

Hi
I want to copy cells A3:A35 then and then "paste special as value" into the first column in the range of C:AP (beginning in row 3) where C3:AP is blank.
Any help would be appreciated. I have tried a few things and have just not gotten it right yet
Thanks in advance
D

 

[Bong]

If you turn on the macro recorder, you get:

    Range("A1:A35").Select
    Selection.Copy

then

    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

unless you meant to take the column a3:a35 and transpose it into the row starting at c3, in which case you get

    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

_________________
Bob Rashkin

 

[dhamDIT]

Hi Bob, thanks! this certainly solves one of the issues, (the first option is what worked for me BTW, I was not trying to transpose), My other issue is how to get it to paste to the first column between c and ap where row 3 is blank.
Thanks D

 

[Bong]

    Range("C3").Select
    Selection.End(xlToRight).Select
    cells(selection.row,selection.column+1).select
         ....

_________________
Bob Rashkin

 

[Loomah]

Hi
Bong's suggestion seems ok but what happens if C3 is blank, or D3 is blank for that matter?

It's safer starting 'from the other end'

range("iv3").end(xltoleft).offset(0,1).select

It might also be worth checking if the destination cell is between columns C & AP

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[dhamDIT]

Hi, Thanks Bob and Loomah, combining your suggestions gave me exactly what I needed!!

Here is the code I ended up with

Sub Copy_lookup()

Range("B3:B35").Select
Selection.Copy
Range("ap3").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

D

 

[Loomah]

Hi
Just for information you can shorten your code as follows. The key is that is very rarely you will need to select/activate any object (especially ranges) in VBA.

Sub Copy_lookup()
    Range("B3:B35").Copy
    Range("ap3").End(xlToLeft).Offset(0, 1).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

As a side issue, what will happen when all the columns C to AP are filled?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​