Hello from Steved

Is the below possible please if so How would I go about it.

I Have a "input box", no matter where I'm on the worksheet I would like it
to open so the I can type ie 007,7.25 or something similar

I have this input box at the Beginning off the worksheet, and I find if I'm
on row 580 and I would like to open a input box on row 580.

I Thankyou.

hello from Steved

I found this I would like to make changes please

1 To work on the current worksheet only and Search critria is Col C:C and
Col D

Col C:C contains a 3digit number ie "007" and Col D contains A number with
2 Decimal points ie "8.25"

2 If I type in "007,8.25" I would like it to find every occurence normal
appears up to three times, I woud like to cancel the find on the correct one.
In column A:A I have Data I need to change and I have over 1500 rows of
information.

I Thankyou.

Option Explicit
Sub Schools()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[a1].Activate
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not Firstcell Is Nothing Then
Firstcell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " &
oSheet.Name & "!" & Firstcell.Address)
On Error Resume Next
While (Not NextCell Is Nothing) And (Not
NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) &
" in " & oSheet.Name & "!" & NextCell.Address)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
End Sub








"Steved" wrote:

> Hello from Steved
>
> Is the below possible please if so How would I go about it.
>
> I Have a "input box", no matter where I'm on the worksheet I would like it
> to open so the I can type ie 007,7.25 or something similar
>
> I have this input box at the Beginning off the worksheet, and I find if I'm
> on row 580 and I would like to open a input box on row 580.
>
> I Thankyou.

hi
confused.
a "input box" is called by code. if you have code, please post that so we
can see how you are calling the input box. if you don't have code, what are
you calling an input box?
also. what is the differenct between typing into a cell on row 580 and
typing into a input box associated with row 580. and how would xl know that
you want the input box associated with row 580.

post back with more details.

Regards
FSt1

"Steved" wrote:

> Hello from Steved
>
> Is the below possible please if so How would I go about it.
>
> I Have a "input box", no matter where I'm on the worksheet I would like it
> to open so the I can type ie 007,7.25 or something similar
>
> I have this input box at the Beginning off the worksheet, and I find if I'm
> on row 580 and I would like to open a input box on row 580.
>
> I Thankyou.

Hello FSt1

As you were replying I was inputing hopefully something you will be able to
follow

I Thankyou for taking timeout on my issue.


Cheers

Steved

"FSt1" wrote:

> hi
> confused.
> a "input box" is called by code. if you have code, please post that so we
> can see how you are calling the input box. if you don't have code, what are
> you calling an input box?
> also. what is the differenct between typing into a cell on row 580 and
> typing into a input box associated with row 580. and how would xl know that
> you want the input box associated with row 580.
>
> post back with more details.
>
> Regards
> FSt1
>
> "Steved" wrote:
>
> > Hello from Steved
> >
> > Is the below possible please if so How would I go about it.
> >
> > I Have a "input box", no matter where I'm on the worksheet I would like it
> > to open so the I can type ie 007,7.25 or something similar
> >
> > I have this input box at the Beginning off the worksheet, and I find if I'm
> > on row 580 and I would like to open a input box on row 580.
> >
> > I Thankyou.

Do you mean...

vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)

If this post helps click Yes
---------------
Jacob Skaria


"Steved" wrote:

> Hello from Steved
>
> Is the below possible please if so How would I go about it.
>
> I Have a "input box", no matter where I'm on the worksheet I would like it
> to open so the I can type ie 007,7.25 or something similar
>
> I have this input box at the Beginning off the worksheet, and I find if I'm
> on row 580 and I would like to open a input box on row 580.
>
> I Thankyou.

Hello Jacob

I thankyou, however please I require your help

The below brings up the input box I type in 007,7.25 the input box closes,
after I hit ok.

I'm not a VBA person but I got the below to go so far, and I would like
please to stop at the found Cells and if that is the only one found go left 2
cells if their is more than one occurrence please invite me to cancel or go
and find the next ie "007,7.25" may be there more than once.

I Thankyou for your time on my issue.

Steved

Sub Schools()
Dim Firstcell As Range
Dim NextCell As Range
Dim vaRecipient As Variant
vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)

End Sub




"Jacob Skaria" wrote:

