Find and replace within VBA
 

Hi,

I write macros that that extract data from my company's database.
use the 'Edit-Replace' option in VB to change the dates that ar
programmed to extract the data as they occur several times within th
macro.

Is it possible to program a userform (or similar) to do this?

For example I don't really want my clients playing directly with th
macro. I hoped I could have a userform pop-up that said 'Find' (the
insert the date) and 'Replace' (they insert the new date!) and upo
clicking OK the macro is changed and run!

I can only do this to use the 'Edit-Replace' option within Exce
rather than VBA

Any suggestions greatly recieved

Kate
Pool

--
Message posted from http://www.ExcelForum.com

Find and replace within VBA
 

Consider moving these dates into a lookup worksheet of it's own. The code
then references the lookup sheet whenever it needs the date.
Instead of:
If Date() = CDate("1-Mar-2004") Then
MsgBox "Greater than reference date"
End If

Use:
If Date = Worksheets("LookupData").Range("ReferenceDate").Va lue Then
MsgBox "Greater than reference date"
End If


If that's not an option, you could make the date a Public Const in it's own
module. That way you limit their exposure to the entire code.

eg.
In modConstants
Public Const cReferenceDate = "1-Mar-2004"

In modLoanCalcs
If Date() = CDate(cReferenceDate) Then
MsgBox "Greater than reference date"
End If



And last and most certainly least in this case, theres a find/replace code
example on my website.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"kateb " wrote in message
...
Hi,

I write macros that that extract data from my company's database. I
use the 'Edit-Replace' option in VB to change the dates that are
programmed to extract the data as they occur several times within the
macro.

Is it possible to program a userform (or similar) to do this?

For example I don't really want my clients playing directly with the
macro. I hoped I could have a userform pop-up that said 'Find' (they
insert the date) and 'Replace' (they insert the new date!) and upon
clicking OK the macro is changed and run!

I can only do this to use the 'Edit-Replace' option within Excel
rather than VBA

Any suggestions greatly recieved

Kate
Poole


---
Message posted from http://www.ExcelForum.com/

Find and replace within VBA
 

Hi,
Thanks for the ideas - I'm trying to understand them!! I'm not tha
expert at VBA programming at all, but I'm learning fast!!

I think the code you gave me may not work as I am writing commands fo
a communication interface that has a set type library.

An example of the code follows:


Dim RetVal ' Implicitly a Variant.
Dim form, oldtext, text, counter
Set RetVal = CreateObject("monitor.verbatim")


RetVal.DetailMonitor = 1

RetVal.RangeFrom = "1ST04"
RetVal.RangeTo = "1ST04"

RetVal.MonthNo = "1"

RetVal.Source = 3 ' Product

RetVal.SourceName = "Avandamet"


RetVal.PComment = 1
RetVal.MComment = 1 ' 1 = true
RetVal.NComment = 1
RetVal.OComment = 1


RetVal.Extract

After this is a section of code to do with formatting the data i
Excel, plus repetitions of similar code extracting slightly differen
data.

What I want to do is change the "1ST04" to "2ND04" etc, so this has t
be done in the VBA section rather than through a worksheet as yo
suggested.

The section on your website seems similar to what I want except
cannot get it to work. The line

If j 0 And Not .ProcOfLine(i, vbext_pk_Proc) = "test
Then _
.ReplaceLine i, Replace(.Lines(i, 1), strFind
strReplace, , , vbTextCompare)

brings up an error saying "expected variable or procedure, not project
at the Replace(.Lines(i, 1) section.

If I could get around this hurdle it just might work?!?!

Any thoughts would be greatfully received.

Kat

--
Message posted from http://www.ExcelForum.com