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
IO_DorkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think what you are asking in your description can be achieved with the following code.....

Sub CopyFormula()
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
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IO_DorkAuthor Commented:
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)Excel & VBA ExpertCommented:
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

IO_DorkAuthor Commented:
yes, but I just tried the version that I posted and it works, so your solution is what I need. thanks.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad it worked for you.
Saurabh Singh TeotiaCommented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.