Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
"the file already exsists in this location do you want to replace. | Excel Discussion (Misc queries) | |||
Check if Userform exsists before deleting via VB | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |