VLookUp ComboBox input to update TextBox values

[NDMDRB]

Hello,

I have the code below to get the lookup amount of the selected client from sheet5, but I don't know what I did wrong since it's not showing the amount on the "txtTotal, can you please help me to get the amount of the selected client?

Note: the Amounts on column "D" contains formulas

Private Sub cmdContact_Click()
On Error Resume Next
If WorksheetFunction.CountIf(Sheet5.Range("B:B"), Me.cboName.Text) = 0 Then
MsgBox "This client is not exist"
Me.cboName.Value = "Cash Client"
Exit Sub
End If
With Me
.txtTotal.Value = Application.WorksheetFunction.VLookup(Me.cboName.Text, Sheet5.Range("A1").CurrentRegion, 4, 0)
End With
End Sub

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Phone[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]Cash Client[/TD]
[TD][/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]Nadim[/TD]
[TD][/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]Rami[/TD]
[TD][/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]

 

[salim hasan]

the right for .txtTotal.Value is

.txtTotal.Value = Application.WorksheetFunction.VLookup(Me.cboName.Text, Sheet5.Range("A1").CurrentRegion.[SIZE=2][COLOR=#ff0000]Offset(,1)[/COLOR][/SIZE], 4, 0)

 

[pella88]

Hi NDMDRB,

I usually avoid using excel functions within VBA, since VBA has its own ways on handling such cases, namely using FIND method.

For example:

Private Sub cmdContact_Click()

On Error Resume Next


With Sheet1.Range("B2:B4")
    Set fvalue = .Find(What:=cmdContact.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
End With


If fvalue Is Nothing Then
    MsgBox "This client does not exist"
    Me.cmdContact.Value = "Cash Client"
    Exit Sub
End If


With Me
    .txtTotal.Value = fvalue.Offset(, 2).Value
End With


End Sub

Hope this will help you.

Br
pella88

 

[NDMDRB]

Thank you so much Salim Hasan,
Can you please add a format for the "txtTotal to show the amount as it is in cells column "D"

Cells in Column "D" formats looks like "2,000 L.L."
Here is the exact format: _-* #,##0 [$L.L.‏-ar-LB]_-;-* #,##0 [$L.L.‏-ar-LB]_-;_-* "-" [$L.L.‏-ar-LB]_-;_-@_-

 

[salim hasan]

Try

Sub test()
Range("D:D").NumberFormat = " #.00  L.L; - #.00  L.L;""Zero"" L.L"
End Sub

 

[NDMDRB]

Thank you Salim Hasan,

I think that I didn't explain well,

I have the cells format already, but what I need is to add this format to the textbox "txtTotal" to be shown in the userform as "2,000 L.L." or if this is not possible just make it "2,000"

 

[salim hasan]

dim t,x
x=[COLOR=#333333]" #.00  L.L; - #.00  L.L;""Zero"" L.L"[/COLOR]
t[COLOR=#333333].txtTotal.Value
[/COLOR]t=t.NumberFormat(x)
[COLOR=#333333]txtTotal.Value=t[/COLOR]

 

[salim hasan]

try this

Sub test()
Dim t, x
x = " #.00  L.L; - #.00  L.L;""Zero"" L.L"
t = Format$([COLOR=#333333]txtTotal.Value[/COLOR], x)
[COLOR=#333333]txtTotal[/COLOR][COLOR=#333333].Value[/COLOR] = t
End Sub

 

[NDMDRB]

Can you please let me know in which subroutine should I put this code

 

[salim hasan]

the complete macro

Private Sub cmdContact_Click()
On Error Resume Next
If WorksheetFunction.CountIf(Sheet5.Range("B:B"), Me.cboName.Text) = 0 Then
MsgBox "This client is not exist"
Me.cboName.Value = "Cash Client"
Exit Sub
End If
With Me

.txtTotal.Value = Application.WorksheetFunction.VLookup(Me.cboName.Text, Sheet5.Range("A1").CurrentRegion, 4, 0)
End With

Dim t, x
x = " #.00  L.L; - #.00  L.L;""Zero"" L.L"
t = Format$([COLOR=#333333]txtTotal.Value[/COLOR], x)
[COLOR=#333333]txtTotal[/COLOR][COLOR=#333333].Value[/COLOR] = tEnd Sub</pre>