View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Mike Fogleman Mike Fogleman is offline
external usenet poster
 
Posts: 1,092
Default 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