Does anyone know how to do advanced input box functions? I am trying to use 1 input box to capture the users requests for several ID numbers. I then take these ID's and compare them to an Excel column and hide all the things that don't match these ID's. It works perfectly for a single entry. Not sure how to do multiple entries.
Here is my code. -
Sub Enrollments()
-
Dim rownumber As Integer, intLastRow As Integer, mycell As Integer, mycolor As Integer, startcell As Integer, X As Integer, Y As Integer, Z As Integer, A As Integer, myvar As Integer, columnnumber As Integer
-
-
Range("C1").Select
-
intLastRow = ActiveCell.CurrentRegion.Rows.Count
-
x1 = ActiveWorkbook.Name
-
-
Y = 6
-
Z = 4
-
A = 5
-
startcell = 2
-
myvar = 2
-
columnnumber = 8
-
rownumber = 2
-
-
Columns("G:G").Select
-
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 1), TrailingMinusNumbers:=True
-
-
Columns(columnnumber).Select
-
Selection.Insert shift:=xlToRight
-
X = InputBox("Please enter the BPL numbers.", "BPL InputBox ")
-
Cells(myvar, columnnumber).Select
-
intLastRow = intLastRow + 1
-
-
Do Until myvar = intLastRow
-
ActiveCell.FormulaR1C1 = "=if(RC[-1]=" & X & ",""MATCH"",""NO MATCH"")"
-
If ActiveCell <> "MATCH" Then
-
Rows(rownumber).Select
-
Selection.EntireRow.Hidden = True
-
End If
-
ActiveCell.Offset(1, 0).Select
-
If myvar = intLastRow Then
-
End If
-
myvar = myvar + 1
-
rownumber = rownumber + 1
-
Cells(myvar, columnnumber).Select
-
Loop
-
-
columnnumber = 16
-
myvar = 2
-
Cells(myvar, columnnumber).Select
-
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-12],RC[-11],RC[-9])"
-
Range("P2").Select
-
Selection.AutoFill Destination:=Range("P2", "P" & intLastRow)
-
Cells(myvar, columnnumber + 1).Select
-
-
'Counting for duplicates
-
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-1]:R[" & intLastRow & "]C[-1],RC[-1])"
-
Range("Q2").Select
-
Selection.AutoFill Destination:=Range("Q2", "Q" & intLastRow)
-
Cells(myvar, columnnumber + 1).Select
-
-
myvar = 2
-
Cells(myvar, columnnumber + 1).Select
-
Do Until myvar = intLastRow
-
If ActiveCell <> 1 Then
-
Selection.Interior.ColorIndex = 3
-
End If
-
ActiveCell.Offset(1, 0).Select
-
If myvar = intLastRow Then
-
End If
-
myvar = myvar + 1
-
Loop
-
-
myCount = 0
-
mycolor = 3
-
For I = startcell To intLastRow
-
mycell = Cells(I, columnnumber + 1).Interior.ColorIndex
-
If mycell = mycolor Then
-
myCount = 1 + myCount
-
End If
-
Next I
-
Worksheets("Export Worksheet").Cells(intLastRow, columnnumber + 1).Value = myCount
-
Worksheets("Export Worksheet").Cells(intLastRow, columnnumber).Select
-
-
MsgBox "All Checks have been completed.", vbInformation, "Enrollments"
-
End Sub
4 2663
Not gone through ur program but it seems that u want to enter multiple IDs in InputBox and do calculations, etc on individual ID. Here are my suggestion:
You can get multiple IDs in InputBox separated by a comma (,). Suppose u get: -
a=InputBox("Please enter IDs separated by comma")
-
Now use another variable, say 'ID', in which u separate these ID from a one by one using Instr function (inside a loop) and do ur calculation, etc for each ID.
Thanks very much for your help. I will try this and see if it works.
@smartchap
Or using the SPLIT function, of course ^.^
Yes using Instr function will require aloop & a little bit of code inside loop. With Split function it is straight forward. I agree with kadghar. Posting ur query to such forums is beneficial that u get a lot of solutions & u can choose the best among them.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: SophistiCat |
last post by:
Hi,
I am working on a computational program that has to read a number of
parameters (~50) from an input file. The program contains a single class
hierarchy with about a dozen member-classes or...
|
by: MM |
last post by:
Hi there,
How can I change my code (below) so that I use an "input argument" to
specify the file name of the input file? For example, if I compile the code
and that the application then gets the...
|
by: F. Michael Miller |
last post by:
I have a db with Access front end, sql back, linked tables. I need to be
able to change input masks at the table level in code.
Any ideas?
Thanks!
|
by: Don Riesbeck Jr. |
last post by:
I'm working on an application (OEM) using C# that utilizes input from a
keyboard, and USB Barcode Scanner. The scanner is a HID Keyboard device,
and input from it is sent to the system as if it...
|
by: FS Liu |
last post by:
Hi,
I am writing ATL Service application (XML Web service) in
VS.NET C++.
Are there any sample programs that accept XML as input and
XML as output
in the web service?
Thank you very much.
|
by: Nathan |
last post by:
I have an aspx page with a data grid, some textboxes, and an update button.
This page also has one html input element with type=file (not inside the data
grid and runat=server).
The update...
|
by: aurora |
last post by:
This is an entry I just added to ASPN. It is a somewhat novel technique I
have employed quite successfully in my code. I repost it here for more
explosure and discussions.
...
|
by: Iaintnofool |
last post by:
Hi I'm stuck on a project using VB 2005 express edition. I'm having trouble reading in data into my code from a Text File.
I'm using an openFileDialog so the user can select a file to open. The...
|
by: manxie |
last post by:
Dear All Readers,
I'm supposed to create a program with a switch and using voids to execute number of codes, that includes finding sum, average, maximum, and minimum, please read my code:...
|
by: moveitho |
last post by:
Hi,
I have the following script on my site but need to implement a select menu into the script as it would make it a lot tidier.
This script adds the required amount of fields selected by the...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |