Search/replace Userform Variable

[ISA]

I've done this in Word, but can't seem to get it to work in Excel. I have a
Userform wherein the user types some text (variable1). When the user clicks
on the "continue" button I want the code behind that button to Search and
Replace the predefined text in the worksheet and replace it with the
variable1 text that the user typed. If I record the keystrokes for search and
replace, the macros works, but it does not work after putting that code in
the button code because I don't know how/where to put the "variable1". In
Word, it looked like this:

>>
Private Sub CommandButton1_Click()
Dim variable1 As String
variable1 = FillInDate.DateBox.Text
Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
<<<
Any help out there?

[Dave Peterson]

Don't you need a variable2, too?

Private Sub CommandButton1_Click()
Dim variable1 As String
dim Variable2 as string
variable1 = FillInDate.DateBox.Text
variable2 = fillindate.datebox2.text '????
Cells.Replace What:=variable1, Replacement:=variable2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
end sub




ISA wrote:
>
> I've done this in Word, but can't seem to get it to work in Excel. I have a
> Userform wherein the user types some text (variable1). When the user clicks
> on the "continue" button I want the code behind that button to Search and
> Replace the predefined text in the worksheet and replace it with the
> variable1 text that the user typed. If I record the keystrokes for search and
> replace, the macros works, but it does not work after putting that code in
> the button code because I don't know how/where to put the "variable1". In
> Word, it looked like this:
>
> >>
> Private Sub CommandButton1_Click()
> Dim variable1 As String
> variable1 = FillInDate.DateBox.Text
> Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False) _
> .Activate
> Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> <<<
> Any help out there?

--

Dave Peterson

[ISA]

No, a variable2 is not needed. The "C4" is the predefined text in the
worksheet that will be replaced with variable1.

"Dave Peterson" wrote:

> Don't you need a variable2, too?
>
> Private Sub CommandButton1_Click()
> Dim variable1 As String
> dim Variable2 as string
> variable1 = FillInDate.DateBox.Text
> variable2 = fillindate.datebox2.text '????
> Cells.Replace What:=variable1, Replacement:=variable2, LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> end sub
>
>
>
>
> ISA wrote:
> >
> > I've done this in Word, but can't seem to get it to work in Excel. I have a
> > Userform wherein the user types some text (variable1). When the user clicks
> > on the "continue" button I want the code behind that button to Search and
> > Replace the predefined text in the worksheet and replace it with the
> > variable1 text that the user typed. If I record the keystrokes for search and
> > replace, the macros works, but it does not work after putting that code in
> > the button code because I don't know how/where to put the "variable1". In
> > Word, it looked like this:
> >
> > >>
> > Private Sub CommandButton1_Click()
> > Dim variable1 As String
> > variable1 = FillInDate.DateBox.Text
> > Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False) _
> > .Activate
> > Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False
> > <<<
> > Any help out there?
>
> --
>
> Dave Peterson
>

[Dave Peterson]

So maybe it's:

Private Sub CommandButton1_Click()
Dim variable1 As String

variable1 = FillInDate.DateBox.Text

Cells.Replace What:="C4", Replacement:=variable1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

end sub

ISA wrote:
>
> No, a variable2 is not needed. The "C4" is the predefined text in the
> worksheet that will be replaced with variable1.
>
> "Dave Peterson" wrote:
>
> > Don't you need a variable2, too?
> >
> > Private Sub CommandButton1_Click()
> > Dim variable1 As String
> > dim Variable2 as string
> > variable1 = FillInDate.DateBox.Text
> > variable2 = fillindate.datebox2.text '????
> > Cells.Replace What:=variable1, Replacement:=variable2, LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False
> > end sub
> >
> >
> >
> >
> > ISA wrote:
> > >
> > > I've done this in Word, but can't seem to get it to work in Excel. I have a
> > > Userform wherein the user types some text (variable1). When the user clicks
> > > on the "continue" button I want the code behind that button to Search and
> > > Replace the predefined text in the worksheet and replace it with the
> > > variable1 text that the user typed. If I record the keystrokes for search and
> > > replace, the macros works, but it does not work after putting that code in
> > > the button code because I don't know how/where to put the "variable1". In
> > > Word, it looked like this:
> > >
> > > >>
> > > Private Sub CommandButton1_Click()
> > > Dim variable1 As String
> > > variable1 = FillInDate.DateBox.Text
> > > Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> > > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > MatchCase:=False) _
> > > .Activate
> > > Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
> > > SearchOrder:=xlByRows, MatchCase:=False
> > > <<<
> > > Any help out there?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[ISA]

I am receiving a Run-Time Error 1004, The formula you typed contains an error.
When I click Debug, the Cells.Replace What... is highlighted.
Perhaps the Replace will not work from the UserForm since I am trying to
replace a cell name in a formula in the worksheet (=(WorksheetB!C4)).

What I'm trying to do is create a summary sheet that pulls the cell contents
from other worksheets but from a different row each day. The only way I can
figure to change the row reference (which represents a day in the month) is
to ask the user to tell me what day it is (for today it would be 26) and then
replace the formula cell with the day the user enters (not saving their
changes to the base document so that C4 will always be in the formula when it
is updated next time). If you have a suggestion on a better way to do this, I
am welcome to hear it.

P.S. It may be too much to ask the user to use the Replace function
manually, since I am dealing with non-computer people.

"Dave Peterson" wrote:

> So maybe it's:
>
> Private Sub CommandButton1_Click()
> Dim variable1 As String
>
> variable1 = FillInDate.DateBox.Text
>
> Cells.Replace What:="C4", Replacement:=variable1, LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
>
> end sub
>
> ISA wrote:
> >
> > No, a variable2 is not needed. The "C4" is the predefined text in the
> > worksheet that will be replaced with variable1.
> >
> > "Dave Peterson" wrote:
> >
> > > Don't you need a variable2, too?
> > >
> > > Private Sub CommandButton1_Click()
> > > Dim variable1 As String
> > > dim Variable2 as string
> > > variable1 = FillInDate.DateBox.Text
> > > variable2 = fillindate.datebox2.text '????
> > > Cells.Replace What:=variable1, Replacement:=variable2, LookAt:=xlPart, _
> > > SearchOrder:=xlByRows, MatchCase:=False
> > > end sub
> > >
> > >
> > >
> > >
> > > ISA wrote:
> > > >
> > > > I've done this in Word, but can't seem to get it to work in Excel. I have a
> > > > Userform wherein the user types some text (variable1). When the user clicks
> > > > on the "continue" button I want the code behind that button to Search and
> > > > Replace the predefined text in the worksheet and replace it with the
> > > > variable1 text that the user typed. If I record the keystrokes for search and
> > > > replace, the macros works, but it does not work after putting that code in
> > > > the button code because I don't know how/where to put the "variable1". In
> > > > Word, it looked like this:
> > > >
> > > > >>
> > > > Private Sub CommandButton1_Click()
> > > > Dim variable1 As String
> > > > variable1 = FillInDate.DateBox.Text
> > > > Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> > > > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > > MatchCase:=False) _
> > > > .Activate
> > > > Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
> > > > SearchOrder:=xlByRows, MatchCase:=False
> > > > <<<
> > > > Any help out there?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I don't know what's causing the error. I would expect that it's something in
Variable1 that makes the formula an error--just like typing it in from the user
interface.

But maybe you could use a different technique:

=today() returns today's date.
=day(today()) would return the day portion of that date
=format(day(today()),"00") would make it a two digit string (if that were
required.

so maybe altering the formula to use =day(today()) and =indirect() would work:

=indirect("WorksheetB!C" & day(today()))
(or something like that...)
This would return the value from Worksheetb!C26 (well, today it would).



ISA wrote:
>
> I am receiving a Run-Time Error 1004, The formula you typed contains an error.
> When I click Debug, the Cells.Replace What... is highlighted.
> Perhaps the Replace will not work from the UserForm since I am trying to
> replace a cell name in a formula in the worksheet (=(WorksheetB!C4)).
>
> What I'm trying to do is create a summary sheet that pulls the cell contents
> from other worksheets but from a different row each day. The only way I can
> figure to change the row reference (which represents a day in the month) is
> to ask the user to tell me what day it is (for today it would be 26) and then
> replace the formula cell with the day the user enters (not saving their
> changes to the base document so that C4 will always be in the formula when it
> is updated next time). If you have a suggestion on a better way to do this, I
> am welcome to hear it.
>
> P.S. It may be too much to ask the user to use the Replace function
> manually, since I am dealing with non-computer people.
>
> "Dave Peterson" wrote:
>
> > So maybe it's:
> >
> > Private Sub CommandButton1_Click()
> > Dim variable1 As String
> >
> > variable1 = FillInDate.DateBox.Text
> >
> > Cells.Replace What:="C4", Replacement:=variable1, LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False
> >
> > end sub
> >
> > ISA wrote:
> > >
> > > No, a variable2 is not needed. The "C4" is the predefined text in the
> > > worksheet that will be replaced with variable1.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Don't you need a variable2, too?
> > > >
> > > > Private Sub CommandButton1_Click()
> > > > Dim variable1 As String
> > > > dim Variable2 as string
> > > > variable1 = FillInDate.DateBox.Text
> > > > variable2 = fillindate.datebox2.text '????
> > > > Cells.Replace What:=variable1, Replacement:=variable2, LookAt:=xlPart, _
> > > > SearchOrder:=xlByRows, MatchCase:=False
> > > > end sub
> > > >
> > > >
> > > >
> > > >
> > > > ISA wrote:
> > > > >
> > > > > I've done this in Word, but can't seem to get it to work in Excel. I have a
> > > > > Userform wherein the user types some text (variable1). When the user clicks
> > > > > on the "continue" button I want the code behind that button to Search and
> > > > > Replace the predefined text in the worksheet and replace it with the
> > > > > variable1 text that the user typed. If I record the keystrokes for search and
> > > > > replace, the macros works, but it does not work after putting that code in
> > > > > the button code because I don't know how/where to put the "variable1". In
> > > > > Word, it looked like this:
> > > > >
> > > > > >>
> > > > > Private Sub CommandButton1_Click()
> > > > > Dim variable1 As String
> > > > > variable1 = FillInDate.DateBox.Text
> > > > > Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> > > > > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > > > MatchCase:=False) _
> > > > > .Activate
> > > > > Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
> > > > > SearchOrder:=xlByRows, MatchCase:=False
> > > > > <<<
> > > > > Any help out there?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[ISA]

