Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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:D Col C:C contains a 3digit number ie "007" and Col D: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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done "Open Input Box"
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
How to disable "New" "open" menu items in Excel 2007 | Excel Programming | |||
Detecting user cancel out of "File Open" input box | Excel Programming | |||
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? | Excel Programming |