Prev: Comments and Print Preview
Next: How to write networking code in VBA?
From: Shane on 9 Jan 2010 19:49
I have a Workbook "Case Prep File" that pulls information from another
workbook "Client List". I have written a macro (fired by commandbutton on
"Case Prep File") that opens an input box to enter a client number and then
compares that information to numbers in Column A on "Client List" to pull the
client information from the corresponding rows.

I am testing the code in step mode in VBEditor and I see that the number
entered into the input box "GIDInput" is the same number as the value for
....range("groupid"), except "groupid" has quotation marks around it. I
thought by adding the lines with the "@" into the code it would make the
numbers appear the same to the code.

It currently runs without any numbers matching the input. What am I doing
wrong?

Sub CommandButton5_Click()

Dim LastRow As Long
Dim GIDCell As Range, TMISCell As Range
Dim TMIS As Worksheet, CList As Worksheet
Dim Option1Label As MSForms.Label, Option2Label As MSForms.Label,
Option3Label
Dim GIDInput, GID, FN1, FN2, FN3, FN4, LN1, LN2, LN3, LN4, GIDCount, GIDRow
Set TMIS = ThisWorkbook.Sheets("Team Member Input Sheet")
Set CList = Workbooks("Client List.xls").Sheets(1)
Set GID = ThisWorkbook.Sheets("Team Member Input Sheet").Range("groupid")
Application.ScreenUpdating = False
Application.EnableEvents = False
LastRow = CList.Range("A" & Rows.Count).End(xlUp).Row
GIDInput = InputBox("What is the 11 digit GROUP ID (No Check Digit) of the
clients you are looking up?", "Client Search")
TMIS.Range("groupid").Value = GIDInput
TMIS.Range("groupid").NumberFormat = "@"
CList.Activate
Columns("A:A").NumberFormat = "@"
On Error GoTo ws_exit
GIDCount = 1
Load ClientSelForm
For Each GIDCell In CList.Range("A1", "A" & LastRow)
If GIDCell.Value = TMIS.Range("groupid").Value Then
If Range("D" & GIDCell.Row).Value = "" Then
TMIS.Range("FName" & GIDCount).Value = CList.Range("C" &
GIDCell.Row).Value
Else: TMIS.Range("FName" & GIDCount).Value = CList.Range("D" &
GIDCell.Row).Value
End If
TMIS.Range("LName" & GIDCount).Value = CList.Range("E" &
GIDCell.Row).Value
ClientSelForm.Controls("Option" & GIDCount & "Label").Caption =
Range("FName" & GIDCount).Value & " " & Range("LNname" & GIDCount).Value
End If
GIDCount = GIDCount + 1
Next GIDCell
ClientSelForm.Show
ws_exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
 | 
Pages: 1
Prev: Comments and Print Preview
Next: How to write networking code in VBA?