VB Code Help

shaikhiy

Board Regular
Joined
Jul 24, 2002
Messages
52
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:

Code:
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

Board Regular
Joined
Jul 24, 2002
Messages
52
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Just change:

Code:
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

Code:
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

Board Regular
Joined
Jul 24, 2002
Messages
52

ADVERTISEMENT

Thanks Andrew, will try!!

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

Code:
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

Board Regular
Joined
Jul 24, 2002
Messages
52
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Yes, remove .Activate from the end.
 

shaikhiy

Board Regular
Joined
Jul 24, 2002
Messages
52
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top