VB Code Help

[shaikhiy]

Hi,

I need to do an if statement within the VB code, to say that if you cannot find the "#" don't do anything,otherwise, do what is in the code below...can you help???

The code is listed below:

Range("R10").Activate
Range("Draw2").Find(what:="#", after:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False).Activate
ActiveCell.Offset(0, -1).Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto reference:="DD2_Home_Date"
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Cheers
Imran

 

[Richie(UK)]

Hi Imran,

Not tested, but how about something like this:

<pre>Sub FindItNow()
Dim rngFindMe As Range

Range("R10").Activate
Set rngFindMe = Range("Draw2").Find(What:="#", after:=ActiveCell, LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If rngFindMe Is Nothing Then Exit Sub
rngFindMe.Activate
ActiveCell.Offset(0, -1).Select
Application.CutCopyMode = False
Selection.copy
Application.Goto reference:="DD2_Home_Date"
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub</pre>
HTH

 

[shaikhiy]

Thanks Richie,

I'm trying it out now...but one thing I'm worried about is that i have many of these to do, ie there is a "Draw3", "Draw4",....."Draw10" to do. What would I need to set rngFindMe as??? I want it all done in the same sub.

cheers Imran

 

[Andrew Poulsom]

Just change:

If rngFindMe Is Nothing Then Exit Sub
   rngFindMe.Activate
   ActiveCell.Offset(0, -1).Select
   Application.CutCopyMode = False
   Selection.copy
   Application.Goto reference:="DD2_Home_Date"
   ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

to

If Not rngFindMe Is Nothing Then
   rngFindMe.Activate
   ActiveCell.Offset(0, -1).Select
   Application.CutCopyMode = False
   Selection.copy
   Application.Goto reference:="DD2_Home_Date"
   ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If

and repeat the process for Draw3 etc.

 

[shaikhiy]

Thanks Andrew, will try!!

But |I had an earlier problem where the code was breaking at the point

Set rngFindMe = Range("Draw2").Find(What:="#", after:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False).Activate[code]

Do you know any reasons as to why?

Imran

 

[shaikhiy]

Andrew/Richie,

Also when I have enetered it for another, I am getting a compiling error message, where it is saying "Gives Duplicate message in Current Scope". Is this because I will need to change the Dim rngFindMe As Range part????

Imran

 

[Andrew Poulsom]

Yes, remove .Activate from the end.

 

[shaikhiy]

The removal of actviate works, cheers!!

Any suggestions on the other??

 

[Andrew Poulsom]

You only need

Dim rngFindMe As Range

once at the top of your code.

 

[shaikhiy]

Cheers for the help

...have you any suggestions for a shorter piece of code, as I'm having to repeat thsi for 10, and it is LONG!!!

i am looking to use Di Ranges As String, but not quite sure how to use it.

imran