ofind
 

Hey folks,
Got a bit of code running to setup the sheet since the columns keep changing
order at the download. So I got this running to about BP1. Anyway, it's not
picking up the ID. Instead it's picking up OpportunityID. SO column A, B both
populate with OpportunityID, any thoughts?

Dim oFind As Range

Set oFind = Range("1:1").Find("ID")
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("A1")
End If
Set oFind = Range("1:1").Find("OpportunityID")
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("B1")
End If

ofind
 

Give this a whirl

Dim oFind As Range

Set oFind = Nothing
On Error Resume Next
Set oFind = Range("1:1").Find("ID")
On Error Goto 0
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("A1")
End If
Set oFind = Nothing
On Error Resume Next
Set oFind = Range("1:1").Find("OpportunityID")
On Error Goto 0
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("B1")
End If



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duncan_J" wrote in message
...
Hey folks,
Got a bit of code running to setup the sheet since the columns keep
changing
order at the download. So I got this running to about BP1. Anyway, it's
not
picking up the ID. Instead it's picking up OpportunityID. SO column A, B
both
populate with OpportunityID, any thoughts?

Dim oFind As Range

Set oFind = Range("1:1").Find("ID")
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("A1")
End If
Set oFind = Range("1:1").Find("OpportunityID")
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("B1")
End If

ofind
 

..Find() remembers the last thing that you used (either via code or via the
userinterface).

I'd specify the .find command with all it's parms:

Dim oFind As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks.Range("1:1")
Set oFind = .Cells.Find(what:="Id", _
searchdirection:=xlNext, after:=.Cells(.Cells.Count), _
lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False)
End With

===
xlwhole and matchcase may be important to you.

After:=.cells(.cells.count) used with searchdirection:=xlNext means that it'll
find the first one (when reading from A1 to IV1).



Duncan_J wrote:

Hey folks,
Got a bit of code running to setup the sheet since the columns keep changing
order at the download. So I got this running to about BP1. Anyway, it's not
picking up the ID. Instead it's picking up OpportunityID. SO column A, B both
populate with OpportunityID, any thoughts?

Dim oFind As Range

Set oFind = Range("1:1").Find("ID")
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("A1")
End If
Set oFind = Range("1:1").Find("OpportunityID")
If Not oFind Is Nothing Then
oFind.Resize(2000, 1).Copy _
Destination:=Worksheets("Raw Data").Range("B1")
End If

--

Dave Peterson