IO_Dork
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).Se lect
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)).Sel ect
Application.CutCopyMode = False
Selection.ClearContents
Range("K6").Select
End Sub
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).Se
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)).Sel
Application.CutCopyMode = False
Selection.ClearContents
Range("K6").Select
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
yes, but I just tried the version that I posted and it works, so your solution is what I need. thanks.
You're welcome. Glad it worked for you.
If you ask me i will do something like this..rather then copy & pasting formula ..i will apply the formula in go like this..
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...
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
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...
ASKER
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)).Sel
Application.CutCopyMode = False
Selection.ClearContents
Range("K6").Select
End Sub