Vlookup and a UserForm - Aladin or Dave any ideas

Posted by Dominic on May 29, 2001 7:14 AM

Based on info from ALadin I am using a User Form to calculate a price. I receive a numerical input ie 0,87 which I then write to an excel sheet where vlookup then determines the correct matching number and then returns it to me form. The problem is if someone enters 0.87 then OK but if someone enters 0,87, it can not match up.(ie can't deal with the comma)
The excel colums are formatted as numbers to two decimal places. Here is a snippet of the code

Curr = UserForm1.ComboBox3.Value
MAC = UserForm1.ComboBox2.Value
Freight = UserForm1.ComboBox4.Value
EXW = ((MAC / 1000) * 0.4536) * 100
UserForm1.TextBox1.Text = Str$(EXW)
DDU = (EXW + (((Freight / 1000) * 100) * 0.4536))
UserForm1.TextBox2.Text = Str$(DDU)
Correction = ((1 / Curr) - 1) / 2

Posted by Aladin Akyurek on May 29, 2001 7:26 AM

Dominic

Build an extra round of processing before you feed the input to the vlookup formula.

=IF(ISNUMBER(A8),A8,SUBSTITUTE(A8,",",".")+0)

where A1 contains the input value.

Aladin

Posted by Dominic on May 29, 2001 8:36 AM

Thanks Aladin - One more question

It worked a treat, I tried a similar thing using the replace command, but was not successful.
I actually have 2 questions

I build error protection into all my Macros

ie. On Error Goto
but this is not ideal, any suggestions.

And then WHen I Close my Form ( Userform.Hide)
certain text does not get deleted. I use the following code to hadle my ComboBoxes, but how do I handle Textboxes ?
Dim ctlwork as Control
For each ctlwork in UserForm1.Controls
if typeof ctlWork is ComboBox then
Ctlwork.text=""
End If
Next

Posted by Aladin Akyurek on May 29, 2001 9:08 AM

One more question for VBA folks...

Dominic

I'm afraid this one is for the VBA programmers. I expect them to come in soon.

Aladin

Posted by Dax on May 29, 2001 10:33 AM

Re: Thanks Aladin - One more question

Hi,

You can make a slight change to your code to allow for textboxes: -

Dim ctlwork As Control
For Each ctlwork In UserForm1.Controls
If TypeOf ctlwork Is ComboBox Or TypeOf ctlwork Is MSForms.TextBox Then
ctlwork.Text = ""
End If
Next

You need to qualify the Textbox bit with MSforms because otherwise the macro will test to see if the control is an Excel textbox (a hidden object) and the macro won't work properly.

HTH,
Dax.

Posted by Dominic on May 29, 2001 10:50 AM

Thanks Dax, it was the MsForm that i was missing !!!

Thanks