asked on

Select Range - find last value in adjacent column

So I need to modify the following macro so that it pastes the formula from L5 into cells L6 down to the last corresponding value in column K. For example, if the last value in column K ends at K510, then I want to select L6 through L510. This needs to be dynamic b/c the last cell in column K changes everday...somedays the data will fill down to k300, or 500 or 100, etc, but the data always starts on k6, so I need to always select cells in column L at L6 and go down as far as column K goes.

I know this is a messy maco (thats b/c i created it the sloppy way using the record button), but essentially what its doing is copying the formula in L5 and pasting it in cells L6 through Lx (as far down as last value in col. K), once the formulas calculate it then copies and pastes only the values of the formulas from L6 to Lx, over to K6 to Kx. Then to clean up column l for the next run, we clear out all the formulas from L6 and down, leaving the formula in L5.

Sub Convert_DollarPrice()
'
' Convert_DollarPrice Macro
'
'
Range("L5").Select
Selection.Copy
Range("L6:L1065").Select
' Range("L6").End(xlDown).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("K6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("K6").Select

End Sub

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IO_Dork

ASKER

so, is this how i would incorporate it into my existing macro?

Sub Convert_DollarPrice()
'
' Convert_DollarPrice Macro
'
'
Range("L5").Select
Selection.Copy
Dim lr As Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
If lr > 5 Then
Range("L5").Copy
Range("L6:L" & lr).PasteSpecial xlPasteFormulas
End If
Application.CutCopyMode = 0
Application.CutCopyMode = False
Selection.Copy
Range("K6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("K6").Select

End Sub

Subodh Tiwari (Neeraj)

Would you please explain that what are you trying to do here.

What it seems from your description and then your code is that you want to copy the formula in L5 to the range L6:L100 (assuming 100 is the last row with data in col. K) and then copy the range L6:L100 again and paste the values only to the range K6:K100 and then delete the range L6:L100.
Is this correct?

IO_Dork

ASKER

yes, but I just tried the version that I posted and it works, so your solution is what I need. thanks.

Subodh Tiwari (Neeraj)

You're welcome. Glad it worked for you.

Saurabh Singh Teotia

If you ask me i will do something like this..rather then copy & pasting formula ..i will apply the formula in go like this..

Sub applyf()

Dim lr As Long
        lr = Cells(Rows.Count, "K").End(xlUp).Row
      
        Range("L5:L" & lr).Formula = "=a5*10" 'Basically this will beformula you want to apply..
        Range("k5:k" & lr).Formula = "=l5*10"
     
End Sub

Open in new window

Now in this the formula i'm applying basically multiplying a5 value with 10 and then l5 value with 10 and now for row-6 it will automaitcally refer to row-6 and so on..and you can change that with the original formula that you are copy pasting or applying..

Saurabh...