If value already exsists in range....
Hi Folks!
I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak for a different application. Sub copyrange() Dim rng As Range, rng1 As Range Dim cell As Range, i As Long Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _ .Cells(Rows.Count, 11).End(xlUp)(2) Set rng = Range("copyrange") i = 0 For Each cell In rng rng1.Offset(0, i).Value = cell i = i + 1 Next End Sub It copies the value in a named cell, "copyrange", to the first empty cell starting in Sheet1 K2. What I'd like to tweak is that before the value is copied is to check and make sure that value doesn't already exsist in the range K2:Kn. If the value does not exsist continue with the copy. If the value does exsist pop a msg box then end the routine. Any help would be greatly appreciated. Biff |
If value already exsists in range....
Hi Biff
I have assumed that copyrange, Sheet1 and this macro are all in the same workbook and that copyrange is a single cell. Let me know if any of these are not correct: Sub TryMe() Dim eRow As Long Dim cpyVal As Variant 'I don't know the datatype of your data Dim fndRng As Range eRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row cpyVal = Range("copyrange").Value With Sheets("Sheet1").Range("K2:K" & eRow) Set fndRng = .Find(cpyVal) End With If Not fndRng Is Nothing Then MsgBox "Already exist in cell " & fndRng.Address Else Sheets("Sheet1").Cells(eRow + 1, 11).Value = cpyVal End If End Sub Regards Rowan Biff wrote: Hi Folks! I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak for a different application. Sub copyrange() Dim rng As Range, rng1 As Range Dim cell As Range, i As Long Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _ .Cells(Rows.Count, 11).End(xlUp)(2) Set rng = Range("copyrange") i = 0 For Each cell In rng rng1.Offset(0, i).Value = cell i = i + 1 Next End Sub It copies the value in a named cell, "copyrange", to the first empty cell starting in Sheet1 K2. What I'd like to tweak is that before the value is copied is to check and make sure that value doesn't already exsist in the range K2:Kn. If the value does not exsist continue with the copy. If the value does exsist pop a msg box then end the routine. Any help would be greatly appreciated. Biff |
If value already exsists in range....
Hi Rowan!
Yes, everything is in the same wb. Dim cpyVal As Variant 'I don't know the datatype of your data Data type is numeric. It's not working as I had hoped. It's copying the value and to the correct location but it's still copying if the value already exsists. Example: copyrange = 1100 K2 = 927 K3 = 1024 K4 = 1100 1100 already exsists in K2:Kn. The routine is still copying 1100 to K5. If the copyrange value already exsists in K2:Kn, then Msg box and end routine. Otherwise, continue. Thanks Biff "Rowan Drummond" wrote in message ... Hi Biff I have assumed that copyrange, Sheet1 and this macro are all in the same workbook and that copyrange is a single cell. Let me know if any of these are not correct: Sub TryMe() Dim eRow As Long Dim cpyVal As Variant 'I don't know the datatype of your data Dim fndRng As Range eRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row cpyVal = Range("copyrange").Value With Sheets("Sheet1").Range("K2:K" & eRow) Set fndRng = .Find(cpyVal) End With If Not fndRng Is Nothing Then MsgBox "Already exist in cell " & fndRng.Address Else Sheets("Sheet1").Cells(eRow + 1, 11).Value = cpyVal End If End Sub Regards Rowan Biff wrote: Hi Folks! I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak for a different application. Sub copyrange() Dim rng As Range, rng1 As Range Dim cell As Range, i As Long Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _ .Cells(Rows.Count, 11).End(xlUp)(2) Set rng = Range("copyrange") i = 0 For Each cell In rng rng1.Offset(0, i).Value = cell i = i + 1 Next End Sub It copies the value in a named cell, "copyrange", to the first empty cell starting in Sheet1 K2. What I'd like to tweak is that before the value is copied is to check and make sure that value doesn't already exsist in the range K2:Kn. If the value does not exsist continue with the copy. If the value does exsist pop a msg box then end the routine. Any help would be greatly appreciated. Biff |
If value already exsists in range....
Ooops!
Sorry, I was running the wrong module! It works just fine. Thank very much you! Biff "Biff" wrote in message ... Hi Rowan! Yes, everything is in the same wb. Dim cpyVal As Variant 'I don't know the datatype of your data Data type is numeric. It's not working as I had hoped. It's copying the value and to the correct location but it's still copying if the value already exsists. Example: copyrange = 1100 K2 = 927 K3 = 1024 K4 = 1100 1100 already exsists in K2:Kn. The routine is still copying 1100 to K5. If the copyrange value already exsists in K2:Kn, then Msg box and end routine. Otherwise, continue. Thanks Biff "Rowan Drummond" wrote in message ... Hi Biff I have assumed that copyrange, Sheet1 and this macro are all in the same workbook and that copyrange is a single cell. Let me know if any of these are not correct: Sub TryMe() Dim eRow As Long Dim cpyVal As Variant 'I don't know the datatype of your data Dim fndRng As Range eRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row cpyVal = Range("copyrange").Value With Sheets("Sheet1").Range("K2:K" & eRow) Set fndRng = .Find(cpyVal) End With If Not fndRng Is Nothing Then MsgBox "Already exist in cell " & fndRng.Address Else Sheets("Sheet1").Cells(eRow + 1, 11).Value = cpyVal End If End Sub Regards Rowan Biff wrote: Hi Folks! I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak for a different application. Sub copyrange() Dim rng As Range, rng1 As Range Dim cell As Range, i As Long Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _ .Cells(Rows.Count, 11).End(xlUp)(2) Set rng = Range("copyrange") i = 0 For Each cell In rng rng1.Offset(0, i).Value = cell i = i + 1 Next End Sub It copies the value in a named cell, "copyrange", to the first empty cell starting in Sheet1 K2. What I'd like to tweak is that before the value is copied is to check and make sure that value doesn't already exsist in the range K2:Kn. If the value does not exsist continue with the copy. If the value does exsist pop a msg box then end the routine. Any help would be greatly appreciated. Biff |
If value already exsists in range....
Hi Biff
I started with a clean sheet. Typed 927 in K2, 1024 in K3 and 1100 in K4. Typed 1100 in copyrange and ran macro. Got the message "Already exists in K4". Could your values in col K or copyrange include decimal places not shown due to calculations and formatting? Regards Rowan Biff wrote: Hi Rowan! Yes, everything is in the same wb. Dim cpyVal As Variant 'I don't know the datatype of your data Data type is numeric. It's not working as I had hoped. It's copying the value and to the correct location but it's still copying if the value already exsists. Example: copyrange = 1100 K2 = 927 K3 = 1024 K4 = 1100 1100 already exsists in K2:Kn. The routine is still copying 1100 to K5. If the copyrange value already exsists in K2:Kn, then Msg box and end routine. Otherwise, continue. Thanks Biff "Rowan Drummond" wrote in message ... Hi Biff I have assumed that copyrange, Sheet1 and this macro are all in the same workbook and that copyrange is a single cell. Let me know if any of these are not correct: Sub TryMe() Dim eRow As Long Dim cpyVal As Variant 'I don't know the datatype of your data Dim fndRng As Range eRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row cpyVal = Range("copyrange").Value With Sheets("Sheet1").Range("K2:K" & eRow) Set fndRng = .Find(cpyVal) End With If Not fndRng Is Nothing Then MsgBox "Already exist in cell " & fndRng.Address Else Sheets("Sheet1").Cells(eRow + 1, 11).Value = cpyVal End If End Sub Regards Rowan Biff wrote: Hi Folks! I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak for a different application. Sub copyrange() Dim rng As Range, rng1 As Range Dim cell As Range, i As Long Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _ .Cells(Rows.Count, 11).End(xlUp)(2) Set rng = Range("copyrange") i = 0 For Each cell In rng rng1.Offset(0, i).Value = cell i = i + 1 Next End Sub It copies the value in a named cell, "copyrange", to the first empty cell starting in Sheet1 K2. What I'd like to tweak is that before the value is copied is to check and make sure that value doesn't already exsist in the range K2:Kn. If the value does not exsist continue with the copy. If the value does exsist pop a msg box then end the routine. Any help would be greatly appreciated. Biff |
If value already exsists in range....
You're welcome. Disregard my other response.
Regards Rowan Biff wrote: Ooops! Sorry, I was running the wrong module! It works just fine. Thank very much you! Biff "Biff" wrote in message ... Hi Rowan! Yes, everything is in the same wb. Dim cpyVal As Variant 'I don't know the datatype of your data Data type is numeric. It's not working as I had hoped. It's copying the value and to the correct location but it's still copying if the value already exsists. Example: copyrange = 1100 K2 = 927 K3 = 1024 K4 = 1100 1100 already exsists in K2:Kn. The routine is still copying 1100 to K5. If the copyrange value already exsists in K2:Kn, then Msg box and end routine. Otherwise, continue. Thanks Biff "Rowan Drummond" wrote in message ... Hi Biff I have assumed that copyrange, Sheet1 and this macro are all in the same workbook and that copyrange is a single cell. Let me know if any of these are not correct: Sub TryMe() Dim eRow As Long Dim cpyVal As Variant 'I don't know the datatype of your data Dim fndRng As Range eRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row cpyVal = Range("copyrange").Value With Sheets("Sheet1").Range("K2:K" & eRow) Set fndRng = .Find(cpyVal) End With If Not fndRng Is Nothing Then MsgBox "Already exist in cell " & fndRng.Address Else Sheets("Sheet1").Cells(eRow + 1, 11).Value = cpyVal End If End Sub Regards Rowan Biff wrote: Hi Folks! I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak for a different application. Sub copyrange() Dim rng As Range, rng1 As Range Dim cell As Range, i As Long Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _ .Cells(Rows.Count, 11).End(xlUp)(2) Set rng = Range("copyrange") i = 0 For Each cell In rng rng1.Offset(0, i).Value = cell i = i + 1 Next End Sub It copies the value in a named cell, "copyrange", to the first empty cell starting in Sheet1 K2. What I'd like to tweak is that before the value is copied is to check and make sure that value doesn't already exsist in the range K2:Kn. If the value does not exsist continue with the copy. If the value does exsist pop a msg box then end the routine. Any help would be greatly appreciated. Biff |
All times are GMT +1. The time now is 12:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com