> Do you mean...
>
> vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
> Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Steved" wrote:
>
> > Hello from Steved
> >
> > Is the below possible please if so How would I go about it.
> >
> > I Have a "input box", no matter where I'm on the worksheet I would like it
> > to open so the I can type ie 007,7.25 or something similar
> >
> > I have this input box at the Beginning off the worksheet, and I find if I'm
> > on row 580 and I would like to open a input box on row 580.
> >
> > I Thankyou.

I am not sure whether I understood your query correctly...Try the below

--You are trying to search a string eg: 007,7.25 which exists in ColC and
ColD. (007 in ColC and 7.25 in Col D)

--Try the below macro which will prompt for a input and if you type 007,7.25
it will look for 007 in ColC and also which has got an entry 7.25 in ColD

--I have tried the below in a new workbook. Formatted the columns C and D as
text and entered 007, 7.25 in C10, D10 and C20,D20....The macro will search
and returns the address and prompts whether to continue. If the user selects
no; the the macro will end...

Sub Macro()

Dim varFound As Variant
Dim varSearch As Variant
Dim arrSearch As Variant
Dim strSearch As String

strSearch = InputBox("Type the string to be searched in Col C and D")
arrSearch = Split(strSearch & ",,", ",")

varSearch = arrSearch(0)

With Worksheets("Sheet1").Range("C:C")
Set varFound = .Find(varSearch, LookIn:=xlValues)
If Not varFound Is Nothing Then
firstAddress = varFound.Address
Do
If varFound.Offset(0, 1) = arrSearch(1) Then
varFound.Activate
If MsgBox(strSearch & " found at " & varFound.Address & _
vbLf & "Do you want to continue ?", vbYesNo) = vbNo Then Exit Sub
End If
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address <> firstAddress
End If
End With

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"Steved" wrote:

> Hello Jacob
>
> I thankyou, however please I require your help
>
> The below brings up the input box I type in 007,7.25 the input box closes,
> after I hit ok.
>
> I'm not a VBA person but I got the below to go so far, and I would like
> please to stop at the found Cells and if that is the only one found go left 2
> cells if their is more than one occurrence please invite me to cancel or go
> and find the next ie "007,7.25" may be there more than once.
>
> I Thankyou for your time on my issue.
>
> Steved
>
> Sub Schools()
> Dim Firstcell As Range
> Dim NextCell As Range
> Dim vaRecipient As Variant
> vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
> Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)
>
> End Sub
>
>
>
>
> "Jacob Skaria" wrote:
>
> > Do you mean...
> >
> > vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
> > Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Steved" wrote:
> >
> > > Hello from Steved
> > >
> > > Is the below possible please if so How would I go about it.
> > >
> > > I Have a "input box", no matter where I'm on the worksheet I would like it
> > > to open so the I can type ie 007,7.25 or something similar
> > >
> > > I have this input box at the Beginning off the worksheet, and I find if I'm
> > > on row 580 and I would like to open a input box on row 580.
> > >
> > > I Thankyou.

Hello Steved


I would like to know please if it is possible to search for a value instead
off text.

I have over 700 entires and when I change "001" to text value it shows "1"

The below works as it should if I have cells as text


Sub Macro()

Dim varFound As Variant
Dim varSearch As Variant
Dim arrSearch As Variant
Dim strSearch As String

strSearch = InputBox("Type the string to be searched in Col C and D")
arrSearch = Split(strSearch & ",,", ",")

varSearch = arrSearch(0)

With Worksheets("Sheet1").Range("C:C")
Set varFound = .Find(varSearch, LookIn:=xlValues)
If Not varFound Is Nothing Then
firstAddress = varFound.Address
Do
If varFound.Offset(0, 1) = arrSearch(1) Then
varFound.Activate
If MsgBox(strSearch & " found at " & varFound.Address & _
vbLf & "Do you want to continue ?", vbYesNo) = vbNo Then Exit Sub
End If
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address <> firstAddress
End If
End With

End Sub

Hello from Steved

This is now closed as I've posted a Later question

I Thankyou.

"Steved" wrote:

