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

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

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

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

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

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

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

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

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

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

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