Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
I'm sort of new to VBA programming with Excel and am trying to write a code that will do the following: 1) search a range covering J100:J1000 looking for the last 3 cells which have numbers in them, skipping over those cells with text. 2) I want to copy those three numbers to a worksheet in a different excel book to range AT3, AU3 and AV3 It sounds simple in my mind but I can't see to do it. and I would appreciate any help in this. Sirjadin26 -- sirjadin26 ------------------------------------------------------------------------ sirjadin26's Profile: http://www.excelforum.com/member.php...o&userid=24427 View this thread: http://www.excelforum.com/showthread...hreadid=380307 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
I think I'd just start at the bottom and work my way up looking for numbers:
Option Explicit Option Base 0 Sub testme() Dim fCtr As Long Dim myAddresses As Variant Dim otherWks As Worksheet Dim wks As Worksheet Dim cCtr As Long Dim myRng As Range Dim NumberOfAddresses As Long myAddresses = Array("AT3", "AU3", "AV3") NumberOfAddresses = UBound(myAddresses) - LBound(myAddresses) + 1 Set wks = ActiveSheet Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") With wks Set myRng = .Range("J100:J1000") fCtr = 0 For cCtr = myRng.Cells.Count To 1 Step -1 If Application.IsNumber(myRng.Cells(cCtr).Value) Then fCtr = fCtr + 1 otherWks.Range(myAddresses(fCtr - 1)).Value _ = myRng.Cells(cCtr).Value If fCtr = NumberOfAddresses Then Exit For End If End If Next cCtr If fCtr < NumberOfAddresses Then MsgBox "Only " & fCtr & " numbers found instead of " _ & NumberOfAddresses & "." End If End With End Sub The bottom most on the list goes to AT3, then AU3, then AV3? If not just change the order in this line: myAddresses = Array("AT3", "AU3", "AV3") sirjadin26 wrote: I'm sort of new to VBA programming with Excel and am trying to write a code that will do the following: 1) search a range covering J100:J1000 looking for the last 3 cells which have numbers in them, skipping over those cells with text. 2) I want to copy those three numbers to a worksheet in a different excel book to range AT3, AU3 and AV3 It sounds simple in my mind but I can't see to do it. and I would appreciate any help in this. Sirjadin26 -- sirjadin26 ------------------------------------------------------------------------ sirjadin26's Profile: http://www.excelforum.com/member.php...o&userid=24427 View this thread: http://www.excelforum.com/showthread...hreadid=380307 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
One correction to Dave's solution:
drop the .xls in this line Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") to look like this Set otherWks = Workbooks("book2").Worksheets("sheet1") Mike F "Dave Peterson" wrote in message ... I think I'd just start at the bottom and work my way up looking for numbers: Option Explicit Option Base 0 Sub testme() Dim fCtr As Long Dim myAddresses As Variant Dim otherWks As Worksheet Dim wks As Worksheet Dim cCtr As Long Dim myRng As Range Dim NumberOfAddresses As Long myAddresses = Array("AT3", "AU3", "AV3") NumberOfAddresses = UBound(myAddresses) - LBound(myAddresses) + 1 Set wks = ActiveSheet Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") With wks Set myRng = .Range("J100:J1000") fCtr = 0 For cCtr = myRng.Cells.Count To 1 Step -1 If Application.IsNumber(myRng.Cells(cCtr).Value) Then fCtr = fCtr + 1 otherWks.Range(myAddresses(fCtr - 1)).Value _ = myRng.Cells(cCtr).Value If fCtr = NumberOfAddresses Then Exit For End If End If Next cCtr If fCtr < NumberOfAddresses Then MsgBox "Only " & fCtr & " numbers found instead of " _ & NumberOfAddresses & "." End If End With End Sub The bottom most on the list goes to AT3, then AU3, then AV3? If not just change the order in this line: myAddresses = Array("AT3", "AU3", "AV3") sirjadin26 wrote: I'm sort of new to VBA programming with Excel and am trying to write a code that will do the following: 1) search a range covering J100:J1000 looking for the last 3 cells which have numbers in them, skipping over those cells with text. 2) I want to copy those three numbers to a worksheet in a different excel book to range AT3, AU3 and AV3 It sounds simple in my mind but I can't see to do it. and I would appreciate any help in this. Sirjadin26 -- sirjadin26 ------------------------------------------------------------------------ sirjadin26's Profile: http://www.excelforum.com/member.php...o&userid=24427 View this thread: http://www.excelforum.com/showthread...hreadid=380307 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
If your system doesn't have the option 'Hide extension for known file types'
selected, removing .xls will result in error. I guess it is always best to use the extension for compatibility reasons. Regards, KL "Mike Fogleman" wrote in message ... One correction to Dave's solution: drop the .xls in this line Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") to look like this Set otherWks = Workbooks("book2").Worksheets("sheet1") Mike F "Dave Peterson" wrote in message ... I think I'd just start at the bottom and work my way up looking for numbers: Option Explicit Option Base 0 Sub testme() Dim fCtr As Long Dim myAddresses As Variant Dim otherWks As Worksheet Dim wks As Worksheet Dim cCtr As Long Dim myRng As Range Dim NumberOfAddresses As Long myAddresses = Array("AT3", "AU3", "AV3") NumberOfAddresses = UBound(myAddresses) - LBound(myAddresses) + 1 Set wks = ActiveSheet Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") With wks Set myRng = .Range("J100:J1000") fCtr = 0 For cCtr = myRng.Cells.Count To 1 Step -1 If Application.IsNumber(myRng.Cells(cCtr).Value) Then fCtr = fCtr + 1 otherWks.Range(myAddresses(fCtr - 1)).Value _ = myRng.Cells(cCtr).Value If fCtr = NumberOfAddresses Then Exit For End If End If Next cCtr If fCtr < NumberOfAddresses Then MsgBox "Only " & fCtr & " numbers found instead of " _ & NumberOfAddresses & "." End If End With End Sub The bottom most on the list goes to AT3, then AU3, then AV3? If not just change the order in this line: myAddresses = Array("AT3", "AU3", "AV3") sirjadin26 wrote: I'm sort of new to VBA programming with Excel and am trying to write a code that will do the following: 1) search a range covering J100:J1000 looking for the last 3 cells which have numbers in them, skipping over those cells with text. 2) I want to copy those three numbers to a worksheet in a different excel book to range AT3, AU3 and AV3 It sounds simple in my mind but I can't see to do it. and I would appreciate any help in this. Sirjadin26 -- sirjadin26 ------------------------------------------------------------------------ sirjadin26's Profile: http://www.excelforum.com/member.php...o&userid=24427 View this thread: http://www.excelforum.com/showthread...hreadid=380307 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
You are right. I have that option unselected, therefore I errored out with
the .xls. I guess the problem is to determine which option the user is using. Since Windows selects this option by default, it would be better to include the extension for most users. Mike F "KL" wrote in message ... If your system doesn't have the option 'Hide extension for known file types' selected, removing .xls will result in error. I guess it is always best to use the extension for compatibility reasons. Regards, KL "Mike Fogleman" wrote in message ... One correction to Dave's solution: drop the .xls in this line Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") to look like this Set otherWks = Workbooks("book2").Worksheets("sheet1") Mike F "Dave Peterson" wrote in message ... I think I'd just start at the bottom and work my way up looking for numbers: Option Explicit Option Base 0 Sub testme() Dim fCtr As Long Dim myAddresses As Variant Dim otherWks As Worksheet Dim wks As Worksheet Dim cCtr As Long Dim myRng As Range Dim NumberOfAddresses As Long myAddresses = Array("AT3", "AU3", "AV3") NumberOfAddresses = UBound(myAddresses) - LBound(myAddresses) + 1 Set wks = ActiveSheet Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") With wks Set myRng = .Range("J100:J1000") fCtr = 0 For cCtr = myRng.Cells.Count To 1 Step -1 If Application.IsNumber(myRng.Cells(cCtr).Value) Then fCtr = fCtr + 1 otherWks.Range(myAddresses(fCtr - 1)).Value _ = myRng.Cells(cCtr).Value If fCtr = NumberOfAddresses Then Exit For End If End If Next cCtr If fCtr < NumberOfAddresses Then MsgBox "Only " & fCtr & " numbers found instead of " _ & NumberOfAddresses & "." End If End With End Sub The bottom most on the list goes to AT3, then AU3, then AV3? If not just change the order in this line: myAddresses = Array("AT3", "AU3", "AV3") sirjadin26 wrote: I'm sort of new to VBA programming with Excel and am trying to write a code that will do the following: 1) search a range covering J100:J1000 looking for the last 3 cells which have numbers in them, skipping over those cells with text. 2) I want to copy those three numbers to a worksheet in a different excel book to range AT3, AU3 and AV3 It sounds simple in my mind but I can't see to do it. and I would appreciate any help in this. Sirjadin26 -- sirjadin26 ------------------------------------------------------------------------ sirjadin26's Profile: http://www.excelforum.com/member.php...o&userid=24427 View this thread: http://www.excelforum.com/showthread...hreadid=380307 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
Hi Mike,
"Mike Fogleman" wrote in message ... it would be better to include the extension for most users. I'd say "it would be better...for all users" as the version "Book2.xls" would work in both cases, while the "Book2" version requires the option 'Hide extension for known file types' to be selected. Regards, KL |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
What I am saying is that I do not have 'Hide extension for known file types'
selected, and the code error "Subscript out of Range" stopped at: Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") So it did not work in my case. Mike F "KL" wrote in message ... Hi Mike, "Mike Fogleman" wrote in message ... it would be better to include the extension for most users. I'd say "it would be better...for all users" as the version "Book2.xls" would work in both cases, while the "Book2" version requires the option 'Hide extension for known file types' to be selected. Regards, KL |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
I just tried the code with either option selected, show & hide extensions,
and the code had the same error both ways. Windows XP Home - Excel 2000 Mike F "Mike Fogleman" wrote in message ... What I am saying is that I do not have 'Hide extension for known file types' selected, and the code error "Subscript out of Range" stopped at: Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") So it did not work in my case. Mike F "KL" wrote in message ... Hi Mike, "Mike Fogleman" wrote in message ... it would be better to include the extension for most users. I'd say "it would be better...for all users" as the version "Book2.xls" would work in both cases, while the "Book2" version requires the option 'Hide extension for known file types' to be selected. Regards, KL |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
Hi Mike,
Did you have a workbook open which had previously been saved as Book2? --- Regards, Norman "Mike Fogleman" wrote in message ... What I am saying is that I do not have 'Hide extension for known file types' selected, and the code error "Subscript out of Range" stopped at: Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") So it did not work in my case. Mike F "KL" wrote in message ... Hi Mike, "Mike Fogleman" wrote in message ... it would be better to include the extension for most users. I'd say "it would be better...for all users" as the version "Book2.xls" would work in both cases, while the "Book2" version requires the option 'Hide extension for known file types' to be selected. Regards, KL |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
DUH!! No. I had opened the saved Book1 but opened Book2 with "New", not a
saved Book2. Of course the code works exactly as Dave intended when Book2 is saved. Ya gotta luv xl Mike F "Norman Jones" wrote in message ... Hi Mike, Did you have a workbook open which had previously been saved as Book2? --- Regards, Norman "Mike Fogleman" wrote in message ... What I am saying is that I do not have 'Hide extension for known file types' selected, and the code error "Subscript out of Range" stopped at: Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") So it did not work in my case. Mike F "KL" wrote in message ... Hi Mike, "Mike Fogleman" wrote in message ... it would be better to include the extension for most users. I'd say "it would be better...for all users" as the version "Book2.xls" would work in both cases, while the "Book2" version requires the option 'Hide extension for known file types' to be selected. Regards, KL |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific cells
Hehehe.
I saved my test workbooks first! But it's fun to learn, huh? Mike Fogleman wrote: DUH!! No. I had opened the saved Book1 but opened Book2 with "New", not a saved Book2. Of course the code works exactly as Dave intended when Book2 is saved. Ya gotta luv xl Mike F "Norman Jones" wrote in message ... Hi Mike, Did you have a workbook open which had previously been saved as Book2? --- Regards, Norman "Mike Fogleman" wrote in message ... What I am saying is that I do not have 'Hide extension for known file types' selected, and the code error "Subscript out of Range" stopped at: Set otherWks = Workbooks("book2.xls").Worksheets("sheet1") So it did not work in my case. Mike F "KL" wrote in message ... Hi Mike, "Mike Fogleman" wrote in message ... it would be better to include the extension for most users. I'd say "it would be better...for all users" as the version "Book2.xls" would work in both cases, while the "Book2" version requires the option 'Hide extension for known file types' to be selected. Regards, KL -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Cells that add up to Specific Sum | Excel Worksheet Functions | |||
how can I find which cells in a dataset equal a specific value | Excel Worksheet Functions | |||
find cells that contain specific characters | Excel Worksheet Functions | |||
How can I find and format specific cells automatically in Excel? | Excel Worksheet Functions | |||
How to find a specific inform with some cells and return total | Excel Programming |