Thanks. I think I can make it work with this. I'll put the =day(today()) in
a cell and then set the formula to pull the day from the cell like this
=INDIRECT("WorksheetB!C" & F1). Thanks so much for your quick responses.

Happy New Year !


"Dave Peterson" wrote:

> I don't know what's causing the error. I would expect that it's something in
> Variable1 that makes the formula an error--just like typing it in from the user
> interface.
>
> But maybe you could use a different technique:
>
> =today() returns today's date.
> =day(today()) would return the day portion of that date
> =format(day(today()),"00") would make it a two digit string (if that were
> required.
>
> so maybe altering the formula to use =day(today()) and =indirect() would work:
>
> =indirect("WorksheetB!C" & day(today()))
> (or something like that...)
> This would return the value from Worksheetb!C26 (well, today it would).
>
>
>
> ISA wrote:
> >
> > I am receiving a Run-Time Error 1004, The formula you typed contains an error.
> > When I click Debug, the Cells.Replace What... is highlighted.
> > Perhaps the Replace will not work from the UserForm since I am trying to
> > replace a cell name in a formula in the worksheet (=(WorksheetB!C4)).
> >
> > What I'm trying to do is create a summary sheet that pulls the cell contents
> > from other worksheets but from a different row each day. The only way I can
> > figure to change the row reference (which represents a day in the month) is
> > to ask the user to tell me what day it is (for today it would be 26) and then
> > replace the formula cell with the day the user enters (not saving their
> > changes to the base document so that C4 will always be in the formula when it
> > is updated next time). If you have a suggestion on a better way to do this, I
> > am welcome to hear it.
> >
> > P.S. It may be too much to ask the user to use the Replace function
> > manually, since I am dealing with non-computer people.
> >
> > "Dave Peterson" wrote:
> >
> > > So maybe it's:
> > >
> > > Private Sub CommandButton1_Click()
> > > Dim variable1 As String
> > >
> > > variable1 = FillInDate.DateBox.Text
> > >
> > > Cells.Replace What:="C4", Replacement:=variable1, LookAt:=xlPart, _
> > > SearchOrder:=xlByRows, MatchCase:=False
> > >
> > > end sub
> > >
> > > ISA wrote:
> > > >
> > > > No, a variable2 is not needed. The "C4" is the predefined text in the
> > > > worksheet that will be replaced with variable1.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Don't you need a variable2, too?
> > > > >
> > > > > Private Sub CommandButton1_Click()
> > > > > Dim variable1 As String
> > > > > dim Variable2 as string
> > > > > variable1 = FillInDate.DateBox.Text
> > > > > variable2 = fillindate.datebox2.text '????
> > > > > Cells.Replace What:=variable1, Replacement:=variable2, LookAt:=xlPart, _
> > > > > SearchOrder:=xlByRows, MatchCase:=False
> > > > > end sub
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ISA wrote:
> > > > > >
> > > > > > I've done this in Word, but can't seem to get it to work in Excel. I have a
> > > > > > Userform wherein the user types some text (variable1). When the user clicks
> > > > > > on the "continue" button I want the code behind that button to Search and
> > > > > > Replace the predefined text in the worksheet and replace it with the
> > > > > > variable1 text that the user typed. If I record the keystrokes for search and
> > > > > > replace, the macros works, but it does not work after putting that code in
> > > > > > the button code because I don't know how/where to put the "variable1". In
> > > > > > Word, it looked like this:
> > > > > >
> > > > > > >>
> > > > > > Private Sub CommandButton1_Click()
> > > > > > Dim variable1 As String
> > > > > > variable1 = FillInDate.DateBox.Text
> > > > > > Cells.Find(What:="C4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> > > > > > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > > > > MatchCase:=False) _
> > > > > > .Activate
> > > > > > Cells.Replace What:="C4", Replacement:="C5", LookAt:=xlPart, _
> > > > > > SearchOrder:=xlByRows, MatchCase:=False
> > > > > > <<<
> > > > > > Any help out there?
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>