> Hello Steved
>
>
> I would like to know please if it is possible to search for a value instead
> off text.
>
> I have over 700 entires and when I change "001" to text value it shows "1"
>
> The below works as it should if I have cells as text
>
>
> Sub Macro()
>
> Dim varFound As Variant
> Dim varSearch As Variant
> Dim arrSearch As Variant
> Dim strSearch As String
>
> strSearch = InputBox("Type the string to be searched in Col C and D")
> arrSearch = Split(strSearch & ",,", ",")
>
> varSearch = arrSearch(0)
>
> With Worksheets("Sheet1").Range("C:C")
> Set varFound = .Find(varSearch, LookIn:=xlValues)
> If Not varFound Is Nothing Then
> firstAddress = varFound.Address
> Do
> If varFound.Offset(0, 1) = arrSearch(1) Then
> varFound.Activate
> If MsgBox(strSearch & " found at " & varFound.Address & _
> vbLf & "Do you want to continue ?", vbYesNo) = vbNo Then Exit Sub
> End If
> Set varFound = .FindNext(varFound)
> Loop While Not varFound Is Nothing And _
> varFound.Address <> firstAddress
> End If
> End With
>
> End Sub
>
>

Hello Jacob

Excellent and I thankyou

Steved

"Jacob Skaria" wrote:

> I am not sure whether I understood your query correctly...Try the below
>
> --You are trying to search a string eg: 007,7.25 which exists in ColC and
> ColD. (007 in ColC and 7.25 in Col D)
>
> --Try the below macro which will prompt for a input and if you type 007,7.25
> it will look for 007 in ColC and also which has got an entry 7.25 in ColD
>
> --I have tried the below in a new workbook. Formatted the columns C and D as
> text and entered 007, 7.25 in C10, D10 and C20,D20....The macro will search
> and returns the address and prompts whether to continue. If the user selects
> no; the the macro will end...
>
> Sub Macro()
>
> Dim varFound As Variant
> Dim varSearch As Variant
> Dim arrSearch As Variant
> Dim strSearch As String
>
> strSearch = InputBox("Type the string to be searched in Col C and D")
> arrSearch = Split(strSearch & ",,", ",")
>
> varSearch = arrSearch(0)
>
> With Worksheets("Sheet1").Range("C:C")
> Set varFound = .Find(varSearch, LookIn:=xlValues)
> If Not varFound Is Nothing Then
> firstAddress = varFound.Address
> Do
> If varFound.Offset(0, 1) = arrSearch(1) Then
> varFound.Activate
> If MsgBox(strSearch & " found at " & varFound.Address & _
> vbLf & "Do you want to continue ?", vbYesNo) = vbNo Then Exit Sub
> End If
> Set varFound = .FindNext(varFound)
> Loop While Not varFound Is Nothing And _
> varFound.Address <> firstAddress
> End If
> End With
>
> End Sub
>
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Steved" wrote:
>
> > Hello Jacob
> >
> > I thankyou, however please I require your help
> >
> > The below brings up the input box I type in 007,7.25 the input box closes,
> > after I hit ok.
> >
> > I'm not a VBA person but I got the below to go so far, and I would like
> > please to stop at the found Cells and if that is the only one found go left 2
> > cells if their is more than one occurrence please invite me to cancel or go
> > and find the next ie "007,7.25" may be there more than once.
> >
> > I Thankyou for your time on my issue.
> >
> > Steved
> >
> > Sub Schools()
> > Dim Firstcell As Range
> > Dim NextCell As Range
> > Dim vaRecipient As Variant
> > vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
> > Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)
> >
> > End Sub
> >
> >
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Do you mean...
> > >
> > > vaRecipient = Application.InputBox(Prompt:="Please enter the number", _
> > > Title:="Recipient", Type:=2, Left:=ActiveCell.Left, Top:=ActiveCell.Top)
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "Steved" wrote:
> > >
> > > > Hello from Steved
> > > >
> > > > Is the below possible please if so How would I go about it.
> > > >
> > > > I Have a "input box", no matter where I'm on the worksheet I would like it
> > > > to open so the I can type ie 007,7.25 or something similar
> > > >
> > > > I have this input box at the Beginning off the worksheet, and I find if I'm
> > > > on row 580 and I would like to open a input box on row 580.
> > > >
> > > > I Thankyou.